Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
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
Comment #1
pbuyle CreditAttribution: pbuyle commentedsubscribe
Comment #2
Bevan CreditAttribution: Bevan commentedsubscribe
Comment #3
carn1x CreditAttribution: carn1x commentedsubscribe
Comment #4
grota CreditAttribution: grota commentedAs a workaround for this issues, i used hook_query_TAG_alter like this:
Then I implemented hook_query_TAG_alter leveraging the fact that 'my_custom_tag' is only set by me:
Comment #5
seddonym CreditAttribution: seddonym commentedIf you're desperate, could also do this:
Comment #6
chx CreditAttribution: chx commentedThe new entity query has exists and notexists methods.
Comment #8
MurzIs this issue will be backported to 7.x?
Comment #9
Dave Cohen CreditAttribution: Dave Cohen commentedAlthough 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.
Comment #10
Mark TrappDave: 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).
Comment #11
joachim CreditAttribution: joachim commentedMaybe 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?
Comment #12
sebas5384 CreditAttribution: sebas5384 commentedHey! following the idea from the #4 by @grota I would like to show just another example:
Register the tag to alter the query instance:
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:
That should do the trick! :)
Cheers!
Comment #13
heddnPer #6, this is in D8. Can we get it added to D7?
Comment #14
chx CreditAttribution: chx commentedThe problem with #12 is it's SQL bound. A proper NULL support would mean more...
Comment #15
kenorb CreditAttribution: kenorb commentedSee also:
http://drupal.stackexchange.com/questions/26128/excluding-emptynull-fiel...
http://drupal.stackexchange.com/questions/65134/how-to-check-non-existen...
Comment #15.0
kenorb CreditAttribution: kenorb commentedUpdated issue summary.
Comment #16
Kazanir CreditAttribution: Kazanir commentedA 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?
Comment #17
robhoward79 CreditAttribution: robhoward79 commentedHi 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:
Any ideas?
Thanks!
Comment #18
roynilanjan CreditAttribution: roynilanjan commented@robhoward79 : Seems that you have a wrong
hook
name, it should beComment #19
Neyaz Alam CreditAttribution: Neyaz Alam commentedI try get empty value so this method is working fine for me
$query->condition('field_name', 'value', 'IS NULL');
Comment #20
artreaktor CreditAttribution: artreaktor commented+1 for #10:
use
Comment #21
mh.marouan CreditAttribution: mh.marouan commentedI try #19 $query->condition('field_name', 'value', 'IS NULL'); worked well for me
Comment #22
amit0212 CreditAttribution: amit0212 as a volunteer and at Iksula commentedFor 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);
}
}
Comment #23
DamienMcKenna@amit0212: Are you actively working on improving the patch? If not, please change the "assigned" field to "unassigned". Thanks.
Comment #24
amit0212 CreditAttribution: amit0212 as a volunteer and at Iksula commentedComment #25
nevergone CreditAttribution: nevergone commentedAnd now? This is enough important feature. :(
Comment #26
useernamee CreditAttribution: useernamee as a volunteer commentedHey,
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
Comment #27
RAWDESK CreditAttribution: RAWDESK commented@#12,
This also works for user entitities, not only nodes.
hook_query_TAG_alter()