EntityQuery where multi-value field contains a value

Question

I've been programming for 15 years, 10 with PHP, but on my 1st Drupal project. Using Drupal 9.

I'm trying to retrieve an entity (custom content type) that has 2 multi-value entity (user) fields. If the value I have is in one field I want to get the other one. The problem is I can't figure out how to ask for the entity when the scalar value I have is in the list. Here's what I've got:

return \Drupal::entityQuery('node')
  ->condition('type', 'osj_relationships')
  ->condition('field_supervised_reps', [$userId], 'IN')
  ->execute();

Which generates 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__field_supervised_reps" "node__field_supervised_reps" ON "node__field_supervised_reps"."entity_id" = "base_table"."nid"
WHERE ("node_field_data"."type" = 'osj_relationships') AND ("node__field_supervised_reps"."field_supervised_reps_target_id" IN ('2458'))'

The 2nd part of the WHERE clause is looking for the field value in my parameter. I need it to look for my parameter in the field. I've also tried CONTAINS but that seems to assume the field value is a string rather than an array. If I could turn that WHERE clause around it would be perfect. ie.

(('2458') IN "node__field_supervised_reps"."field_supervised_reps_target_id") 

I suppose I could use 'CONTAINS' if I could implode the field list but that would risk false hits.

Any suggestions?

Answer 1

You don't even have to use IN here, only if you have multiple $userIds, otherwise a simple = operator will work:

return \Drupal::entityQuery('node')
  ->condition('type', 'osj_relationships')
  ->condition('field_supervised_reps', $userId)
  ->execute();

The generated query will actually work how you'd like. It works because each value of a multi-value field is stored in its own row in the database.

For example, given a multi-value entity reference field, field_tags, added to article nodes, if you have an article (with id = 111), referencing two taxonomy terms with ids 222 and 333, the node__field_tags table might look like:

bundle	deleted	entity_id	revision_id	langcode	delta	field_tags_target_id
article	0	111	777	en	0	222
article	0	111	777	en	1	333