Doctrine Query Language Latest Row Per Group

user_16002765 0
Viewed 221

I am trying (and failing) to turn this SQL statement into using Doctrine QB. I'm attempting to get all the latest order records for each customer from the table 'orders'.

SELECT t1.* FROM order t1
  JOIN (SELECT id, MAX(timestamp) timestamp FROM order GROUP BY customer) t2
    ON t1.timestamp = t2.timestamp;

I've tried almost every relevant solution I found here and the closest I got was

$qb->join('App\Entity\Order', 'b', 'WITH', 'ro.date> b.date')

However it seems to work for the first 2 results, the following results just seem to grab whatever. Note that I'm using query builder because I have a lot of conditional statements to add "andWhere" and "orWhere" etc.

symfony doctrine greatest-n-per-group mysql php
1 Answers

this should get you the result you want

public function selectLastOrdersPerCustomer(){
    return $this->createQueryBuilder('o')
        ->leftJoin(Order::class, 'o2', 'WITH', 'o2.customer = o.customer AND o.timestamp < o2.timestamp')
        ->where( 'o2.timestamp IS NULL' )
        ->innerJoin('o.customer', 'c')
        ->addSelect('c')
        ->groupBy('c.id')
        ->orderBy('o.timestamp', 'DESC')
        ->getQuery()
        ->getResult();
}
Admin1
Related