Perform a query with an entity field condition with multiple values

Question

I have a content type that has a entity reference field that allows users to add multiple taxonomy terms in that field. I'm attempting to perform queries that grab nodes that have a specific set of taxonomy terms within that field.

Using one value in that field works fine, like so.

    $query = \Drupal::entityQuery('node')
        ->condition('status', NODE_PUBLISHED)
        ->condition('type', 'custom_type')
        ->condition('custom_taxonomy', 2)
        ->sort('field_last_name', DESC);

Where 2 is the id of the term I'm searching for. However when I attempt to search for nodes that contain two specific terms like so,

    $query = \Drupal::entityQuery('node')
        ->condition('status', NODE_PUBLISHED)
        ->condition('type', 'custom_type')
        ->condition('custom_taxonomy', [2,8])
        ->sort('field_last_name', DESC);

I receive the error

Invalid parameter number: number of bound variables does not match number of tokens:

I've also attempted

    $query = \Drupal::entityQuery('node')
        ->condition('status', NODE_PUBLISHED)
        ->condition('type', 'custom_type')
        ->condition('custom_taxonomy', [2,8], 'IN')
        ->sort('field_last_name', DESC);

Which doesn't fail, but doesn't provide the intended results. It displays every node that has either term 2 OR term 8. Instead of term 2 AND term 8 as intended. How would I perform a query that checks if a node has multiple specific values in an entity reference field?

Answer 1

Use two separate andConditionGroup():

$query = \Drupal::entityQuery('node')
  ->condition('status', NODE_PUBLISHED)
  ->condition('type', 'custom_type');
$and = $query->andConditionGroup();
$and->condition('custom_taxonomy', 2);
$query->condition($and);
$and = $query->andConditionGroup();
$and->condition('custom_taxonomy', 8);
$query->condition($and);
$result = $query->execute();

This works no matter how many terms are in the field or in which delta they are.

Edit

This results in this SQL:

SELECT base_table.vid AS vid, base_table.nid AS nid
FROM 
{node} base_table
INNER JOIN {node_field_data} node_field_data ON node_field_data.nid = base_table.nid
INNER JOIN {node__custom_taxonomy} node__custom_taxonomy ON node__custom_taxonomy.entity_id = base_table.nid
INNER JOIN {node__custom_taxonomy} node__custom_taxonomy_2 ON node__custom_taxonomy_2.entity_id = base_table.nid
WHERE  (node_field_data.status = '1') AND (node_field_data.type = 'custom_type') AND( (node__custom_taxonomy.custom_taxonomy_target_id = '2') )AND( (node__custom_taxonomy_2.custom_taxonomy_target_id = '8') )

Answer 2

To do complex queries as you asked, you will need to use a condition group and to query the delta.

$query = \Drupal::entityQuery('node');
$query->condition('status', NODE_PUBLISHED)
  ->condition('type', 'custom_type')
  ->condition('custom_taxonomy', [2, 8], 'IN')
  ->condition('custom_taxonomy.%delta', 2, '=')
  ->sort('field_last_name', DESC);
$or = $query->orConditionGroup();
$or->condition('custom_taxonomy.0.target_id', 2);
$or->condition('custom_taxonomy.0.target_id', 8);
$query->condition($or);

See QueryInterface::condition documentation.

Answer 3

$taxonomy_term = 'taxonomy_term';
    $vid = 'name_taxon';
    $terms = $this->entity_type_manager->getStorage($taxonomy_term)
      ->loadTree($vid);

foreach ($terms as $term) {
  $term_data[] = [
    "vid" => $term->vid,
    "name" => $term->name,
  ];
}

Answer 4

Drupal 9 looks like this

$userStorage = $this->entityTypeManager->getStorage('user');

$userQuery = $userStorage->getQuery();
$result_user_ids = $userQuery->accessCheck(FALSE)
  ->condition('field_user_nodes.target_id', [1,2,4], 'IN')
  ->condition('status', 1)
  ->execute();
// Above ^^ 
return $userStorage->loadMultiple($result_user_ids);