Use case: find all nodes that haven't been tagged with a taxonomy term or all users that haven't filled out a new field on their user account, etc.

Would it be terrible to add an isNull isNotNull method that pass through to the SelectQuery by default? Would that interfere with other storage backends that don't support is null?

Note: the issue is full of SQL-bound broken hacks. Ignore them.

Comments

pbuyle’s picture

subscribe

Bevan’s picture

subscribe

carn1x’s picture

subscribe

grota’s picture

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');
}
seddonym’s picture

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();  
  }
chx’s picture

Status: Active » Fixed

The new entity query has exists and notexists methods.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

Murz’s picture

Is this issue will be backported to 7.x?

Dave Cohen’s picture

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.

Mark Trapp’s picture

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).

joachim’s picture

Version: 8.x-dev » 7.x-dev
Status: Closed (fixed) » Active

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?

sebas5384’s picture

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!

heddn’s picture

Issue tags: +needs backport to D7

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

chx’s picture

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

kenorb’s picture

Issue summary: View changes

Updated issue summary.

Kazanir’s picture

Issue summary: View changes

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?

robhoward79’s picture

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!

roynilanjan’s picture

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

 function xxx_xxx_query_TAG_alter(QueryAlterableInterface $query) {
   // Logic here. 
 }
Neyaz Alam’s picture

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

artreaktor’s picture

+1 for #10:
use

$query->exists('field_name.value');
$query->notExists('field_name.value');
mh.marouan’s picture

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

amit0212’s picture

Assigned: Unassigned » amit0212
Status: Active » Needs review

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

DamienMcKenna’s picture

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

amit0212’s picture

Assigned: amit0212 » Unassigned
Status: Needs review » Active
nevergone’s picture

And now? This is enough important feature. :(