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.
CodePudding user response:
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();
}