How to query for entities without a value for a specific field?

Question

Using EntityFieldQuery it is pretty easy to query for entities with a given value for a specific field. But I fail to find a way to query for entities without a value for a given field. Let's say I have an optional Taxonomy Reference field on all my content type. I can query the nodes associated with a given taxonomy term using:

$query = new EntityFieldQuery();
$query->entityCondition('entity_type', 'file')
  ->fieldCondition('field_tags', 'tid', $tid)
  ->pager($limit);
$results = $query->execute();

But how would I query for all nodes not associated with any terms (ie. which have no value for the field_tags field). I tried setting $tid to NULL and $query->fieldCondition('field_tags', 'tid', $all_tids, 'NOT IN') without success.

Answer 1

The SQL implementation of EntityFieldQuery doesn't support this, because it builds the query using INNER JOINs instead of LEFT JOINs. I consider this a bug, please open an issue in the core queue.

Answer 2

Redefine class EntityFieldQuery:

class GrabberEntityFieldQuery extends \EntityFieldQuery
{
    protected $absent = array();

    function finishQuery($select_query, $id_key = 'entity_id')
    {
        if ($this->age == FIELD_LOAD_CURRENT) {
            $tablename_function = '_field_sql_storage_tablename';
            $id_key = 'entity_id';
        }
        else {
            $tablename_function = '_field_sql_storage_revision_tablename';
            $id_key = 'revision_id';
        }

        $table_aliases = array();

        reset($this->fields);
        $key = key($this->fields);
        $field = current($this->fields);
        $tablename = $tablename_function($field);
        $field_base_table = $tablename . $key;

        $key = count($this->fields) + 1;

        foreach($this->absent as $field) {
            $tablename = $tablename_function($field);
            // Every field needs a new table.
            $table_alias = $tablename . $key;
            $table_aliases[$key] = $table_alias;

            $select_query->leftJoin(
                $tablename,
                $table_alias,
                "$table_alias.entity_type = $field_base_table.entity_type AND $table_alias.$id_key = $field_base_table.$id_key"
            );

            $select_query->where("$table_alias.$id_key IS NULL");

            $key++;
        }

        return parent::finishQuery($select_query, $id_key);
    }

    public function absentFieldCondition($field) {
        $field_definition = field_info_field($field);
        $this->absent[] = $field_definition;
    }
}

Usage:

$query = new GrabberEntityFieldQuery();
$query
    ->entityCondition('entity_type', 'node')
    ->deleted(FALSE);

// absent field
$query->absentFieldCondition('non_existent_field_name');

Nodes without the field 'non_existent_field_name' will be selected.

That's all.