Issue when comparing using expressions

[jspasiuk]

Hello,

I don't really know if this is possible to solve using this ORM:

I'm trying to compare a timestamp column with an mysql expression, and i always get an error from PDO, saying is not a timestamp valid format.

This is the line I'm having issues with: $object->where_raw('DATE(fec_log) >= ?',array('DATE_SUB(NOW(), INTERVAL 30 DAY)'));

Is this possible to compare timestamp columns or any other column with expressions or functions in this ORM?

I looked into the manual and docs, and I couldn't found anything about this.

Thanks a lot

[treffynnon]

where_raw() is raw on the left-hand side only and the parameters in the array are bound using PDO so they get the usual escaping you would expect from a standard where() clause.

There are two ways that you can workaround this in Idiorm using the code that you've supplied:

$object->where_raw(
  'DATE(fec_log) >= ?',
  array('DATE_SUB(NOW(), INTERVAL 30 DAY)')
);

Option 1

As you're not actually binding any values in this instance you can use where_raw() with no second argument. This is the preferred option.

$object->where_raw(
  'DATE(fec_log) >= DATE_SUB(NOW(), INTERVAL 30 DAY)'
);

You only need to supply an array of arguments when you are binding values and in the query you posted you've only got fixed values - DATE_SUB(NOW(), INTERVAL 30 DAY).

So, let's say that you want an application user to be able to supply their own value for the INTERVAL in your query - you could achieve that like this:

$object->where_raw(
  'DATE(fec_log) >= DATE_SUB(NOW(), INTERVAL ? DAY)',
  array(30)
);

Option 2

Another way to get around this is to use a raw_query()^DOCs:

$people = ORM::for_table('person')->raw_query(
  'SELECT fec_log
    FROM logs
   WHERE DATE(fec_log) >= DATE_SUB(NOW(), INTERVAL 30 DAY)'
)->find_many();

And, again an example of a user supplying a value for the interval and binding it using PDO:

$people = ORM::for_table('person')->raw_query(
  'SELECT fec_log
    FROM logs
   WHERE DATE(fec_log) >= DATE_SUB(NOW(), INTERVAL :interval DAY)', 
  array('interval' => 30)
)->find_many();

However, do be mindful of the warning in the manual:

Using raw_query is advanced and possibly dangerous, and Idiorm does not make any attempt to protect you from making errors when using this method. If you find yourself calling raw_query often, you may have misunderstood the purpose of using an ORM, or your application may be too complex for Idiorm. Consider using a more full-featured database abstraction system.

[jspasiuk]

This answer is gold, thanks a lot @treffynnon treffynnon ! I was looking to avoid using 'where_raw' , but you are right, no user input in that part of the query, so it should be no problem to use it like that. Thanks again and thanks for the quick reply!