EntityFieldQuery does not support isNull or isNotNull

subscribe

subscribe

subscribe

As a workaround for this issues, i used hook_query_TAG_alter like this:

  $result = $query
    ->entityCondition('entity_type', 'node')
    ->propertyCondition('type', 'my_content_type')
    ->fieldCondition('field_mine_one', 'value', '', '<>')
    ->fieldCondition('field_mine_two', 'value', '', '<>')
    ->addTag('my_custom_tag')
    ->deleted(FALSE)
    ->propertyOrderBy('changed', 'DESC')
    ->range(0, $my_range_value)
    ->execute();

Then I implemented hook_query_TAG_alter leveraging the fact that 'my_custom_tag' is only set by me:

/**
 * Implements hook_query_TAG_alter()
 */
function prontocare_migration_query_filtra_geofield_vuoto_alter(QueryAlterableInterface $query) {
  $query->leftJoin('field_data_field_other', 'o', 'node.nid = o.entity_id');
  $query->isNull('o.field_other_value');
}

If you're desperate, could also do this:

  //Get all the entities that DO have values
  $query = new EntityFieldQuery();
  $query->entityCondition('entity_type', 'MY_TYPE')
    ->fieldCondition('field_MY_FIELD', 'value', 'NULL', '!=');
  $result = $query->execute();

  if (is_array(@$result['registration'])) {
    //Now get all the other entities, that aren't in the list you just retrieved 
    $query = new EntityFieldQuery();
    $query->entityCondition('entity_type', 'MY_TYPE')
      ->entityCondition('entity_id', array_keys($result['MY_TYPE']), 'NOT IN');
    $result_two = $query->execute();  
  }

The new entity query has exists and notexists methods.

Is this issue will be backported to 7.x?

Although api.drupal.org indicates that notexists may have been backported to D7, http://api.drupal.org/api/drupal/includes%21database%21query.inc/functio..., I can't figure out how to use it. Is there any example documented anywhere?

I'm trying to use an EntityFieldQuery to find taxonomy terms which do not have any value for a field. I've found many threads stating such queries are not supported by Drupal, but this thread implies they are. If so, please point me to how. Thanks.

Dave: that API documentation is for the Database API, not EntityFieldQuery. EntityFieldQuery has been rewritten for Drupal 8 and now includes exists() and notExists() methods (hope I got the right API documentation links there), but that rewrite has not been backported to Drupal 7 (would be virtually impossible: relies on all the major changes to Drupal 8).

Maybe I'm not thinking about this in enough depth on a Monday morning, but wouldn't it involve adding functionality to field_sql_storage_field_storage_query() to add left joins for fields whose values are requested as NULL?

Hey! following the idea from the #4 by @grota I would like to show just another example:

Register the tag to alter the query instance:

/**
 * Implements hook_query_TAG_alter()
 */
function custom_news_query_node_is_not_tagged_alter(QueryAlterableInterface $query) {
  $query->leftJoin('field_data_field_tags', 'o', 'node.nid = o.entity_id AND o.entity_type = :entity_type');
  $query->isNull('o.field_tags_tid');
}

Obs.: This query tag alter only works for the "node" entity type. Don't confuse "field_tags" related to the "Tags" vocabulary, can be any other like "Categories".

Get all the nodes from that doesn't been tagged yet using EntityFieldQuery, look at the addTag() method:

$query = new EntityFieldQuery();
$query->entityCondition('entity_type', 'node')
  ->entityCondition('bundle', 'news')
  ->addTag('node_is_not_tagged')
  ->propertyCondition('status', 1);
$result = $query->execute();

That should do the trick! :)

Cheers!

Per #6, this is in D8. Can we get it added to D7?

The problem with #12 is it's SQL bound. A proper NULL support would mean more...

See also:
http://drupal.stackexchange.com/questions/26128/excluding-emptynull-fiel...
http://drupal.stackexchange.com/questions/65134/how-to-check-non-existen...

Updated issue summary.

A proper fix is going to involve fixes to the various storage engines that EFQ relies on. Is that realistic at this stage in D7's life cycle?

Hi there,

I've been trying to get this working, but with no joy. I'm basically trying to get articles that have no body copy in them and I still keep getting the same results back as though there's no alter function being actioned.

Here is my code:

function xxx_xxx_body_null_alter(QueryAlterableInterface $query) {
  $query->leftJoin('field_data_body', 'b', 'node.nid = b.entity_id AND b.entity_type = :entity_type');
  $query->isNull('b.body_value');
}

function xxx_xxx_init ()
{  
    
  $query = new EntityFieldQuery();
  
  $current_date = date("Y-m-d g:i:s");
  
  $query->entityCondition('entity_type', 'node')
    ->entityCondition('bundle', 'article')
    ->addTag('body_null')
    ->propertyCondition('status', 1)
    ->fieldCondition('field_date', 'value', $current_date, '<')
    ->propertyOrderBy('created', 'DESC');
  $result = $query->execute();
  
  print_r($result);exit;
     
}

Any ideas?

Thanks!

@robhoward79 : Seems that you have a wrong hook name, it should be

 function xxx_xxx_query_TAG_alter(QueryAlterableInterface $query) {
   // Logic here. 
 }

I try get empty value so this method is working fine for me
$query->condition('field_name', 'value', 'IS NULL');

+1 for #10:
use

$query->exists('field_name.value');
$query->notExists('field_name.value');

I try #19 $query->condition('field_name', 'value', 'IS NULL'); worked well for me

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);
}
}

@amit0212: Are you actively working on improving the patch? If not, please change the "assigned" field to "unassigned". Thanks.

And now? This is enough important feature. :(

Hey,
I wonder what should be the value of 'table' argument of fieldCondition query for EntityFieldQueryForNullFields. I have Node reference type of field which I would like to check if it is set and if it is not - then set some values. But I can't get fieldCondition to work. (Some kind of example would be very useful).

Thanks and regards
kirili

@#12,

This also works for user entitities, not only nodes.

$query = new EntityFieldQuery();
$query->entityCondition('entity_type', 'user');
$query->propertyCondition('status', '1');
$query->addTag('user_without_guid');
$result = $query->execute();

hook_query_TAG_alter()

function custom_user_query_user_without_guid_alter(QueryAlterableInterface $query) {  
$query->leftJoin('field_data_field_uuid', 'u', 'users.uid = u.entity_id AND u.entity_type = :entity_type');
$query->isNull('u.field_uuid_value');
}