I have an issue with a few exposed filters not working properly with each other. I haven't been able to figure out the pattern, but I created a new Drupal installation (7.4, with only Views 7.x-3.0-rc1 and CTools 7.x-1.0-beta1 added on), just to confirm the problem.

In my test, there are only 2 content items. The fields are set up like this:

Color
field_color
Term reference
Autocomplete term widget (tagging)

Shape
field_shape
List (text)
Check boxes/radio buttons

Part Number
field_partnumber
Term reference
Autocomplete term widget (tagging)

Color taxonomy terms are:

Red
-Maroon
-Burgandy
Blue
-Baby Blue
--Baby Blue Child
-Phthalo Blue
--Phthalo Blue Child

The search page is set up in Views, and the Filter Criteria all use AND joins:

Content: Published (Yes)
Content: Has taxonomy terms (with depth) (exposed) --- depth is set to "3"
Content: Shape (exposed)
Content: Part Number (exposed)

Here are a few test cases, which you can try out at: http://domainspring.com/sample/search

1) Shape: Circle (works)
2) Has taxonomy terms (with depth): Blue (works)
3) Has taxonomy terms (with depth): Phthalo Blue Child (works)
4) Has taxonomy terms (with depth): Blue *and* Shape: Circle (doesn't work - no results)
5) Has taxonomy terms (with depth): Phthalo Blue *and* Shape: Circle (doesn't work - no results)
6) Has taxonomy terms (with depth): Phthalo Blue Child *and* Shape: Circle (works)
7) Has taxonomy terms (with depth): Blue *and* Shape: Circle *and* Part Number: 1111 (works)

So, it looks like there is something strange going on (how #4 doesn't work, but if you tack on the part number, it works, etc.) Any ideas? I'd be happy to include the SQL queries or anything else about my setup, if that helps.

Thanks

CommentFileSizeAuthor
#2 views_export.txt7 KBtj2653

Comments

merlinofchaos’s picture

merlinofchaos commented

Can you attach an export of the simplest view recreating this you can make?

tj2653’s picture

tj2653 commented
StatusFileSize
new views_export.txt7 KB

Attached as a TXT file, thanks.

tj2653’s picture

tj2653 commented

Here are SQL queries for some of the test cases:

1) Shape: Circle (works)

SELECT node.created AS node_created, node.nid AS nid FROM node node LEFT JOIN field_data_field_shape field_data_field_shape ON node.nid = field_data_field_shape.entity_id AND (field_data_field_shape.entity_type = :views_join_condition_0 AND field_data_field_shape.deleted = :views_join_condition_1) WHERE (( (node.status = :db_condition_placeholder_2) AND (field_data_field_shape.field_shape_value IN (:db_condition_placeholder_3)) )) ORDER BY node_created DESC LIMIT 10 OFFSET 0



2) Has taxonomy terms (with depth): Blue (works)

SELECT node.created AS node_created, node.nid AS nid FROM node node WHERE (( (node.status = :db_condition_placeholder_0) AND (node.nid IN (SELECT tn.nid AS nid FROM taxonomy_index tn LEFT OUTER JOIN taxonomy_term_hierarchy th ON th.tid = tn.tid LEFT OUTER JOIN taxonomy_term_hierarchy th1 ON th.parent = th1.tid LEFT OUTER JOIN taxonomy_term_hierarchy th2 ON th1.parent = th2.tid LEFT OUTER JOIN taxonomy_term_hierarchy th3 ON th2.parent = th3.tid WHERE ( (tn.tid = :db_condition_placeholder_1) OR (th1.tid = :db_condition_placeholder_2) OR (th2.tid = :db_condition_placeholder_3) OR (th3.tid = :db_condition_placeholder_4) ))) )) ORDER BY node_created DESC LIMIT 10 OFFSET 0



4) Has taxonomy terms (with depth): Blue *and* Shape: Circle (doesn't work - no results)

SELECT node.created AS node_created, node.nid AS nid FROM node node LEFT JOIN field_data_field_shape field_data_field_shape ON node.nid = field_data_field_shape.entity_id AND (field_data_field_shape.entity_type = :views_join_condition_0 AND field_data_field_shape.deleted = :views_join_condition_1) WHERE (( (node.status = :db_condition_placeholder_2) AND (node.nid IN (SELECT tn.nid AS nid FROM taxonomy_index tn LEFT OUTER JOIN taxonomy_term_hierarchy th ON th.tid = tn.tid LEFT OUTER JOIN taxonomy_term_hierarchy th1 ON th.parent = th1.tid LEFT OUTER JOIN taxonomy_term_hierarchy th2 ON th1.parent = th2.tid LEFT OUTER JOIN taxonomy_term_hierarchy th3 ON th2.parent = th3.tid WHERE ( (tn.tid = :db_condition_placeholder_1) OR (th1.tid = :db_condition_placeholder_2) OR (th2.tid = :db_condition_placeholder_3) OR (th3.tid = :db_condition_placeholder_4) ))) AND (field_data_field_shape.field_shape_value IN (:db_condition_placeholder_3)) )) ORDER BY node_created DESC LIMIT 10 OFFSET 0



7) Has taxonomy terms (with depth): Blue *and* Shape: Circle *and* Part Number: 1111 (works)

SELECT node.created AS node_created, node.nid AS nid FROM node node LEFT JOIN field_data_field_shape field_data_field_shape ON node.nid = field_data_field_shape.entity_id AND (field_data_field_shape.entity_type = :views_join_condition_0 AND field_data_field_shape.deleted = :views_join_condition_1) INNER JOIN field_data_field_partnumber field_data_field_partnumber ON node.nid = field_data_field_partnumber.entity_id AND (field_data_field_partnumber.entity_type = :views_join_condition_2 AND field_data_field_partnumber.deleted = :views_join_condition_3) WHERE (( (node.status = :db_condition_placeholder_4) AND (node.nid IN (SELECT tn.nid AS nid FROM taxonomy_index tn LEFT OUTER JOIN taxonomy_term_hierarchy th ON th.tid = tn.tid LEFT OUTER JOIN taxonomy_term_hierarchy th1 ON th.parent = th1.tid LEFT OUTER JOIN taxonomy_term_hierarchy th2 ON th1.parent = th2.tid LEFT OUTER JOIN taxonomy_term_hierarchy th3 ON th2.parent = th3.tid WHERE ( (tn.tid = :db_condition_placeholder_1) OR (th1.tid = :db_condition_placeholder_2) OR (th2.tid = :db_condition_placeholder_3) OR (th3.tid = :db_condition_placeholder_4) ))) AND (field_data_field_shape.field_shape_value IN (:db_condition_placeholder_5)) AND (field_data_field_partnumber.field_partnumber_tid = :db_condition_placeholder_6) )) ORDER BY node_created DESC LIMIT 10 OFFSET 0
urbanhick’s picture

urbanhick commented

I am having this same issue. Can be replicated with a taxonomy of 3 terms no depth using the taxonomy with depth filter. Content tagged with one of the terms is only searchable with the -Any- selection, if it's actual taxonomy is searched no results are found.

Note that it works using the taxonomy without depth filter using the same settings.

dawehner’s picture

dawehner
Primary language German
commented
Status: Active » Postponed (maintainer needs more info)

Seems to be a duplicate of #1112854: Subqueries use wrong arguments

Perhaps you could look at the sql output, which would help to understand it

peyrol’s picture

peyrol commented
Status: Active » Needs review

Hello

I have some strange behavior with Views when one of filters is 'Has Filter Has taxonomy terms (with depth)' and one of sorting fields is enabled. I have no one row (and I should have) in output but pagination show some pages.

The key is that one field is defined by me, there is no problem with original field (just checked for created date time field).

First I think it has something with Exposed Filters, but I'd rather think it's something with joining the same table when depth is set.

Versions:
drupal 7.7
views 7.x-3.0-rc1

Best Regards
Przemek

dawehner’s picture

dawehner
Primary language German
commented
Category: bug » support
Status: Postponed (maintainer needs more info) » Active

Needs review means that there is a patch on the issue, update status.

please try to read http://drupal.org/node/571990 as this really helps other people to understand the issue.

peyrol’s picture

peyrol commented
Component: Miscellaneous » taxonomy data
Category: support » bug
Status: Needs review » Active

Did I made update status in right way?
Przemek

peyrol’s picture

peyrol commented

Hello

Below i put queries to database when exposed filter is set to user and when it is not.

This sql is for filter Has taxonomy terms (with depth) (= something) and sorting by data time field is exposed to user: 

SELECT node.title AS node_title, node.nid AS nid, node.created AS node_created, 'node' AS field_data_field_machine_name_of_type_like_article_name_of_filed_1_node_entity_type, 'node' AS field_data_field_machine_name_of_type_like_article_name_of_filed_2_node_entity_type, 'node' AS field_data_field_machine_name_of_type_like_article_name_of_filed_3_problematic_node_entity_type
FROM 
node node
WHERE (( (node.status = '1') AND (node.type IN  ('machine_name_of_type_like_article')) AND (node.nid IN  (SELECT tn.nid AS nid
FROM 
taxonomy_index tn
LEFT OUTER JOIN taxonomy_term_hierarchy th ON th.tid = tn.tid
LEFT OUTER JOIN taxonomy_term_hierarchy th1 ON th.parent = th1.tid
LEFT OUTER JOIN taxonomy_term_hierarchy th2 ON th1.parent = th2.tid
LEFT OUTER JOIN taxonomy_term_hierarchy th3 ON th2.parent = th3.tid
LEFT OUTER JOIN taxonomy_term_hierarchy th4 ON th3.parent = th4.tid
LEFT OUTER JOIN taxonomy_term_hierarchy th5 ON th4.parent = th5.tid
LEFT OUTER JOIN taxonomy_term_hierarchy th6 ON th5.parent = th6.tid
LEFT OUTER JOIN taxonomy_term_hierarchy th7 ON th6.parent = th7.tid
LEFT OUTER JOIN taxonomy_term_hierarchy th8 ON th7.parent = th8.tid
LEFT OUTER JOIN taxonomy_term_hierarchy th9 ON th8.parent = th9.tid
LEFT OUTER JOIN taxonomy_term_hierarchy th10 ON th9.parent = th10.tid
WHERE ( (tn.tid = '1') OR (th1.tid = '1') OR (th2.tid = '1') OR (th3.tid = '1') OR (th4.tid = '1') OR (th5.tid = '1') OR (th6.tid = '1') OR (th7.tid = '1') OR (th8.tid = '1') OR (th9.tid = '1') OR (th10.tid = '1') ))) ))
ORDER BY node_created DESC
LIMIT 10 OFFSET 0

Return many rows. Sorting by name_of_filed_3_problematic is not defined at all.

This sql is for filter Has taxonomy terms (with depth) (= something) and sorting by data time field and sorting by name_of_filed_3_problematic is exposed to user:

SELECT node.title AS node_title, node.nid AS nid, node.created AS node_created, field_data_field_machine_name_of_type_like_article_name_of_filed_3_problematic.field_machine_name_of_type_like_article_name_of_filed_3_problematic_value AS field_data_field_machine_name_of_type_like_article_name_of_filed_3_problematic_field_pozycja_ofe, 'node' AS field_data_field_machine_name_of_type_like_article_name_of_filed_1_node_entity_type, 'node' AS field_data_field_machine_name_of_type_like_article_name_of_filed_2_node_entity_type, 'node' AS field_data_field_machine_name_of_type_like_article_name_of_filed_3_problematic_node_entity_type
FROM 
node node
LEFT JOIN field_data_field_machine_name_of_type_like_article_name_of_filed_3_problematic field_data_field_machine_name_of_type_like_article_name_of_filed_3_problematic ON node.nid = field_data_field_machine_name_of_type_like_article_name_of_filed_3_problematic.entity_id AND (field_data_field_machine_name_of_type_like_article_name_of_filed_3_problematic.entity_type = 'node' AND field_data_field_machine_name_of_type_like_article_name_of_filed_3_problematic.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN  ('machine_name_of_type_like_article')) AND (node.nid IN  (SELECT tn.nid AS nid
FROM 
taxonomy_index tn
LEFT OUTER JOIN taxonomy_term_hierarchy th ON th.tid = tn.tid
LEFT OUTER JOIN taxonomy_term_hierarchy th1 ON th.parent = th1.tid
LEFT OUTER JOIN taxonomy_term_hierarchy th2 ON th1.parent = th2.tid
LEFT OUTER JOIN taxonomy_term_hierarchy th3 ON th2.parent = th3.tid
LEFT OUTER JOIN taxonomy_term_hierarchy th4 ON th3.parent = th4.tid
LEFT OUTER JOIN taxonomy_term_hierarchy th5 ON th4.parent = th5.tid
LEFT OUTER JOIN taxonomy_term_hierarchy th6 ON th5.parent = th6.tid
LEFT OUTER JOIN taxonomy_term_hierarchy th7 ON th6.parent = th7.tid
LEFT OUTER JOIN taxonomy_term_hierarchy th8 ON th7.parent = th8.tid
LEFT OUTER JOIN taxonomy_term_hierarchy th9 ON th8.parent = th9.tid
LEFT OUTER JOIN taxonomy_term_hierarchy th10 ON th9.parent = th10.tid
WHERE ( (tn.tid = '1') OR (th1.tid = 'machine_name_of_type_like_article') OR (th2.tid = '1') OR (th3.tid = '1') OR (th4.tid = '1') OR (th5.tid = '1') OR (th6.tid = '1') OR (th7.tid = '1') OR (th8.tid = '1') OR (th9.tid = '1') OR (th10.tid = '1') ))) ))
ORDER BY field_data_field_machine_name_of_type_like_article_name_of_filed_3_problematic_field_pozycja_ofe DESC, node_created DESC
LIMIT 10 OFFSET 0

Return 0 rows.

The difference is for working view setting :
WHERE ( (tn.tid = '1') OR (th1.tid = '1') OR (th2.tid = '1') OR (th3.tid = '1') OR (th4.tid = '1') OR (th5.tid = '1') OR (th6.tid = '1') OR (th7.tid = '1') OR (th8.tid = '1') OR (th9.tid = '1') OR (th10.tid = '1') ))) ))
and for not working view setting:
WHERE ( (tn.tid = '1') OR (th1.tid = 'machine_name_of_type_like_article') OR (th2.tid = '1') OR (th3.tid = '1') OR (th4.tid = '1') OR (th5.tid = '1') OR (th6.tid = '1') OR (th7.tid = '1') OR (th8.tid = '1') OR (th9.tid = '1') OR (th10.tid = '1') ))) ))

For putting 1 instead of machine_name_of_type_like_article in second WHERE clause it works fine.

One more thing is that, for two view settings there is count(*) query. For these queries WHERE are:
without sorting filed:
WHERE ( (tn.tid = '1') OR (th1.tid = '1') OR (th2.tid = '1') OR (th3.tid = '1') OR (th4.tid = '1') OR (th5.tid = '1') OR (th6.tid = '1') OR (th7.tid = '1') OR (th8.tid = '1') OR (th9.tid = '1') OR (th10.tid = '1') ))) ))) subquery
with sorting field by name_of_filed_3_problematic:
WHERE ( (tn.tid = '1') OR (th1.tid = '1') OR (th2.tid = '1') OR (th3.tid = '1') OR (th4.tid = '1') OR (th5.tid = '1') OR (th6.tid = '1') OR (th7.tid = '1') OR (th8.tid = '1') OR (th9.tid = '1') OR (th10.tid = '1') ))) ))) subquery
Both returns right pagination.

my problems has nothing with exposed to user setting.

I'm hope I did right this bug report.

Best regards
Przemek

tj2653’s picture

tj2653 commented

Just to update, my original problem was solved by one of the patches in the other thread. Thanks.

dawehner’s picture

dawehner
Primary language German
commented
Status: Active » Fixed

The problem described in #1228280: Strange Issue with Exposed Filters is exactly which should be fixed by the subquery issue.
Thanks for reporting that it's fixed for you.

Update the status

Status: Fixed » Closed (fixed)

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

Anonymous’s picture

(not verified) commented
Issue summary: View changes

Added version numbers