Drupal seems to lack a solid "Is none of" operator for many operations (see:
#225899: "None of" (NOR) filter incorrect on vocabulary name
#463856: Views "is none of" NOR operator FAILS on multiple values
http://drupalredesign.testing-zone.net/node/328243
and so on
).

I, for one, would like to have a working "Vocabulary is none of…" in D6 (the behaviour I was looking for in D5 with #225899: "None of" (NOR) filter incorrect on vocabulary name).

Test case:
Clean install of Drupal 6.x-dev, CCK 6.x-2.x-dev (by the way, it wasn't needed, but… too much used to it) and Views 6.x-2.x-dev, as of 2009-12-30, running on MySQL.
Enabling Content, Views and Views UI, update.php.
Administer, Taxonomy, Add vocabulary, Test, Story, Multiple select, Save, add terms, t1, Save, t2, Save.
Administer, Taxonomy, Add vocabulary, Unimportant, Story, Multiple select, Save, add terms, u1, Save, u2, Save.
Create content, Story, With none, u1, Save.
Create content, Story, With one, u1, t2, Save.
Create content, Story, With both, u1, t1, t2, Save.
Administer, Views, Add, none_of_test, Next, Fields, Node: title (to have a usable preview), Add, Update, Filters: +, Taxonomy: Vocabulary, Is none of, Test, Update.
Preview.
Here we get all results… because u1 (which we don't care for this filter, as it belongs to vocabulary Unimportant) has been given to each Story, and table joins make it unvoluntarily participate in the filter.

I made the attached patch to get the behaviour I wanted. After applying it, and emptying the caches, it works as expected (and the same patch on a one-year old production Drupal install shows no side effect).

The patch contains:
- a views_join_excluding class, that either calls its superclass (views_join) or gently composes the query as wanted, based on the type of join it is asked (join between the already accumulated pack of tables and a new, single table, or join between the pack and a table coming with its own subtables).
- an awful implementation (string insertions and so on), because I first composed it on an old D6 release; but newer Drupal versions seem to allow more flexibility between tables in queries: perhaps a cleaner implementation could be based on those features?
- and the final use: taxonomy, for which an INNER join is used instead of a LEFT one, so that views_join_excluding handles term_node and term_data together.

See #225899: "None of" (NOR) filter incorrect on vocabulary name for thoretical explanation of what it does (although, on this D6 patch, I was much less clean than on the D5 one).

This generic class could serve as a base for all kind of "Is none of" filters that require join between second-class tables.

CommentFileSizeAuthor
views_join_excluding-with-taxonomy.patch5.58 KBguillaume.outters

Comments

merlinofchaos’s picture

merlinofchaos commented
Status: Needs review » Needs work 
+    if ($this->operator == 'not in') {
+      $not_join = new views_join_excluding();
+      $not_join->construct($this->table, $this->view->base_table, $this->view->base_field, $this->field, array_values($this->value));
+      $not_join_alias = $this->query->queue_table($this->table, NULL, $not_join);
+    }
+    else {
     $this->query->add_where($this->options['group'], "$this->table_alias.$this->real_field " . $this->operator . $in, array_values($this->value));
+    }

Minor: The above is missing indentation in the else clause.

Major: Really the in_operator isn't meant to be used for items where you might do the join. I don't think this will actually work very well. This is great for things like 'node type' where you're simply selecting a single field against a list. The joining has to happen elsewhere. The problem you're trying to solve is supposed to be dealt with by the many_to_one_helper.

-    if (!$join) {
+    if (!$join && $relationship) {
       $join = $this->get_join_data($table, $this->relationships[$relationship]['base']);
     }

The ramifications of doing this are huge. What if you end up there without a join and then you don't go and fetch the join? This could be very bad. Why is this change in there?

       'left_table' => 'term_node',
       'left_field' => 'tid',
       'field' => 'tid',
+      'type' => 'INNER',

Doing this means that Taxonomy: Term will filter any node that has no terms, and that will break many use cases.

Overall, I'm not comfortable with the general approach here. If there is something broken, it needs to be fixed at the many_to_one_helper which is what will handle the join for the exclusion. There is already code for this that is similar to your join.

esmerel’s picture

esmerel commented
Status: Needs work » Closed (won't fix)

No work done on thsi patch in more than 3 months