0

Strange effect in my view:

I have a list of nodes. On of the fields is a term reference.

  • I want the list to only show nodes related to 3 specific terms. that's one filter.
  • I want the user to optionally select one of these 3 terms. that's another (exposed) filter.

It almost seems to work. But if the user selects that term from filter 2, in the sql, the first filter is rewritten to explicitly deny that term. zero results. In an almost duplicate display of that view, the same happens, but with one other term (of the same three).

So without selecting anything in filter 2, the sql is:

SELECT DISTINCT bla
FROM 
{node} node
...
INNER JOIN {field_data_field_publication_cat} field_data_field_publication_cat ON node.nid = field_data_field_publication_cat.entity_id AND (field_data_field_publication_cat.entity_type = 'node' AND field_data_field_publication_cat.deleted = '0')
...
WHERE (( (node.status = '1') AND (node.type IN  ('publication')) AND (field_data_field_publication_cat.field_publication_cat_tid IN  ('48', '50', '49')) ))
ORDER BY ... LIMIT .. OFFSET .. 

and as soon as I select one of the terms in filter two, it reads

SELECT DISTINCT bla
FROM 
{node} node
...
INNER JOIN {field_data_field_publication_cat} field_data_field_publication_cat ON node.nid = field_data_field_publication_cat.entity_id AND (field_data_field_publication_cat.entity_type = 'node' AND field_data_field_publication_cat.deleted = '0')
LEFT JOIN {field_data_field_publication_cat} field_data_field_publication_cat2 ON node.nid = field_data_field_publication_cat2.entity_id AND field_data_field_publication_cat2.field_publication_cat_tid != '48'
...
WHERE (( (node.status = '1') AND (node.type IN  ('publication')) AND (field_data_field_publication_cat.field_publication_cat_tid = '48') AND (field_data_field_publication_cat2.field_publication_cat_tid IN  ('48', '50', '49')) ))
ORDER BY ... LIMIT .. OFFSET .. 

The number 48 in the second query, second join seems fairly random. If I remove and reattach the filter it seems to change.

cat2 is only a left join, but in the where clause, the number 48 is required again. Thats why I get zero results.

What's going on ?

  • .. if I enable 'reduce duplicates' on the second (exposed) filter, the problem disappears. That helps me out, but doesn't answer the question .. still doesn't make sense to me. – commonpike Nov 23 '15 at 12:20
  • was tagged: views-sql :-D – commonpike Nov 27 '15 at 10:38
0

Notice that filters are by default added in AND logic. You probably want OR logic so the one filter does not affect the other.

Of course if the two fields are not relevant at all you should have used different vocabularies (e.g. terms and someothercategory)and not only terms

  • both filters operate on the same three terms. the first filter limits results to only these 3 terms. the second filter is exposed and optionally lets the user choose one of the three terms. – commonpike Nov 23 '15 at 10:55
  • If I use 'OR', always get all results related to all 3 terms, regardless of the users seletion in the second filter. this makes sense. the SQL is (field_data_field_publication_cat.field_publication_cat_tid IN ('50', '49', '48')) OR (field_data_field_publication_cat2.field_publication_cat_tid = '48') )) – commonpike Nov 23 '15 at 10:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.