5
$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?

2 Answers 2

14

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();
16
  • i get Placeholders must have a trailing [] if they are to be expanded with an array of values. and I cant really figure out what the correct syntax is (using the static one)
    – Alex
    May 19, 2016 at 10:08
  • @Alex Updated the answer, the change record is here if you're interested
    – Clive
    May 19, 2016 at 10:11
  • Thanks, I've already guessed it has to look like this but it still throws an error: Invalid parameter number: number of bound variables does not match number of tokens: SELECT {nid} FROM {acl_node} WHERE grant_update = :grant AND acl_id IN (:acl_ids__0, acl_ids__1);. Looks like a drupal bug?
    – Alex
    May 19, 2016 at 10:13
  • 1
    @Alex {nid} tells Drupal nid is a table name, while it is a table row name. Remove those parentheses from your code.
    – apaderno
    May 19, 2016 at 10:17
  • 2
    It should be fetchCol() if you want multiple results, fetchField() if you want a single field from a single result, that's correct
    – Clive
    May 19, 2016 at 10:36
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.

11
  • The colon shouldn't make any difference, it's passed straight through to PDO which doesn't mind if it's missing...
    – Clive
    May 19, 2016 at 12:06
  • Drupal always use placeholders like :grant in the query and in the array. I have never checked if removing the colon gives an error, actually.
    – apaderno
    May 19, 2016 at 12:15
  • Yeah you're right, Drupal's convention is to use the colon unconditionally, but, at least in D7, it wasn't necessary because PDO doesn't care and the array was passed almost straight through. I can't imagine it would be different in D8, it would surely be more work to remove that ability than to just let it work as 'expected'. Worth a test though
    – Clive
    May 19, 2016 at 12:18
  • In the example given on PDOStatement::execute() I see the colon is used in both the places: $sth = $dbh->prepare('SELECT name, colour, calories FROM fruit WHERE calories < :calories AND colour LIKE :colour'); $sth->bindParam(':calories', $calories, PDO::PARAM_INT); $sth->bindValue(':colour', "%{$colour}%");.
    – apaderno
    May 19, 2016 at 12:27
  • 1
    Let's not use the word errors then. To split hairs, not even using {nid} is causing the error the OP is seeing, but I think it is better to point out that using {nid} is not something the OP should do. :-)
    – apaderno
    May 19, 2016 at 13:10

Your Answer

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

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