From Select in doctrine 2

Question

How do I do this in doctrine2 QB or DQL.

SELECT * FROM

( select * from my_table order by timestamp desc ) as my_table_tmp

group by catid

order by nid desc

Answer 1

I think your query is the same as:

SELECT *
FROM my_table
GROUP BY catid
    HAVING timestamp = MAX(timestamp)
ORDER BY nid DESC
;

If it is correct, then you should be able to do:

$qb->select('e')
    ->from('My\Entities\Table', 'e')
    ->groupBy('e.catid')
    ->having('e.timestamp = MAX(e.timestamp)')
    ->orderBy('nid', 'DESC')
;

Or, directly using DQL:

SELECT e
FROM My\Entities\Table e
GROUP BY e.catid
    HAVING e.timestamp = MAX(e.timestamp)
ORDER BY e.nid DESC
;

Hope this helps and works! ;)