Symfony Doctrine Query Builder Where last in arraycollection

Question

I want to use symfony's query builder and add a where to the last item in an array collection

$query = $em->getRepository('RlBookingsBundle:Booking')->createQueryBuilder('b')
        ->select('b, v, c, ca, q')
        ->leftJoin('b.vehicle', 'v')
        ->leftJoin('b.customer', 'c')
        ->leftJoin('c.address', 'ca')
        ->leftJoin('b.quote', 'q')
        ->leftJoin('b.history', 'h') //This is an array collection
        ->orderBy('b.edited', 'DESC')
    ;

I want to use only the latest value from history as it is a log but only the most recent entry is valid

->where('h.status IN (:status)')
  ->setParameter('status', [7]);

Will return all results with h.status = 7 but I would like it to only query the most recent result. Is there anyway to do this?

I tried a groupby on the history field but this seems to groupby with data from the first entry, even if I add an orderby to it.

have you a date from where you can distinguish the most recent record? — DonCallisto, Aug 25 '15 at 16:50
Yes, the history rows have dates, or id with each one the latest entry would be the highest. Ideally I would like to leftJoin a subquery, but I don't think doctrine can handle this. or just left join the latest result from the history entity. — user1806445, Aug 25 '15 at 16:54

score 1 · Answer 1 · answered Aug 27 '15 at 11:22

If the results you get are already ok, but you only want the first, you could just use

...
->setMaxResults(1)
...

If you want to order by history ID desc, you may want to add another orderBy clause before the existing one

...
->orderBy('h.id', 'DESC')
->orderBy('b.edited', 'DESC')
...

If it's more complex than that, I strongly suggest you perform a separate query to get the desired record(s) from history, and THEN use it as a filter, instead of the leftJoin.

Symfony Doctrine Query Builder Where last in arraycollection

1 Answers1