Using the entity query API to identify entities with correlated values

Question

Imagine two Drupal 9 custom entity types named a and b.

Entity type a has two fields a_f1 and a_f2.

Entity type b has a field b_a which is an entity reference with a target type of a and unlimited cardinality.

How does one create an entity query which finds all entities of type b at least one of whose b_a entity references points to an a entity which has value 'v1' for its field a_f1 and value 'v2' for its field a_f2?

It's not appropriate in this case to find a b which has one entity reference pointing to an a entity with a_f1 of 'v1' but a_f2 of 'something else', and another entity reference pointing to a different a entity with a_f2 of 'v2' but a_f1 of 'wrong value'.

I could do this in my sleep with direct SQL queries, but that's frowned on in the Drupal world. I'm having a hard time finding the guidance for doing this the Drupal Way in the official Drupal documentation.

Does the Drupal entity query API support this functionality?

Answer 1

The following code would achieve what described in the question.

$result = \Drupal::entityQuery('b')
  ->condition('b_a.entity:a.a_f1', 'v1')
  ->condition('b_a.entity:a.a_f2', 'v2')
  ->execute();

In the first string passed to condition():

• b_a is the field name
• .entity:a says that field is an entity reference for an a entity
• a_f1 and a_f2 are fields for the a entity

For more information about the values accepted for the first condition() argument, see QueryInterface::condition().

In the case the b_a field is a multi-value field, both the conditions would refer to the same delta, which means the value of the a_f1 and A_f2 fields are taken from the same a entity.
The code I wrote at the beginning of the answer returns all the entities for which both the conditions are met. If you want to get all the entities for which one of the conditions is true, the following code should be used.

$query = \Drupal::entityQuery('b');
$group = $query->orConditionGroup()
  ->condition('b_a.entity:a.a_f1', 'v1')
  ->condition('b_a.entity:a.a_f2', 'v2');
$result = $query->condition($group)->execute();