3

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);
}
5

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:

4

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);
}
0

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();
  • This will give an incorrect result. You must innerJoin and remove isNull condition. – user4035 Oct 8 '16 at 9:19

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.