empty / not empty filter for Views

I'd assume that if you can add the filter at all, you should be able to leave the value blank and set the operator to 'is equal to'. Or is not equal to to get the reverse.

Merlin, actually it does not work :

1/ 'is equal to' / 'is not equal to' are not defined for all field types.
Field modules are free to define the operators that are relevant to their data
text fields for instance have 'equal to', 'starts / ends with', 'contains', etc, but don't have 'is not equal to'...
We might fix that for "cck-bundled" field modules, but we can't control what other contrib field modules do.

Maybe the solution to 1 would be to provide an additional filter for each field with two operators, 'empty' / 'not empty', but that would _double_ the number of view filters generated by CCK, and the filter list is already quite huge...

2/ Even if a field type has 'is equal to', leaving the value empty does not do the expected "select only the nodes with this field empty" ('is not equal to' seems to work, at least for number fields...).
This might be complicated by the fact that the notion of 'empty' is not the same for all fields : it's "" (empty string) for text fields, it's NULL for numbers, it's currently 0 for nodereferences...

I'm currently working on several things with Views integration in CCK (issues with multiple fields, plus another sort of related inconsistent behaviour of arguments wrt to empty fields...) so I'll try to take a look at this.
I'm bringing this to CCK for now - maybe some requirements / feature requests for Views will come out...

OK, here is my proposition.
I want to avoid defining a standalone "empty / not empty" that would more or less double the number of filters CCK fields generate.

So :
- if the field module defines no filters, CCK adds one with "empty / not empty" operators
- if the field module defines one or several filters, CCK adds "empty / not empty" operators to the one being labeled 'default' . That way, a field module has the opportunity to avoid them (and then preferably manage empty / not empty filtering itself)

See attached patch for 4.7 - you'll have to empty drupal cache after applying.

Related note : I just updated text module so that it always stores at least an empty string for empty text fields (it was storing nothing for empty multiple fields). Other cck-bundled fields should be OK.

Note to Karen : date module is probably the field module with the most extensive use of views filters, so I could probably use your opinion on this...

We just came across this problem in our Drupal implementation. Has there been any progress on this concept of filtering nodes which do not have any value set?

see http://drupal.org/node/101050
(i'm assuming you're talking about CCK fields - please reopen if not)

Er - forget my previous post - I stumbled across the original post and mistook it for a new request :-D
I marked another duplicate request for this a few days ago, though, but I can't seem to find it now.

@greenrenault : the patch I proposed interferes with filters defined in field modules, so I'm expexting some reviews (esp. from other cck and field modules maintainers) before committing.

yched, I'm trying to understand this patch and the need for it. It seems like an empty/not empty handler is something that should either already have a solution or if not be something that should be added to Views to be available to other fields. Maybe a couple empty/not empty handlers, one for text values and one for numeric data. Is there some way to generalize this out into a new Views handler (as a patch to Views) that we can then use for CCK fields rather than adding all this specialized code to CCK for something that might be useful elsewhere?

Sorry to make a suggestion that is more work than you've already done :-(

If I recall correctly, the problem is that the notion of 'empty' depends on the db column definition :
a 'userref' column set to 0 is empty, whereas a 'number field' column set to 0 is not empty,
The generated 'WHERE' clause also needs to know the column type (integer, float, type...) in order to generate the correct placeholder (%d, %f, '%s'...)

We have these informations in the $db_info for CCK fields, but i'm really not sure how we can generalize that to all the 'views fields' declared by any module out there.

Plus there is the issue that in order to detect an 'empty' value, there has to be a record in the db, stating that 'there is a value, and this value is empty", as opposed to : no record at all in the db, which I think is the most common behaviour for (non-cck) 'fields'.
I think I made sure that all core-cck field modules do leave a record for empty values, but some contribs may not, depending on whether the field is stored in 'per field' or 'per content type' tables - See my comment in http://drupal.org/node/118971#comment-199198 about that.
This stuff is sort of complex, I don't know if I'm being clear...

All in all : I don't see how we can generalize this feature to all views fields for now...

OK, sounds like you're right. I just always like to see if we can make things as general as possible. Next I need to test this. No time now, but I'll try to get back to it.

Here's a re-rolled patch against latest 5 branch

subscribing

I just applied the patch from yched (comment #11 in this thread) for the drupal-5 branch, but I can't see any effect.
At least not for the nodereference field I was hoping to use the new filters on.

Well, I'm currently away from my coding / testing setup, so I can't provide much help.
You _should_ see two 'Is empty / Is not empty' items in the 'operator' column when you add a filter on your noderef field (if several filters are available for a given field, they are added to the first one, but noderef fields declare only one filter).

It seems the cache made me look stupid again, some time gone since my last post here and now it works.

Thanks a lot and I hope to see this in CCK proper soon.

OK - the patch is marked "needs review", and there are a lot of cck fields and contrib fields combinations to test, so any amount of time you can spend fiddling with this with various field types is more than welcome.

PS : last patch is for 5.x branch, so i'm moving this to the 5 version, where the dev actually happens. If / when this goes in, a backport will be straightforward.

There seem to be subtle interactions:

I had a view with 2 "empty" filters for noderefs and everything worked fine. Then I added a filter on "Node: Type" and the sql statement broke:

SELECT DISTINCT(node.nid) FROM node node 
LEFT JOIN [noderef filter 1] LEFT JOIN [noderef filter 2]  
WHERE ([noderef1 field] IS NULL) AND 
    ([noderef2 field] IS NULL) AND 
    (. IN ('type1','type2','type3')) 
LIMIT 0, 10

and "." as selector for node-type is not good ...

When I remove the 2 "empty" filters, the node-type filter works again. No idea what's going on here, but if I can do something to help fix this, just tell me ;-)

Note: using the patch for drupal 5

Thanks for the feedback.
It's strange, I can't reproduce this. Plus I can't see why our handling of this filter would mess with the node: type filter...
Maybe you should check you have the latest releases of everything (CCK 1.4, Views 1.5), check for update.php updates, empty your cache, re-save your view... ?

Another odd thing is that for nodereferences, the 'empty / not empty' filter should generate
WHERE [field value] = (or !=) 0
and not
WHERE [field value] IS NULL

(I'm moving the issue to 5 branch, this is where development happens... the patch should apply smoothly on 4.7, though)

http://drupal.org/node/133903 has been marked a duplicate

I'm still stuck on the idea of getting as much of this as possible factored out of CCK and into Views, especially since this would be useful to other modules. The only real problem is that Views does not know what 'empty' means to a field, so I am thinking that any module that wants to provide empty/not empty filters could just add a 'empty' value to their filter which would indicate the type of testing to perform. The options for that item would be '' (blank), NULL, or 0.

All the core CCK modules are using one of three basic views operators, eqnequ(), gtlt(), and like(), so we could add an 'empty' version of each of those operator to views, then use those new operators in our core CCK modules. If we define the info about what 'empty' means in the filter, we don't need the handler to know what type of field it is and we can generalize content_views_filter_handler_empty() to become views_filter_handler_empty(), and content_views_filter_handler_default() to become views_filter_handler_default(). That would remove a fair bit of this code from CCK and allow any module to use them.

So a filter using this new operator and handler would look like:

'filters' => array(
  'currentuid' => array(
        'field' => 'uid',
        'name' => t('Node: Author is Current User'),
        'list' => 'views_handler_filter_usercurrent',
        'list-type' => 'select',
        'operator' => 'views_handler_operator_eqneqempty',
        'handler' => 'views_handler_operator_eqneqempty',
        'empty' => '0',
      ),

Am I making any sense? I should create a patch to illustrate it -- that is a patch to Views to add this new functionality and another patch for CCK to take advantage of it. The CCK patch would alter each of the core modules to use the 'empty' version of those Views filters and add the 'empty' attribute to the filter based on the field type. I think the filters could then go straight to the Views handlers rather than a content module handler, but I haven't actually tried this yet...

+1 to KarenS' last comment...
This make sense to me, especially with regards to patching views so that it will have the capacity to filter by Null or Not Null... Other (contributed) CCK fields could then also use this feature by mocking the filtering implementations of the core CCK fields...

Some thoughts, in no particular order:

1. I really think all "empty" CCK fields should standardize on NULL as the empty indicator. For single type columns setting the field value to NULL and for multi-type values deleting the row, causing LEFT JOINs to create NULL fields. More specifically, empty strings for text fields are difficult to work with at a DB level (they can look the same but behave differently). For statistical purposes there is a difference between the value 0 and a non-recorded data point; I think this is a compelling reason to change. For user references/node references 0 might be a valid identifier (e.g. anonymous user, perhaps some sort of super type (?) for nodes), where as NULL clearly indicates no data.

I recently wrote a null handler for views (http://drupal.org/node/142504). This would help with the query building.

2. On not filling the views filters list up with unnecessary entries: what about a single filter "Content: Filed is empty". This could have a "Is empty/Is not empty" operator and the option could be the field name itself (probably displayed as a select list: 'type' => 'Pretty Name'). Using the handler I mention above, this would be trivial to implement.

To sum up: NULL is our friend. :-)

Just tested the patch.

Provides a filter for imagefield and it works fine.

Seems to break the "Date (field_date)" filter though (the "Operator" field is empty).

*bump* looking for a solution to this problem and wanted to check to see if anyone's made any progress?

me too.. subscribing :)

For D6, all modules must define their columns to use NULL as the empty value. That much is already in the D6 code in HEAD. We'll need to create Views handlers to use those values, once Views is ready for D6.

I have no time to write and debug something that will work in D5, but if someone wants it and does the work, I'll look at it.

Subscribing, currently attempting to solve this in views with no success.

No success so far! Doesn't anybody else need this? Anybody know anything that might get me started in the right direction? I'll do the work, but I keep hitting brick walls.

Thanks a bunch!

Subscribing. I also need this for a project I'm working on.

I've added this patch to drupal 5.3 so as to filter null values from a table view of email cck type fields. After patching I get the IS Empty/IS Not Empty filter in the view edit, but when I do the view, it appears to filter everything. Without the Is Not Empty filter the table displays, albeit with the null rows. Looking under the hood in the patched content_views.inc, at the end of content_views_field_query_handler it calls
$query->add_where($clause , $arg);

echoing out $clause it says "node_data_field_program_contact_email.field_program_contact_email_email IS NULL"

field_program_contact_email_email is indeed the name of the column I want, but node_data_field_program_contact_email is not the table. This table doesn't exist so i presume it can't find anything which returns true for this sql and every row is filtered out.

at the end of function content_views_tables i find

// We don't use $db_info['table'] for the key, since that may change during
// the lifetime of the field and we don't want to require users to redefine
// their views.
return array('node_data_'. $field['field_name'] => $table);

my correct table name ( $db_info['table'] ) is in $table['name'], whereas 'node_data_'. $field['field_name'] is what ends up in $clause. Shouldn't it be $table['name']?

I've used your "null" filter with success. Actually, by combining it with the "Is None Of -> " filter already available from nodereference I was able to list all of the content that do refer to some nodes.

However, I agree with the need for a "Content: Field is empty" filter and I strongly suggest that it be included in CCK. And to go deeper into that idea, we should have a handler "Content: Field Count" that compares (with "is equal to", "is less than", etc) the number of values for a field to a number. That would be helpful for multiple fields, and would also encompass the issue at hand.

I am currently working on a patch for mediafield that goes along these tracks.

>I've used your "null" filter with success.

Could you help us out with instruction on how you did this?

Still pounding my head against the wall ;)

Subscribing. I've marked my original post as duplicate, as I believe it deals with the same issue.
http://drupal.org/node/209583

subscribing

subscribing

subsrubing

subscribing... and hoping there is some progress...

+1 for mfredrickson's [#22] "Content: Field is empty" - that sounds like a general, usable approach.
Can't help dev., but will test with CCK date fields.

I made a quick patch to views.module to allow it to test empty values for cck fields. I restricted it to cck since it sounds like cck makes sure to put an empty value in the db. (ref: comment #9) I haven't done a lot of testing so ymmv, etc., but it works for me, and I hope it can help some other people out.

This seemed to work when the operator is set to Contain a blank value, but I got either no output or errors when it was set to something else.

subscribing in case...

Has this been commited in newer version? If not, we should update the module's version for this post.

I just applied this patch (#38) but it doesn't seem to have had any affect. Has anyone made progress on this?

subscribing - using CCK 5.x-1.9 meanwhile and no luck

Views2 changes for D6 allowed this to happen in CCK D6.
This will not likely not happen for the D5 branch, which is now feature frozen.

Demoting this, btw.

Too bad, I have no plans to upgrade to D6 in the near future. Maybe someone can still figure out a hack to implement.

Idea for a (rather ugly) workaround, but relatively easy to do and the fastest way to a solution for Drupal 5.x:
Add a hidden computed field that calculates the string length of your text field. Then you can check if the length is > 0 in the view filter formula....

Hi, thanks for the suggestion. I think that's possibly what the above patch tries to do but it doesn't seem to work for me. However I'll take a look at the code again to see if I can figure out where to try this little hack.

Still no solution yet. Subscribing.

CCK and Views in D6 offer the feature, this thread is about D5 (whose siruation won't change much I'm afraid)
Changing the version number here will confuse everyone

not a real solution but as it seems this isn't going to get fixed in 5.. just an FYI for those still needing to filter on null

this query sub hook call will let you use the is equal/not equal operator with NULL

function mymodule_views_query_substitutions(&$view) {
  switch ($view->name) {
    case 'my_view':       // provide support for search on NULL
      $replacements[" = 'null'"] = "is null";
      $replacements["!= 'null'"] = "is not null"; 
      break;
  }
  return $replacements;
}

Cool! Except I'm a bit of a dope and don't know exactly how to implement this hack. Does it go in template.php?

Thanks, Brad.

needs to be put in a module... from example code it would have to be in one called mymodule.module

I've added this to my module. Please could you please explain what to put in the view?
I have "is equal to" and "does not contain" but I'm not sure what to put in the Value for these.
I'm trying to choose "is not null"

i am using this with an exposed filter where i select operator "is equal to" and enter the word "null"

http://screencast.com/t/T3JzVWZo

Thanks, does anyone have an example of using it the oher way round - where a text field is not empty (or null)

Too old now, way past the time when we will add features to D5 version.