26

What is the proper way to write a query containing 'NOT IN' using a condition statement?

My query is the following one:

SELECT DISTINCT nid FROM node WHERE language NOT IN 
  (SELECT language 
    FROM languages WHERE language = 'ab');

I've tried something like the following:

$query->condition('n.' . $key, $value, 'not in (select language from 
  languages where language = $value)');
  • Maybe I am missing the obvious, but what is the difference between your query and SELECT nid FROM node WHERE language != 'ab'? – Елин Й. Jan 31 '16 at 10:51
38

In the specific example, you should simply write the condition as:

$query->condition('n.language', 'ab', '<>');

In the generic case, where you need to select the rows in a database basing on the values returned from a sub-query, you should consider what follows:

  • "NOT IN" is accepted as operator from SelectQuery::condition(). In fact, the following query would be executed:

    $query = db_select('node', 'n')->fields('n');
    $query->condition('n.nid', array(1, 2, 3), 'NOT IN');
    $nodes = $query->execute();
    
    foreach ($nodes as $node) {
      dsm($node->nid);
    }
    
  • As reported in Conditional clauses ("Subselects"), SelectQuery::condition() accepts also an object implementing SelectQueryInterface as value for $value, such as the one returned by db_select(); the problem is that actually you can just use it when the value of $operator is equal to "IN". See Subselects don't work in DBTNG conditions, except when used as value for IN.

The only way I can see to use the "NOT IN" operator with a sub-query in condition is to:

  • Execute the subquery to get an array
  • Execute the main query setting the condition as in the following snippet

    $query->condition($key, $subquery_result, 'NOT IN');
    

    $subquery_result is the array containing the result of the sub-query.

Otherwise, you could use where() as others said, which accepts a string for the part of the query you need to add.

Keep in mind that db_select() is slower that db_query(); you should use the first when you know the query could be altered by other modules. Otherwise, if other modules are not supposed to use hook_query_alter() to alter your query, you should use db_query().
In the case of accessing nodes, if you need to obtain only the nodes to which a user has access, then you need to use db_select() and add 'node_access' as tag of the query, with SelectQuery::addTag(). For example, blog_page_last() uses the following code.

  $query = db_select('node', 'n')->extend('PagerDefault');
  $nids = $query
  ->fields('n', array('nid', 'sticky', 'created'))
    ->condition('type', 'blog')
    ->condition('status', 1)
    ->orderBy('sticky', 'DESC')
    ->orderBy('created', 'DESC')
    ->limit(variable_get('default_nodes_main', 10))
    ->addTag('node_access')
    ->execute()
    ->fetchCol();

Similar code is used by book_block_view().

$select = db_select('node', 'n')
  ->fields('n', array('title'))
  ->condition('n.nid', $node->book['bid'])
  ->addTag('node_access');
$title = $select->execute()->fetchField();
  • Here is an example of a subquery for a views custom filter I've written: link – Roger Nov 15 '16 at 15:38
  • 1
    "Subselects don't work in DBTNG conditions, except when used as value for IN" is fixed in Drupal 8.3 – Jonathan Nov 24 '16 at 15:35
3

When writing complex queries you should definitely use db_query() instead of db_select().

  1. You can't write a NOT IN clause with a subquery with the current Drupal database API (it's a know issue being worked out).
  2. If you don't need your query to be dynamic (hence rewritten by others modules), don't bother trying to write such a complex one with db_select().
  3. Subqueries aren't well supported yet (see a previous answer of mine) and if you're used to write SQL it's way easier to use db_query().

Regarding your query, I'm not sure why you want to use a subquery (unless you simplified your exemple) ? You can write it easily like this:

SELECT nid 
FROM node n INNER JOIN languages l ON n.language = l.language
WHERE language NOT IN ('ab')

DISTINCT isn't necessary as nid is a primary key so it won't be duplicated.

  • 2
    Regarding #2, the OP is selecting nodes. AFAIK db_select() is the only way to provide any required 'node_access' tag, in which case db_select() would be the only choice. – keithm Dec 29 '11 at 23:32
2

There's also where() which allows to add an arbitrary where condition to the query.

Example:

$query->where('n.language NOT IN (SELECT language FROMlanguages WHERE language = :lang)', array(':lang' => $value));

As keithm mentioned, you must use db_select() and addTag('node_access') when selecting nodes which are then displayed to users.

1

An easier way to use db_select with a NOT IN subselect is just to use the little known

$query->where

to add an arbitrary where condition.

e.g:

  // Count query for users without rid 3
  $query = db_select('users', 'u');
  $query->fields('u', array('uid'));
  $query->where('u.uid NOT IN(select uid from {users_roles} where rid = :rid)', array(':rid' => 3));  
  $count = $query->countQuery()->execute()->fetchField();
  drupal_set_message($count);
0

Where $subquery_values is an array of $key => $nid format as result of a subquery

$query->condition('node.nid', array_values($subquery_values), "NOT IN");

it works fine.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.