Could the filter order make a difference?

Question

I have a view which has several filters, some of them exposed. I've noticed that the order of these filters seems to affect how they work. For example, I have a Boolean field, and if I check it's either zero or null.

With ((field_data_field_hide_on_closer_cornwall.field_hide_on_closer_cornwall_value = '0') OR (field_data_field_hide_on_closer_cornwall.field_hide_on_closer_cornwall_value IS NULL) I get just two results returned, but ((field_data_field_hide_on_closer_cornwall.field_hide_on_closer_cornwall_value IS NULL) OR (field_data_field_hide_on_closer_cornwall.field_hide_on_closer_cornwall_value = '0'))), as expected, shows all my nodes apart from one record where that Boolean field is set to 1.

I also notice that on an exposed Taxonomy filter with four taxonomy items, the first three filters return the expected list of results, but the last checkbox says there are no nodes in that taxonomy, even though there ARE nodes in that taxonomy.

I am using Better Exposed Filters to create the checkboxes with Views 3 and Drupal 7.

Here's my entire mysql for the view, including the exposed filter on Taxonomy item 27 which mysteriously returns no results, when a filter on 29 or 28 returns the expected list.

I tried making a separate view with only one filter on the Category Taxonomy, and that returned my Taxonomy 27 nodes correctly.

Could filter order make a difference? If so, how can I identify what the order should I use?

SELECT user_badges_badges.bid AS user_badges_badges_bid, user_badges_badges.name AS user_badges_badges_name, user_badges_badges.image AS user_badges_badges_image, user_badges_badges.href AS user_badges_badges_href, user_badges_badges.weight AS user_badges_badges_weight, node.nid AS nid, node.title AS node_title, 'node' AS field_data_field_photos_node_entity_type, 'node' AS field_data_field_sleeps_node_entity_type, 'node' AS field_data_field_phone_number_node_entity_type, 'node' AS field_data_field_searchdescription_node_entity_type, 'node' AS field_data_field_accommodation_tags_node_entity_type, 'node' AS field_data_field_category_node_entity_type, 'node' AS field_data_field_region_node_entity_type, 'node' AS field_data_field_place_on_map_node_entity_type, 'node' AS field_data_body_node_entity_type, RAND(1422056994) AS _random_seed
FROM 
{node} node
LEFT JOIN {field_data_field_category} field_data_field_category ON node.nid = field_data_field_category.entity_id AND (field_data_field_category.entity_type = 'node' AND field_data_field_category.deleted = '0')
INNER JOIN {taxonomy_term_data} taxonomy_term_data_field_data_field_category ON field_data_field_category.field_category_tid = taxonomy_term_data_field_data_field_category.tid
LEFT JOIN {field_data_field_category} field_data_field_category2 ON node.nid = field_data_field_category2.entity_id AND field_data_field_category2.field_category_tid != '27'
LEFT JOIN {field_data_field_hide_on_closer_cornwall} field_data_field_hide_on_closer_cornwall ON node.nid = field_data_field_hide_on_closer_cornwall.entity_id AND (field_data_field_hide_on_closer_cornwall.entity_type = 'node' AND field_data_field_hide_on_closer_cornwall.deleted = '0')
LEFT JOIN {user_badges_user} user_badges_user ON node.uid = user_badges_user.uid
LEFT JOIN {user_badges_badges} user_badges_badges ON user_badges_user.bid = user_badges_badges.bid
WHERE (( (node.type IN  ('bronze', 'accommodation')) AND (node.status = '1') AND (field_data_field_category.field_category_tid IN  ('157', '31', '28', '29', '27')) )AND( (field_data_field_category2.field_category_tid = '27') )AND( (field_data_field_hide_on_closer_cornwall.field_hide_on_closer_cornwall_value IS NULL ) OR (field_data_field_hide_on_closer_cornwall.field_hide_on_closer_cornwall_value = '0') ))
ORDER BY user_badges_badges_weight ASC, _random_seed ASC
LIMIT 25 OFFSET 0

I have a number of filters there, but I can't see why putting the boolean field '='0' last in the WHERE statement works, whereas putting '=NULL' last causes the last filter to be ignored.

I'm filtering on category twice: once, an unexposed filter, to show only records that are in my four desired categories, and then another exposed filter again on category to allow the user to filter futher to just one or two categories. I wonder if this is causing a problem but if it is, I am stumped as to why this works for category 28, but not for the apparently identical category 27.

I tried removing the exposed category filter, re-ordering the categories in the taxonomy, then re-adding the filter, so that the last item in the field_data_field_category.field_category_tid array became category 28. Sure enough, my filter is now able to find nodes filtered to category 27, but can no longer find nodes in category 28. I tried changing the form to not autosubmit, and to use links instead of checkboxes to select a category, but the same behaviour (last taxonomy term in query is dropped) persisted.

It appears that the last option in a filter is being dropped? Still can't see why.

Answer 1

This issue - the results displayed by a view changing depending on the order of the filters - appears to be down to my having created two filters on the same taxonomy field - an unexposed field, and an exposed field. I removed the unexposed filter.

Instead of filtering twice on the same field, I created a new text field on the relevant content types. I created a Rule to copy the name of my Category field in to the new field Before Saving node.

I hid the new text field with Field Permissions module. Finally I created a Views Bulk Operations view to allow me to re-save all my existing nodes in one go, populating the new text field.

I now have one field I can use in an unexposed filter to create my initial view, and a different but similar field that I can use to provide an exposed filter.

This works, so I'm leaving it here as an answer, but it seems quite convoluted and messy, so I'm not accepting it as the ideal answer in the hope that someone may volunteer a better solution to the problem of needing to filter twice without running into weird filter order problems.