Doctrine2 "order by" before "group by"

doctrine-orm, dql, symfony

Solution

To achieve such a result you'd have to end up with following SQL query:

SELECT n.id, n.update_date
FROM (
    SELECT o.id, o.update_date
    FROM orders o 
    ORDER BY o.update_date DESC
) n 
GROUP BY n.id;

Unfortunately Doctrine's DQL doesn't support nested queries in `FROM` clause. However, it allows you to create a custom, native SQL query that can be mapped by ORM:

$rsm = new ResultSetMappingBuilder($em);
$rsm->addRootEntityFromClassMetadata('MyProject\\Order', 'o');

$sql = sprintf('SELECT %s FROM (SELECT ...) GROUP BY ...', $rsm->generateSelectClause());

$query = $em->createNativeQuery($sql, $rsm);
$result = $query->getResult();

Problem

I know that similar question was asked 3 years ago (Doctrine2 Order By before Group By) but maybe from that time something changed. i have 2 entity propertes: ``` orderId orderUpdateDate ``` both as a pair are set to be unique, but for listing I need last update date so sorting should be ``` order by orderUpdateDate DESC ``` and then grouped by orderId so I have orders only once. ``` group by orderId ``` problem is such that when I paste both in one query in query builder I got random listing of orders

Original source