How to use NOT EXISTS condition?

Question

I have 2 tables:

|users |       |users_roles|
--------       -------------
| uid  |       |uid| rid   |
--------       -------------
|  1   |       | 1 |   3   |
|  2   |   

For the authenticated users the role is missing in users_roles table. User with uid=1 is an admin. I want to write the following query in Drupal DB API:

SELECT
        uid
FROM
        users
WHERE
        NOT EXISTS(
SELECT
        NULL
FROM
        users_roles
WHERE
        users_roles.uid=users.uid
)

I tried with this code, but don't know, how to write the query for notExists condition.

$query = db_select('users');

$query->addField('users', 'uid');

$query_exists = db_select('users_roles');
$query_exists->addExpression('NULL');
$query_exists->condition("users_roles.uid=users.uid"); //<- how to do this. Doesn't work.

$query->notExists($query_exists);
$result = $query->execute();
foreach ($result as $record) {
        var_dump($result);
}

Answer 1

Why not use a simple db_query()? In many cases, this is a perfectly adequate way of doing things; you can still use named parameters, and you can build your query as you'd like. It actually performs (very) slightly better than a db_select() anyways (see: http://drupal.org/node/1067802 and https://drupal.stackexchange.com/a/1205/26).

The only reason to not use a db_query() for a particular select query is if you need the query to be easily extended or changed by some other module.

$result = db_query('SELECT uid
  FROM users
  WHERE NOT EXISTS(SELECT NULL
    FROM users_roles
    WHERE users_roles.uid = users.uid
  )')->fetchCol();

Anyways, your particular query is a little more complex than normal, but can still be done using D7's DB API. See:

• http://drupal.org/node/1001242#comment-3921430
• Database query with notExists ($query->condition('', $subquery, 'EXISTS');)

Answer 2

I did it! Here is the code:

$query = db_select('users');

$query->addField('users', 'uid');

$query_exists = db_select('users_roles');
$query_exists->addExpression('NULL');
$query_exists->where("users_roles.uid=users.uid");

$query->notExists($query_exists);
$result = $query->execute();
foreach ($result as $record) {
        var_dump($result);
}

Answer 3

I had to use something similar:

$query = db_select('users', 'u');
$query->leftJoin('users_roles', 'ur', 'u.uid = ur.uid');

$result = $query->fields('u', array('name'))
  ->condition('u.name', '%' . db_like($string) . '%', 'LIKE')
  ->isNull('ur.rid')
  ->range(0, 10)
  ->execute();