The "Is not one of" operator for filters does not appear to be working correctly. I get the same results I would get with the "Is not empty (NOT NULL)" operator. I have observed this problem with text list fields and boolean fields. These are the only two I checked, so it may be happening with other field types.

Here's a little more detail. Suppose I have a text list field with the following possible values:
choice_1
choice_2
choice_3
choice_4

Then I create a view with a filter for that field, with the "Is not one of" operator selected for choice_3. I would expect the view to show me all nodes with values other than choice_3, as well as all nodes with no value selected for that field. Instead what I get is all nodes with a value entered for that field, even if the value is choice_3. The only nodes getting filtered out are those with no value selected for the field.

CommentFileSizeAuthor
#7 views-1529016-not-in-include-null-values.patch962 bytesygerasimov
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

onejam’s picture

onejam CreditAttribution: onejam commented
Version: 7.x-3.x-dev » 7.x-3.3
Component: Miscellaneous » exposed filters

I'm running Views 7.x-3.3 and i'm also having this same issue.

bunthorne’s picture

bunthorne CreditAttribution: bunthorne commented

I am also having the problem with 7.x-3.3.

I have a List text field with 4 check box options (let's call them A, B, C, and D); I added the field as a Filter to select a record that "is not one of" the B value. The view instead returns only returns records that have A, C or D checked; it does not include the records that are empty/null (no box checked)--which is most of the records.

I am unable to find a workaround to use the "is one of" or the empty/not empty options to get to the same results. I need the "is not one of" option to work! The only way to get there would be to add a fifth checkbox value that represents the null value selection, set it as the default, and fix all the data so that I could flip the logic to the "Is one of" type. But I really don't want to go there.

I have tried this several times in two different environments, building and rebuilding views, plenty of cache clearing, etc. Let me know if any more specifics would be helpful.

dawehner’s picture

dawehner
Primary language German
CreditAttribution: dawehner commented

If you think about how such a query currently looks like, this is not a real wonder:

SELECT ... JOIN field_table f WHERE f.value NOT IN (3)

This joins in the same line multiple times, so a NOT IN will match the other lines at least one time.
I'm not sure at this moment of the early morning how to create proper sql to make it working for you.

bunthorne’s picture

bunthorne CreditAttribution: bunthorne commented

For those of us who don't code and need a logical workaround, I was able to create the desired result by using the "Value is one of (A,C,D)" OR "Value is empty" as a filter combination block--in place of the broken "Value is not one of (B)" logic.

eusonic’s picture

eusonic CreditAttribution: eusonic commented

@bunthorne

That's a great solution as long as you always know what the options are. It won't be maintainable if the options change frequently, such as a term reference field.

bunthorne’s picture

bunthorne CreditAttribution: bunthorne commented

Is there a patch available?

ygerasimov’s picture

ygerasimov CreditAttribution: ygerasimov commented
Status: Active » Needs review
FileSize
views-1529016-not-in-include-null-values.patch962 bytes

The idea of attached patch is to add OR condition to include NULL values. Please test.

marblegravy’s picture

marblegravy CreditAttribution: marblegravy commented

Patch at #7 tested and working as expected. Thanks ygerasimov.

In my case the issue was that I have an existing site that needed a new field to specify an 'archive' state, but because none of the existing content had the archive field, null values were not being counted and so all of my views that were supposed to just not show 'archived' content (Not one of "Archived"), were showing nothing.

EDIT: As an interim measure bunthorne's solution at #4 works too. Create a Filter Group and create two filters for the target field:

all my current filters
AND
field_demo is empty (or null) OR
field_demo is not myValue

This should work for future values too (so #5 - you should be fine to use this method too)

majorbenks’s picture

majorbenks CreditAttribution: majorbenks commented

#4 works not for me, because I have multiple choices. (I can have A and B checked, but I do not want to show the node if B is checked, regardless what else is checked)

The patch in #7 is not working for me neither. Propably becauce the multiple choices.

majorbenks’s picture

majorbenks CreditAttribution: majorbenks commented
Status: Needs review » Active

Whatever I try, it is not working. I definetly need a "not in" for "I can have A and B checked, but I do not want to show the node if B is checked, regardless what else is checked".

majorbenks’s picture

majorbenks CreditAttribution: majorbenks commented
Version: 7.x-3.3 » 7.x-3.5
Status: Active » Needs review

Ouups, sorry I did not see the new update. With 3.5 everything is fine.

bunthorne’s picture

bunthorne CreditAttribution: bunthorne commented

3.5 appears to have reset the choices for me. I did have to rebuild the filters, however. Several of my views were no longer functioning correctly after the upgrade because of the change to the "is not one of" logic, apparently. No problem--I was using workaround logic anyway, and it needed to be fixed. So I am happy. Thank you for the fix!

ygerasimov’s picture

ygerasimov CreditAttribution: ygerasimov commented

Please mark RTBC if you think patch works as expected.

adeb’s picture

adeb CreditAttribution: adeb commented

Does anyone know the status of this ? Is the patch in the dev version? (if so it doesn't work for me) I delayed a drupal 6 upgrade way back in 2010 because of this issue, would be awesome to finally upgrade.
(http://drupal.org/node/1129152)

bunthorne’s picture

bunthorne CreditAttribution: bunthorne commented

Status update?

oneiropolos’s picture

oneiropolos CreditAttribution: oneiropolos commented
Version: 7.x-3.5 » 7.x-3.6

I am having the same issue, using Views 7.x-3.6

Gode.Agarunov’s picture

Gode.Agarunov CreditAttribution: Gode.Agarunov commented

Patch applied on latest version, 3.7, and seems to work without issue.

mattew’s picture

mattew CreditAttribution: mattew commented
Version: 7.x-3.6 » 7.x-3.7
Priority: Normal » Major
Issue summary: View changes

I think this is a major issue because Views does not work as expected and we have to read the query in the Views UI to see the problem ! (the query search for NULL value instead of properly testing the value).

The bug exists on 7.x-3.7 too.

mattew’s picture

mattew CreditAttribution: mattew commented
Status: Needs review » Needs work
Related issues: +#1450918: "Is None Of" does not show values of filtered field with Allowed Values, +#2069269: "Is none of" doesn't play nicely, +#1836138: Taxonomy term field does not work when using taxonomy term filter with "Is none of"