6

Is it possible to add a WHERE NOT IN (SELECT...) clause to a SelectQuery object? I was looking at SelectQuery::condition(), and it doesn't seem to allow for this syntax:

public SelectQuery::condition($field, $value = NULL, $operator = NULL)

It only seems to allow for value comparison expressions. Is there another method that provides this syntax?

I'm looking at conditions on the order of thousands of values, so it seems inefficient to have a bunch of AND value <> x conditions.

4 Answers 4

8

What you're looking for is SelectQuery::exists(), unfortunately it doesn't seems to work with "common way" to build such clause:
Normally in a EXISTS clause you put a JOIN on tables outside of your sub query like this:

SELECT n.nid
FROM node n
WHERE EXISTS (
  SELECT NULL
  FROM users u
  WHERE u.uid = n.uid
  AND u.status = 0)

This returns the nodes of blocked users (yes it can be built easily without the EXISTS but it's for teaching purposes ;).

The problem here is that the exists condition doesn't support "outside linking", the u.uid = n.uid part so you have to build a completely independent query which is not very efficient IMHO.
In Drupal you will write this:

$query = db_select('node', 'n')
  ->fields('n', array('nid'));
$subquery = db_select('node', 'n2')
  ->fields('n2', array('nid'))
  ->join('users', 'u', 'n2.uid = u.uid'
  ->condition('u.status', 0);
$query->condition('', $subquery, 'EXISTS');
$result = $query->execute();

You can also use db_query() like you used to do in Drupal 6 and of course SelectQuery::notExists() is available.

2

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

$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));  
  $result = $query->countQuery()->execute()->fetchField();
  drupal_set_message($result);
1
  • Hi, thanks for this idea, it works for me. I was trying to use a subquery object, but since I was referencing a value from the parent query, the code would brake. May 18, 2013 at 16:23
2

Acutally, you can use SelectQuery::condition() for creating subselects like so:

$query = db_select('users', 'u')
  ->fields('u', array('uid'))
  ->condition('u.uid', db_select('users_roles', 'r')->fields('r', array('uid')), 'NOT IN');

If we print this query using the dpq() function of devel it will output:

SELECT u.uid AS uid
FROM 
{users} u
WHERE  (u.uid NOT IN  (SELECT r.uid AS uid
FROM 
{users_roles} r))

I hope this helps ;-)

0

I believe this would help you.

$query->where('n.nid NOT IN (:nids)', array(
    ':nids' => implode(',', $nids)
));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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