EntityFieldQuery doesn't support query for entities without a value for a specific field

Moving to normal, as this is not a major issue and also pushing to 8.x as we fix in head first.

subscribing

#1236748: EntityFieldQuery doesn't support query for entities without a value for a specific field duplicates this issue.

Querying for NULL value for field is not supported by field_sql_storage_field_storage_query because it uses INNER JOINs instead of LEFT JOINs. I don't known if any other field storage backend support NULL in field condition or not.

These INNER JOINs are also the cause of #1240566: In field_sql_storage_field_storage_query, any condition on a field's column excludes entities with a NULL value for this column.

subscribing

subscribing

In the meantime, is there any other way to do this?

For a workaround see #1157006.

#1157006 is very interesting, but only can be used on tags, while this feature is very important in other entity queries.

Hope see to get passed soon and backport to D7.

I worked around this by extending the EntityFieldQuery class and implementing some custom logic but it would be nice if a solution to this would make it into D8 and hopefully be ported back to D7.

You can just override the finishQuery method that receives the SelectQuery as the first parameter and add custom logic to it.

class CustomEntityFieldQuery extends EntityFieldQuery {

  public function finishQuery($select_query, $id_key = 'entity_id') {

    // Extend the query as you see fit
    $select_query->leftJoin('field_data_field_other', 'o', 'node.nid = o.entity_id');
    $select_query->.............
    ..................

    // When you are done with customizations just call the parent method.
    // This will execute the query with your alterations.
    parent::finishQuery($select_query, $id_key);
  }

}

Cross posting from #1611438: fieldOrderBy filters out results with empty field values

field_sql_storage_field_storage_query() also excludes items when using fieldOrderBy() and the field is NULL.

#9 looks promising and will give that a go.

#1611438: fieldOrderBy filters out results with empty field values

Knowing this issue but not #1611438: fieldOrderBy filters out results with empty field values, it's hard to see how this is a duplicate. Would someone provide a brief explanation.

Since #1611438: fieldOrderBy filters out results with empty field values has been closed as "work as designed" spawning a new feature request at #1662942: Make EntityFieldQuery::fieldOrderBy include NULL field values, should we open a new feature request if we want to be able to query for entities without a value for a specific field?

For folks that are looking for an easier way to do this in D7 try something like this:

/**
 * Use instead of EntityFieldQuery when you need to fetch results where a field
 * is NULL.
 * 
 * Usage:
 * $q = new EntityFieldQueryForNullFields;
 * $q->entityCondition('entity_type', 'node');
 *   ->fieldCondition('table', 'field', NULL, 'IS NULL')
 *   ->execute();
 */
class EntityFieldQueryForNullFields extends EntityFieldQuery {

  public function finishQuery(SelectQuery $select_query, $id_key = 'entity_id') {
    // Iterate through all fields.  If the query is trying to fetch results
    // where a field is null, then alter the query to use a LEFT OUTER join.
    // Otherwise the query will always return 0 results.
    foreach ($this->fieldConditions as $key => $fieldCondition) {
      if ($fieldCondition['operator'] == 'IS NULL' && isset($this->fields[$key]['storage']['details']['sql'][FIELD_LOAD_CURRENT])) {
        $tables =& $select_query->getTables();
        foreach ($tables as &$table) {
          $sql_table = reset(array_keys($this->fields[$key]['storage']['details']['sql'][FIELD_LOAD_CURRENT]));
          if ($table['table'] == $sql_table) {
            $table['join type'] = 'LEFT OUTER';
          }
        }
      }
    }
    return parent::finishQuery($select_query, $id_key);
  }

}

Thanks for the EntityFieldQueryForNullFields code @dalin, it saved me a lot of work.

#13 is a great solution. reset(array_keys(...)) does throw a strict warning though.

This is how I'm using it. I need a value from a field if another field is empty.
http://drupal.stackexchange.com/questions/18093/get-only-some-fields-wit...

<?php
/**
 * Use instead of ApachesolrAttachmentsEntityFieldQuery when you need to fetch
 * results where a field is NULL.
 *
 * @see drupal.org/node/1226622#comment-6809826
 *
 * Usage:
 * $q = new ApachesolrAttachmentsEntityFieldQueryForNullFields;
 * $q->entityCondition('entity_type', 'node');
 *   ->fieldCondition('table', 'field', NULL, 'IS NULL')
 *   ->addExtraField('table', 'field', 'value')
 *   ->execute();
 */
class ApachesolrAttachmentsEntityFieldQueryForNullFields extends ApachesolrAttachmentsEntityFieldQuery {
  public function finishQuery($select_query, $id_key = 'entity_id') {
    // Iterate through all fields.  If the query is trying to fetch results
    // where a field is null, then alter the query to use a LEFT OUTER join.
    // Otherwise the query will always return 0 results.
    foreach ($this->fieldConditions as $key => $fieldCondition) {
      if ($fieldCondition['operator'] == 'IS NULL' && isset($this->fields[$key]['storage']['details']['sql'][FIELD_LOAD_CURRENT])) {
        $tables =& $select_query->getTables();
        foreach ($tables as &$table) {
          $keys = array_keys($this->fields[$key]['storage']['details']['sql'][FIELD_LOAD_CURRENT]);
          $sql_table = reset($keys);
          if ($table['table'] == $sql_table) {
            $table['join type'] = 'LEFT OUTER';
          }
        }
      }
    }
    return parent::finishQuery($select_query, $id_key);
  }
}
?>

Usage

  // Find all active users with an account ID field but no acm.
  $query = new ApachesolrAttachmentsEntityFieldQueryForNullFields();
  $results = $query->entityCondition('entity_type', 'user')
    ->propertyCondition('status', 1)
    ->fieldCondition('field_account_id', 'value', NULL, 'IS NOT NULL')
    ->addExtraField('field_account_id', 'value', 'value')
    ->fieldCondition('field_account_acm', 'uid', NULL, 'IS NULL')
    ->execute();

  // This will return field_account_id_value inside of extraFields. Quite handy.

Using #13 or #15 gives me:

PDOException: SQLSTATE[42000]:
Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
'LEFT OUTER JOIN field_data_field_latitude field_data_field_latitude0 INNER JOIN '
at line 1: SELECT field_data_field_latitude0.entity_type AS entity_type, field_data_field_latitude0.entity_id AS entity_id, field_data_field_latitude0.revision_id AS revision_id, field_data_field_latitude0.bundle AS bundle
FROM LEFT OUTER JOIN {field_data_field_latitude} field_data_field_latitude0
INNER JOIN {node} node ON node.nid = field_data_field_latitude0.entity_id
WHERE (field_data_field_latitude0.field_latitude_value IS NULL )
AND (field_data_field_latitude0.deleted = :db_condition_placeholder_0)
AND (node.type = :db_condition_placeholder_1) AND (field_data_field_latitude0.entity_type = :db_condition_placeholder_2) ;
Array ( [:db_condition_placeholder_0] => 0 [:db_condition_placeholder_1] => delivery [:db_condition_placeholder_2] => node )
in field_sql_storage_field_storage_query()
(line 582 of /modules/field/modules/field_sql_storage/field_sql_storage.module).

Improved it a little bit :

// http://drupal.org/node/1226622#comment-6809826 - adds support for IS NULL
    // Iterate through all fields.  If the query is trying to fetch results
    // where a field is null, then alter the query to use a LEFT OUTER join.
    // Otherwise the query will always return 0 results.
    $tables =& $select_query->getTables();
    foreach ($this->fieldConditions as $key => $fieldCondition) {
      if ($fieldCondition['operator'] == 'IS NULL' && isset($this->fields[$key]['storage']['details']['sql'][FIELD_LOAD_CURRENT])) {
        $keys = array_keys($this->fields[$key]['storage']['details']['sql'][FIELD_LOAD_CURRENT]);
        $sql_table = reset($keys);
        foreach ($tables as $table_id => $table) {
          if ($table['table'] == $sql_table) {
            $tables[$table_id]['join type'] = 'LEFT OUTER';
          }
        }
      }
    }

Some code above was executed too many times. I'm thinking in integrating this in efq_extra_fields.

This is still not ideal.
For me, EntityFieldQuery will put the tables in a weird order. Instead of putting the entity table first it puts some field first and then the entity.
As a result, the field table has neither a join type nor a join condition, whereas the entity table does have.

I don't want to make the entity table the "LEFT JOIN" one..

After a bunch of testing I created a sandbox to hold the EFQ stuff
https://drupal.org/sandbox/mikeytown2/2209909
http://drupalcode.org/sandbox/mikeytown2/2209909.git/blob/HEAD:/entityfi...

Instead of addExtraField I have getFieldValue & getPropertyValue. getFieldValue by default does a LEFT JOIN but it can be changed to INNER if needed.

  $nid = 8;

  $query = new EntityFieldQueryExtra();
  $query
    ->entityCondition('entity_type', 'node')
    ->entityCondition('bundle', 'video')
    ->propertyCondition('nid', $nid)
    ->getFieldValue('field_video_script_approved', 'value', 'INNER')
    ->getFieldValue('field_video_script', 'value')
    ->getFieldValue('field_video_file', 'fid')
    ->getFieldValue('field_video_raw_file', 'fid')
    ->getFieldValue('field_video_voice_talent', 'tid'')
    ->getPropertyValue('status');
  $output = $query->execute();

Re #16, I had the same issue.

The issue is that the query ends up being written as "SELECT ... FROM LEFT OUTER JOIN ...". The extended EFQ class needs another table to query before joining in the one you want to get null data from.

Add another field to your query before the null one - the documentation from #13 could be:

/**
* Use instead of EntityFieldQuery when you need to fetch results where a field
* is NULL.
*
* Usage:
* $q = new EntityFieldQueryForNullFields;
* $q->entityCondition('entity_type', 'node');
*   ->fieldCondition('table', 'field', value, '=') // some other field that has some value you are looking for
*   ->fieldCondition('table', 'field', NULL, 'IS NULL')
*   ->execute();
*/

So I'm intentionally slowing down this query by adding in an unnecessary JOIN as a workaround, which gives me a sense of why this fix can't simply be added to EntityFieldQuery.

Also, if anyone is looking for it, the open issue is at https://drupal.org/node/1226622; I'm pasting the udpate to the documentation here because this is where the snippet is.

When a field tables are empty that field cannot be used on field Condition because result won't be filtered by it. This is an unexpected behavior and can cause security issues.

So, what should be the arguments of fieldCondition when searching for nodes that have certain field NULL.

->fieldCondition('field_snode_reference', 'value', NULL, 'IS NULL')

does not work for me.

Regards and thanks
kireli