db_select subquery "In" condition causes PDOexception This source should get the 4 most popular top level categories.

The result query works fine when run in phpmyadmin so I must assume the problem is in db_select somewhere.

The resulting query should be: SELECT tid FROM taxonomy_index,name WHERE tid IN(SELECT data.tid FROM taxonomy_term_data AS data JOIN taxonomy_term_hierarchy AS hierarchy ON data.tid=hierarchy.tid WHERE vid=2 AND parent=0) ORDER BY COUNT(nid) ASC LIMIT 0,4

&lt;?php
$vocab = 2;

$subquery = db_select('taxonomy_term_data','data')
  -&gt;fields('data',array('tid'));
$subquery
  -&gt;join('taxonomy_term_hierarchy', 'hierarchy', 'data.tid=hierarchy.tid');
$subquery = $subquery
  -&gt;condition('data.vid',$vocab)      // vid of vocab we want to show
  -&gt;condition('hierarchy.parent',0);  // tid of parent (We want toplevel terms)

$toplvlcats = db_select('taxonomy_index','index');
$toplvlcats
  -&gt;join('taxonomy_term_data','data','index.tid=data.tid');
$toplvlcats = $toplvlcats
  -&gt;fields('index',array('nid'))
  -&gt;fields('data',array('name'))
  -&gt;condition('index.tid',$subquery,'IN') // Sift through toplevel terms
  -&gt;orderBy('COUNT(nid)','ASC')           // For the one with the most posts
  -&gt;range(0,4)
  -&gt;execute();
?&gt;


The error message is:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'index INNER JOIN taxonomy_term_data data ON index.tid=data.tid WHERE (index.tid' at line 1: SELECT index.nid AS nid, data.name AS name FROM {taxonomy_index} index INNER JOIN {taxonomy_term_data} data ON index.tid=data.tid WHERE (index.tid IN (SELECT data.tid AS tid FROM {taxonomy_term_data} data INNER JOIN {taxonomy_term_hierarchy} hierarchy ON data.tid=hierarchy.tid WHERE (data.vid = :db_condition_placeholder_0) AND (hierarchy.parent = :db_condition_placeholder_1) )) ORDER BY COUNT(nid) ASC LIMIT 4 OFFSET 0; Array ( [:db_condition_placeholder_0] =&gt; 2 [:db_condition_placeholder_1] =&gt; 0 ) in eval() (regel 25 van /home/j/www/modules/php/php.module(80) : eval()'d code).
The only problem with your query is that you're using index as a table alias...this is a MySQL reserved word and although my instinct is that PDO should escape this, perhaps it doesn't.

The following code (with the aliases renamed) runs without error:

$vocab = 2;

$subquery = db_select('taxonomy_term_data','ttd')
  -&gt;fields('ttd',array('tid'));
$subquery
  -&gt;join('taxonomy_term_hierarchy', 'th', 'ttd.tid=th.tid');
$subquery = $subquery
  -&gt;condition('ttd.vid',$vocab)      // vid of vocab we want to show
  -&gt;condition('th.parent',0);  // tid of parent (We want toplevel terms)

$toplvlcats = db_select('taxonomy_index','ti');
$toplvlcats
  -&gt;join('taxonomy_term_data','ttd','ti.tid=ttd.tid');
$toplvlcats = $toplvlcats
  -&gt;fields('ti',array('nid'))
  -&gt;fields('ttd',array('name'))
  -&gt;condition('ti.tid',$subquery,'IN') // Sift through toplevel terms
  -&gt;orderBy('COUNT(nid)','ASC')           // For the one with the most posts
  -&gt;range(0,4)
  -&gt;execute();