Score:2

Using the entity query API to identify entities with correlated values

ai flag

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?

Score:3
us flag

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();
ai flag
Awesome! Thanks very much. Apparently I don't have permission on this site to upvote your answer or I'd do it, but I have marked it as the correct answer (after creating and testing a repro case). This does exactly what I needed it to do. Is there documentation somewhere which explains that the API will ensure that both values must come from the same referenced entity? And does it explain how to construct the query for the case where I need to find `b` entities which reference an `a` which has `a_f1 = 'v1'` **and** has an `a` where `a_f2 = 'v2'` but they don't have to be the same `a` entities?
apaderno avatar
us flag
I will expand the answer tomorrow (as for me it's almost midnight), but I can say that for the code I shown, both the conditions must be true for each returned entity. The answer doesn't consider a multi-value field, at the moment.
ai flag
I have confirmed (by experimentation) that this approach works the same way for multi-valued multi-occurrence fields (the specified values must all occur within the same occurrence of the field for the parent entity to be included in the query results). If you know where the documentation explaining all this is hiding, please post a link. I promise to be suitably embarrassed if it's sitting in plain sight and I missed it. :-)
apaderno avatar
us flag
I expanded the answer. What the documentation says is that two conditions for the same multi-value field refers to the same delta. it means that the first condition for `b_a.entity:a` cannot refer to the first item and the second condition for `b_a.entity:a` to the third item.
ai flag
I'm going to ignore the instructions here, which are telling me to avoid posting "thanks" comments. :-) I very much appreciate the information you have provided, as well as the link to the documentation (which I should have found on my own). And now it looks like I have (by posting comments) acquired enough "reputation" points to upvote your answer, which I have done. Thanks!
apaderno avatar
us flag
Sometimes, the more obvious documentation page isn't the place you find the information you are looking for. That's why we sometimes don't look at that. It happened to me too.
mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.