Tuesday, 19 April 2011

Conditions for using where

Just to help you avoid spending as many hours as I have trying to force a nasty little query with a subquery to do what I wanted...

If you want to have a WHERE clause that contains something like "fss.entity_id = n.nid", you can't use the condition function. In other words, you can't do this:

$query->condition('fss.entity_id', 'n.nid');

You have to use this:

$query->where('fss.entity_id = n.nid');

Because the first version gets translated into fss.entity_id = 'n.nid' in the SQL, which will never be true.

In debugging this it slowly became obvious that something fundamental was wrong - because when I output the $query string and then replaced the placeholders with the right arguments, and shoved it straight into the MySQL server - it worked.

Eventually it dawned on me to use the Devel query output to see what was actually being called and found the error - though by that time I was looking for it, having exhausted every other option.

Here's a quick tip for outputting the entire contents of an object which contains public and private properties:

dpm(print_r($object, TRUE));

I could have done without that today.


Sergey Eremenko said...

devel module also has DPQ method that print sql qeury from drupal query object ;) it's pretty handy to use with comlicated queries

Adaddinsane said...

Excellent - thanks.