Using SQL built-in functions in doctrine queries

This short article describes a small trick that enables using SQL built-in functions inside doctrine queries. The trick is to use ticks inside the query.

example

We want to update all records of a table by setting one of its column to be rounded to 2 decimal points. By default, doctrine will ignore 2 sign which is after the comma inside the ROUND function.

return Doctrine_Query::create()
  ->update('Product')
  ->set('feature_b', 'ROUND(feature_a, 2)')

will generate the following query:

UPDATE product SET feature_b = ROUND(feature_a)

which is NOT what we want. We only need to add ticks surrounding the value to be rounded:

return Doctrine_Query::create()
  ->update('Product')
  ->set('feature_b', 'ROUND(`feature_a`, 2)')

and the generated query will satisfy our needs:

UPDATE product SET feature_b = ROUND(`feature_a`, 2)