4

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?

Stick it to THE MAN
  • 5,621
  • 17
  • 77
  • 93

4 Answers4

4

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.

Jordan Kasper
  • 13,153
  • 3
  • 36
  • 55
  • this helped me out thanks. I was also looking to get the MAX value of a certain column but your method works wonderfully. Simply order that column desc and findone. Thanks again. – azzy81 Jul 23 '13 at 10:33
  • Sure thing. Like I said, just wanted to mention a possible alternate solution to the business problem versus just how to use `MAX()`. – Jordan Kasper Jul 23 '13 at 13:06
1

Try:

$c = new Criteria();
$c->add(SomeTable::CREATED_AT, '(SELECT MAX('.SomeTable::CREATED_AT.') FROM some_table)', Criteria::CUSTOM);
prodigitalson
  • 60,050
  • 10
  • 100
  • 114
1

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.

lumos0815
  • 3,908
  • 2
  • 25
  • 25
0

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

Kraig1234
  • 43
  • 4