Trying to use EntityFieldQuery to find all nodes with a node reference field not set

Question

After some searching the closest code snippet I've found is:

->fieldCondition('field_name_ref', 'nid', 'NULL', '!=');

This will find everything where the node reference is not null. But if I change the != to = it returns nothing. I've tried all the combinations of values I can think of:

• Passing in NULL instead of 'NULL'.
• Passing in array()
• Trying 1, "<"
• "", "CONTAINS"
• "", "="
• 0, "="

Plus a few more, just trying all the combinations I can think of.

How can I pull this off?

Answer 1

As crazy as it might sound, you can't. Normally you'd use the IS NULL operator to do this but it is apparently not supported by EntityFieldQuery:

EntityFieldQuery does not support isNull or isNotNull

Also I think the EntityFieldQuery internally uses INNER JOIN, rather than LEFT JOIN so records that don't have a value in your condition table would be excluded from the query results anyway. The issue has been reported several times:

• EntityFieldQuery doesn't support query for entities without a value for a specific field
• EntityFieldQuery doesn't support query for entities without a value for a specific field (duplicate)
• In field_sql_storage_field_storage_query, any condition on a field's column excludes entities with a NULL value for this column

Answer 2

For a workaround I added a custom tag to the query and then I used hook_query_TAG_alter(), see #1157006 at drupal.org for more details.