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.