30

Is it possible to select all of the entities who's xyz field is empty?

I tried soemthing like this:

->fieldCondition('field_name', 'value', NULL, 'IS NOT NULL');

However, this doesn't seem to work.

Any ideas?

17

If you look on the fieldCondition documentation page you will see the following warning:

Note that entities with empty field values will be excluded from the EntityFieldQuery results when using this method.

Checking whether a field exists or not has been added to entityFieldQuery in Drupal 8, but unfortunately won't be backported to Drupal 7.

There are various methods to achieve this:

  1. Using a tag and hook_query_TAG_alter as mentioned by @Clive, see comment 4 on the Drupal issue for an example ;
  2. First query all non NULL entries, then query all entries excluding the previous ones, as described in @seddonym's answer and in comment 5 on the Drupal issue ;
  3. You can write your query using SelectQuery rathen than EntityfieldQuery as such:

_

$q = db_select('node', 'n');
$q->fields('n', array('type'))
  ->condition('n.type', 'my_node_type', '=')
  ->addJoin('LEFT', 'field_data_field_my_field', 'f', 'f.entity_id = n.nid');
$q->isNull('f.value');
$r = $q->execute();
14

You can use != NULL, but you can't use = NULL for some reason.

This is my workaround.

  //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(); 
  }
10

According to the documentation you can use null and isnull; it just has a specific way to write it.

$query = new EntityFieldQuery();
$query->entityCondition('entity_type', 'node')
  ->entityCondition('bundle', 'article')
  ->propertyCondition('status', 1)
  ->fieldCondition('field_news_types', 'value', 'spotlight', '=')
  ->fieldCondition('field_photo', 'fid', 'NULL', '!=')
  ->fieldCondition('field_faculty_tag', 'tid', $value)
  ->fieldCondition('field_news_publishdate', 'value', $year. '%', 'like')
  ->range(0, 10)
  ->addMetaData('account', user_load(1)); // run the query as user 1

$result = $query->execute();

if (isset($result['node'])) {
  $news_items_nids = array_keys($result['node']);
  $news_items = entity_load('node', $news_items_nids);
}
9

The short answer is that directly, no you can't (see EntityFieldQuery does not support isNull or isNotNull). If I remember rightly this is a side-effect of the fact that EntityFieldQuery uses only INNER JOINs to join tables.

There is a workaround, though, which involves using hook_query_TAG_alter() and adding a tag to your EntityFieldQuery, there's an example in the last comment on the page I linked to above.

4

In Drupal 7 please check the following workaround proposed here:

Register the tag to alter the query instance:

<?php
/**
 * Implements hook_query_TAG_alter()
 */
function MYMODULE_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:

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

Other example:

  $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 MYMODULE_query_TAG_alter(QueryAlterableInterface $query) {
  $query->leftJoin('field_data_field_other', 'o', 'node.nid = o.entity_id');
  $query->isNull('o.field_other_value');
}

Another example:

<?php
  //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();  
  }
?>

More complete example below which loads bunch of nodes on cron task which empty taxonomy term references and apply some changes:

/**
 * Implements hook_cron().
 */
function MYMODULE_cron() {
  $query = new EntityFieldQuery();
  $query
    ->entityCondition('entity_type', 'node')
    ->entityCondition('bundle', 'property')
    ->propertyOrderBy('changed', 'DESC')
    ->addTag('type_is_null')
    ->range(0,50); // Maximum of 50.
  $result = $query->execute();

  if (!empty($result['node'])) {
    $nids = array_keys($result['node']);
    $nodes = node_load_multiple($nids);

    foreach ($nodes as $node) {
      // do_some_stuff($node);
    }
  }
}

/**
 * Implements hook_query_TAG_alter()
 */
function MYMODULE_query_type_is_null_alter(QueryAlterableInterface $query) {
  $query->leftJoin('field_data_field_foo', 'f', 'node.nid = f.entity_id AND f.entity_type = :entity_type');
  $query->isNull('f.field_foo_tid'); // Check name by SQL: DESC field_data_field_foo

  $query->leftJoin('field_data_field_bar', 'b', 'node.nid = b.entity_id AND b.entity_type = :entity_type');
  $query->isNull('b.field_bar_tid'); // Check name by SQL: DESC field_data_field_bar
}
2

You need to wrap Null in quotes.

->fieldCondition('field_name', 'value', 'NULL', '!=');
1

Please correct me if I'm wrong. It seems that it simply needs to be

$query->fieldCondition('field_name');

to exclude all nodes with an empty field_name field o_O

Tested in Drupal version >= 7.43.

  • That actually works. They more upvoted answers were erroring out for me (couldn't get it to display an error, but was breaking everything). – Joren Jan 25 at 18:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.