I'd be surprised if this hasn't been reported/fixed before, so very open to the idea of me mis-reading the API documentation!
When using WHERE ... NOT IN (values), it looks like db_query is adding quotes around the outside of the values - hard to explain, an example will help:

$queryResult = db_query( "SELECT DISTINCT(ti.tid),name FROM {taxonomy_index} ti INNER JOIN {taxonomy_term_data} ttd ON ti.tid=ttd.tid WHERE nid IN (SELECT nid FROM {taxonomy_index} WHERE tid IN (:tids)) AND ti.tid NOT IN (:tids)" , array( ':tids' => implode( ',' , array( 1,2,3,4 ) ) ) );

devel shows that the following query is being executed:

SELECT DISTINCT(ti.tid),name FROM taxonomy_index ti INNER JOIN taxonomy_term_data ttd ON ti.tid=ttd.tid WHERE nid IN (SELECT nid FROM taxonomy_index WHERE tid IN ('1,2,3,4')) AND ti.tid NOT IN ('1,2,3,4')

Quotes in the 'not in' constraints shouldn't be present, the query should be:

SELECT DISTINCT(ti.tid),name FROM taxonomy_index ti INNER JOIN taxonomy_term_data ttd ON ti.tid=ttd.tid WHERE nid IN (SELECT nid FROM taxonomy_index WHERE tid IN (1,2,3,4)) AND ti.tid NOT IN (1,2,3,4)

Comments

pamatt’s picture

pamatt
Location Brussels
commented

You use
array( ':tids' => implode( ',' , array( 1,2,3,4 ) )
in the argument parameter of db_query(). This syntax is basically equivalent to
array( ':tids' => '1,2,3,4' ) )
which is exactly what you get in the executed query.

You'll probably want to execute 

$queryResult = db_query( "SELECT DISTINCT(ti.tid),name FROM {taxonomy_index} ti INNER JOIN {taxonomy_term_data} ttd ON ti.tid=ttd.tid WHERE nid IN (SELECT nid FROM {taxonomy_index} WHERE tid IN (:tids)) AND ti.tid NOT IN (:tids)" , array( ':tids' => array( 1,2,3,4 ) ) );
pamatt’s picture

pamatt
Location Brussels
commented
Issue summary: View changes
Status: Active » Closed (works as designed)