How do I add a condition to a query?

Question

$acl_ids = array(1,2,3);
$database = \Drupal::database();

$result = $database
  ->query("SELECT {nid} FROM {acl_node} WHERE grant_update = :grant", 
    ['grant' => 1,])
  ->condition('acl_id', $acl_ids, 'in')
  ->fetchField();

Unfortunately the ->condition part does not work, since I get the following error:

Call to undefined method Drupal\Core\Database\Statement::condition()

What method should I instead use?

Answer 1

Conditions are only used in dynamic queries; the query() method is for running static SQL.

The dynamic version would be something like:

$result = $database
  ->select('acl_node', 'n')
  ->fields('n', ['nid'])
  ->condition('grant_update', 1)
  ->condition('acl_id', $acl_ids, 'IN')
  ->execute()
  ->fetchField();

And the static:

$result = $database
  ->query("SELECT nid FROM {acl_node} WHERE grant_update = :grant AND acl_id IN (:acl_ids[])", [':grant' => 1, ':acl_ids[]' => $acl_ids])
  ->fetchField();

Answer 2

The code you wrote is equivalent to the Drupal 7 code that used db_query(). You cannot use condition() with the object returned from \Drupal::database()->query(). I would also change your code in two points:

• You are using {nid} in your query, which is what you should use when nid is a table name, not a table field name ^*
• You are using ['grant' => 1,] as array of arguments, when it should be [':grant' => 1,] basing on Drupal/PHP documentation (Notice the colon before the table field name.)

If you don't want to use functions that are going to be deprecated in Drupal 9, see the answer given by Clive. I will add that you can still use db_query() or db_select() also in Drupal 8. In fact, Drupal 8.2.x still contains 160 calls to db_query() and 128 calls to db_select().

$acl_ids = array(1,2,3);

$result = db_query("SELECT nid FROM {acl_node} an WHERE an.grant_update = :grant AND an.acl_id IN ( :ids[] )", [':grant' => 1, ':ids[]' => $acl_ids]);

foreach ($result AS $row) {
  // access the node ID as $row['nid'].
}

This code is similar to the one used in history_read_multiple().

  $result = db_query('SELECT nid, timestamp FROM {history} WHERE uid = :uid AND nid IN ( :nids[] )', array(
    ':uid' => \Drupal::currentUser()->id(),
    ':nids[]' => array_keys($nodes_to_read),
  ));
  foreach ($result as $row) {
    $nodes_to_read[$row->nid] = (int) $row->timestamp;
  }
  $history += $nodes_to_read;

Notes

Using {nid} would cause a wrong query being executed if Drupal is using prefixes for the table names. Still, it is not correct to use {nid} if you don't want Drupal to alter it as it alters a table name.