How to use MySQL functions in Propel

Question

I want to select records that are 1 month old or newer.

The query is: SELECT * FROM foobar WHERE created_at > DATE_SUB(curdate(), INTERVAL 1 MONTH)

Using Propel in Symfony, I do:

$c = new Criteria
$c->add(FoobarPeer::CREATED_AT, "DATE_SUB(curdate(), INTERVAL 1 MONTH)", Criteria::GREATER_THAN);

What Propel generates is: SELECT * FROM foobar WHERE created_at > 'DATE_SUB(curdate(), INTERVAL 1 MONTH)' - in other words, it puts the MySQL function in single quotes, which makes it a (meaningless) string and I get no records.

What I've done for now is:

$c->add(FoobarPeer::CREATED_AT, "created_at > DATE_SUB(curdate(), INTERVAL 1 MONTH)", Criteria::CUSTOM);

But I don't want to use custom workarounds unless I have to. Any hints besides using Criteria::CUSTOM?

Pemberton

Since Propel has greatly evolved since this question has been answered, here are some considerations (written by the Propel project leader), telling when it is relevant to use an ORM, or raw SQL : propel.posterous.com/how-can-i-write-this-query-using-an-orm – Rhombus

Answer 1

I think there is no option more than using Criteria::CUSTOM or doing a custom SQL query like this:

$con = Propel::getConnection(DATABASE_NAME);

$sql = "SELECT foobar.* FROM foobar WHERE created_at > DATE_SUB(curdate(), INTERVAL 1 MONTH)";  
$stmt = $con->prepare($sql);
$stmt->execute();

$books = FoobarPeer::populateObjects($stmt);

That's because Propel tries to be DBMS-agnostic, to help migration by doing a simple configuration value change, so it doesn't have any DBMS specific functions built in.

Triple

Answer 2

just replace the mysql date code you are using there with a precalculated php variable that has that date in it already.

i.e.

$monthAgo = '2008-10-03';
$c = new Criteria
$c->add(FoobarPeer::CREATED_AT, $monthAgo, Criteria::GREATER_THAN); 

obviously, you should dynamically calculate the date in php, rather than hard coding it, but you get the picture.

Looksee

Yes I could, but I would rather use mysql functions - php has a weak support for date arithmetic. – Pemberton