Finding Groupwise Maximum In Doctrine

Question

How do you find a groupwise maximum, or the row containing the maximum value, in Doctrine? In SQL, I would typically do this using a self join as described here.

While it's possible to set up a self relation in Doctrine, are there any better ways to do this?

Answer 1

Example of groupwise max:

$query = Doctrine_Query::create()
     ->select("txs.id, txs.amount, txs.valid_from")
     ->from("Tx txs")
     ->where("txs.amount = (SELECT MAX(transact.amount) FROM tx transact WHERE txs.id = transact.id)");

Example of row containing maximum:

$query = Doctrine_Query::create()
 ->select("txs.id, txs.amount, txs.valid_from")
 ->from("Tx txs")
 ->where("txs.id = (SELECT transact.id FROM tx transact WHERE transact.amount = (SELECT MAX(transactx.amount) FROM tx transactx))");

These are probably not the only ways (or most clean), but I just tested both and they work.

Answer 2

This question is really old but it ranks highly on Google for "doctrine groupwise max" so I thought I'd add my solution.

In my case I had a parent Entity with many children and I wanted to select fields from the child with the highest ID.

$qb
    ->select('child1.field')
    ->from(Entity::class, 'entity')
    ->join('entity.children', 'child1')
    ->leftJoin('entity.children', 'child2', 'WITH', 'child1.entity=child2.entity AND child1.id<child2.id')
    ->where('child2.id IS NULL')
;