How to use entityQuery to select if a multiple value entityReference field does not contain the id

Question

I have an entity with a user entityReference field, which may contain multiple values. I wish to select using EntityQuery the nodes that do NOT contain the uid in the query.

$query
  ->condition('field_shared_with_user.target_id', $uid, '<>');

This only works to exclude $uid if there is only ONE value in the field. If $uid is present but some other uid is also present, it incorrectly includes this node in the result.

What is the correct syntax for writing a condition for a multiple value field?

The most I can find about writing these queries is here:

https://api.drupal.org/api/drupal/core!lib!Drupal!Core!Entity!Query!QueryInterface.php/function/QueryInterface%3A%3Acondition/8

Answer 1

From what I understand the Entity Query will just perform a join operation on the tables, resulting in data like the following:

entity.id | field_user.target
        1 |                 5
        1 |                 6
        2 |                 5

A join condition can limit the rows returned, but restricting by field_user.target <> 6 can only remove the one row (1:6), leaving the 1:5 row remaining. Without a subquery, I don't think there is a way to exclude both rows with entity.id = 1.

Two alternatives:

• Perform an initial query to find all of the entities that reference the user you would like to filter, and then filter your query by those entity ids (essentially doing your own subquery).
• Get all results, and filter them in PHP afterwards.

Answer 2

gapple put me on the right track (at least a solution for my specific problem), and its a combination of his two ideas.

I needed to query for all the nodes that reference my user, then query for all the entities that match my main query. that gives me two arrays of node ids, and if I array_diff the first from the 2nd, I end up with the array of node ids i want.

so, a separate entityQuery is needed, but its not too computationally expensive in my case, I think.

$my_contacts = \Drupal::entityQuery('node')
  ->condition('type', 'sc_card')
  ->condition('field_shared_with_user.target_id', $user->id())
  ->execute();
$public_contacts = \Drupal::entityQuery('node')
  ->condition('type', 'sc_card')
  ->condition('field_public_directory', true)
  ->execute();
$public_not_in_my_contacts = array_diff($public_contacts, $my_contacts);

Answer 3

If you want to check for the lack of a value, you need to use notExists() instead of condition(). There's also an exists().