Propel ORM including MAX in criteria

Question

I am writing a query using the Propel ORM

The query is of the form:

select * from some_table where some_table.created_at = (SELECT MAX(some_table.created_at) from some_table);

I got this far:

 $c = new Criteria();
 $c->addSelectColumn('MAX('.self::CREATED_AT.')');

Anyone knows how to use Propel to do this, to save me writing RAW SQL?

Answer 1

If you jhust want to know how to add custom WHERE values, then the solution by @prodigitalson should work, but I wonder why you are doing it this way in the first place versus just:

$recs = SomeTableQuery::create()->orderByCreatedAt()->findOne();

...which will get you the latest created record.

Answer 2

Try:

$c = new Criteria();
$c->add(SomeTable::CREATED_AT, '(SELECT MAX('.SomeTable::CREATED_AT.') FROM some_table)', Criteria::CUSTOM);

Answer 3

This way is not mentioned - so this works for me:

MyTable::create() 
->select('max_id') 
->addAsColumn('max_id', 'MAX(id)')
->findOne();

find() returns a collection of objects so use findOne()

addAsColumn() returns just the selected column.

Answer 4

I couldn't get ->withColumn('MAX(itemrevisionID)') to work, so the work around was this ->orderByitemrevisionID('desc')