Doctrine get single latest row of Detail for each Master

Question

I have QueryBuilder like this

$query = $this->getEntityManager()
  ->createQueryBuilder()
  ->select('partial master.{id,name}, partial detail.{id,name}')
  ->from('AppBundle:Master', 'master')
  ->leftJoin('master.detail', 'detail')
  ->orderBy('detail.id','DESC')
  ->getQuery();
return $query->getArrayResult();

master relation is OneToMany, detail relation is ManyToOne. I'm trying to get a single row of detail with detail id is higher or lower. What Querybuilder to make this possible? example I have data like this:

1.A, 1.B, 1.C, 2.A, 3.A, 3.C

I want Result like this below

1.A, 2.A, 3.A

dont like this. Here I have been edited my question and give example. @MertÖksüz — Youngz ie, Feb 17 '17 at 02:07
Possible duplicate of [Doctrine Query Language get Max/Latest Row Per Group](https://stackoverflow.com/questions/46106582/doctrine-query-language-get-max-latest-row-per-group) — M Khalid Junaid, Dec 05 '17 at 09:45

Rendy Eko Prastiyo · Answer 1 · 2017-02-17T03:01:22.157

1

$query = $this->getEntityManager()
    ->createQueryBuilder()
    ->select('master.id, master.name, detail.id, detail.name')
    ->from('AppBundle:Master', 'master')
    ->leftJoin('master.detail', 'detail')
    ->groupBy('master.id')
    ->orderBy('detail.id','DESC')
    ->getQuery()
;

return $query->getArrayResult();

edited Feb 17 '17 at 03:01

answered Feb 16 '17 at 04:02

Rendy Eko Prastiyo

1,068
7
16

i can't use groupBy, if the detail name has 2 different value, i'm still get 2 detail. @rendyEkoPrastiyo – Youngz ie Feb 16 '17 at 04:29
Your question tells `I'm trying to get a single row of detail.`, now you want `if the detail name has 2 different value, i'm still get 2 detail.`, what do you mean? – Rendy Eko Prastiyo Feb 16 '17 at 04:51
if i use groupBy with the detail name has 2 different value, i'm still get 2 detail. I want only get single row with specified detail id. example with higher detail id or lower detail id. @rendy@EkoPrastiyo – Youngz ie Feb 16 '17 at 05:02
Could you give the result of `dump($query->getArrayResult()` of my query above? – Rendy Eko Prastiyo Feb 16 '17 at 10:03
yes. but not yet like i mean. dont like this. Here I have been edited my quetion and give example. @rendyEkoPrastiyo – Youngz ie Feb 17 '17 at 02:09
Do not use `partial` in your select. Doctrine doc says `The partial object problem in general does not apply to methods or queries where you do not retrieve the query result as objects. Examples are: Query#getArrayResult(), Query#getScalarResult(), Query#getSingleScalarResult(), etc.` [Partial Objects](http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/partial-objects.html). I have updated the answer, could you give a try? – Rendy Eko Prastiyo Feb 17 '17 at 03:02
I use partial becauce i dont need other field, and partial can do right i wanted. so how to get detail id with higher or lower id? @rendyEkoPrastiyo – Youngz ie Feb 17 '17 at 03:30
If you want array result, you can't use partial. That's Doctrine's law. If you don't want other fields, or only select some fields, you can use `$queryBuilder->select('table.field1, table.field2')`, that would select only the fields you requested. – Rendy Eko Prastiyo Feb 17 '17 at 03:44
okay, so how i would filter detail id with higher or lower id? @rendyEkoPrastiyo – Youngz ie Feb 17 '17 at 07:03
`orderBy('detail.id','DESC')` on the example is for descending ordering of `detail` by `id`. You may change it to fit your need. – Rendy Eko Prastiyo Feb 18 '17 at 20:26
cant to be simple like that. If parent have 2 or 3 detail, I only need 1 detail with id higher or lower. @rendyEkoPrastiyo – Youngz ie Feb 19 '17 at 14:23
Have you tried before complaining? `orderBy('detail.id','DESC')` will sort details from highest ID to lowest one, and `groupBy('master.id')` is responsible for making sure that only a single detail is displayed on each master. Therefore, only a single detail with highest ID per master is selected. – Rendy Eko Prastiyo Feb 20 '17 at 02:36
yes, i have tried. only using `groupBy('master.id')` i get single detail with result detail id lower, but i cant get with highest detail ID. `orderBy('detail.id','DESC')` only sort result from `groupBy('master.id')`. @rendyEkoPrastiyo – Youngz ie Feb 20 '17 at 04:21

score 0 · Answer 2 · answered Dec 05 '17 at 10:06

0

To get the latest detail for each master row you ca use following DQL

SELECT m,a 
FROM AppBundle\Entity\Master m
    LEFT JOIN m.detail a
    LEFT JOIN AppBundle\Entity\Detail b 
    WITH a.master = b.master 
    AND a.id < b.id
WHERE b.id IS NULL

answered Dec 05 '17 at 10:06

M Khalid Junaid

63,861
10
90
118

Doctrine get single latest row of Detail for each Master

2 Answers2