db_query() and the IN() operator

Haven’t done a Drupal post in a while!

A little while ago I was porting a Drupal 6 module to Drupal 7, which meant changing over a lot of queries. The new database API had oodles of new features and somewhat different rules, and I wanted to do things by the book. Now, I thought I could leave the simpler queries to be static (ie: using db_query()) instead of dynamic (using db_select()) since db_select() apparently creates a lot of overhead… but a few just wouldn’t work. They were the ones using the IN() operator.

Here’s what my original code looked like:

$res = db_query('SELECT field FROM {table} WHERE cond_field in(@values)',array('@values' => implode(',',$array_of_ints));

My first stab in Drupal 7 was simply to replace the ‘@’ with a ‘:’

$res = db_query('SELECT field FROM {table} WHERE cond_field in(:values)',array(':values' => implode(',',$array_of_ints));

Didn’t work. There was no error, but the query didn’t return any rows. However, it did work when I plunked the imploded array right in the query string, like so:

$res = db_query('SELECT field FROM {table} WHERE cond_field in(' . implode(',',$array_of_ints) . ')');

Not pretty. Not clean. There had to be a better way, but what was the problem? And then it hit me: in Drupal 7 you don’t need to add single quotes around placeholders in the query, even if they represent strings, the API does all that for you if the value isn’t a number. So what was happening was that the value of the IN() clause was taken as one big string, which just happened to consist of comma-separated numbers.

What to do about it? I had to use a dynamic query:

$res = db_select('table')
  ->fields('table',array('field'))
  ->condition('cond_field',$array_of_ints,'IN')
  ->execute();

So there you go. It’s more involved, for sure, and note that the condition takes an array instead of a string, but it works. And it was a good introduction to dynamic queries, which used to scare me a little but now totally don’t. I’ve done far more complicated ones, with merges and subqueries and all sorts of crap, and db_select() can handle it all.