Adding sql ORing capability with filters

I have a plan for including a primitive OR in Views 2; but it's unlikely that it will make it into the current version of Views, and Views is a little ways out.

I realize this is a difficult limitation, but right now it's what you got. Your best bet for a work around would be to use the Views API and expose a filter that can do what you need. It's a bit complex but it is possible.

This plan got punted to Views 2.1 -- asisigning ot myself so I can find this for marking duplicates.

I am very interested in this feature, if you need help of any kind I'll be glad to jump in.

I am also very interested in this feature ...

My site deals with product development projects in different development stages. I'd like to create a table view with columns representing each stage (4 or 5 stages), and simply populate the table with the node title as a link to the full record (sample image attached without columns filtered by stage). Each column would need to be filtered to the respective stage for that column. I would also add ANDed filters for selecting the displayed subset by business unit, for example, or perhaps even a subcategory within a business unit. The desired feature is to have projects "move" from stage to stage in this view by simply updating the stage field as the project progresses from stage to stage.

Will this be possible with Views 2.X?

Is the only way of doing this at present still $query->add_where in a filter handler, per #56444: ability to logically OR filter clauses?

I was hoping there might be a way of "adding" together the results of 2 separate Views ... actually I thought maybe that was what attachment displays did, until the penny dropped!

This will be such a killer feature ;)

After IRC with merlinofchaos, i wanted to note two things:

1. This module (temporary measure, hopefully folded into Views @ some point): views_or
2. Once this feature is available in Views for filters, hopefully this can be extended to work with arguments as well #141342: Allow disjunctive conjunction (OR) of arguments
   

Thanks scottrigby.

FWIW, adding a link to views_or, which looks very promising: http://drupal.org/project/views_or.

Is this coming soon? Should we use views or at this stage?

+1

+1

+1

+1

+1 here. Using views_or which is fantastic, just want to keep updated for when the migration to Views2 takes place.

subscribing

Subscribed.

guess I'm going to try the views or,...

Subscribed.

I really, really need this for my web site.

The views_or module is in a dev rev, so I am not using it, although in principle it would meet my needs.

But I am extremely grateful for Views itself already - I think it is awesome and already has allowed me to do so much.

So I respect merlinofchaos' time, and I will wait patiently.

adding a tag und pumping to 3.x

subscribe

View_or just broke for me with the new dev version. I'm excited to see this making it's way into views proper.

track

+1 using views or currently but looking for when it will be included into views

+1

+1 Subscribe

+1 subscribing

Subscribe, +1

Anything new about it?

Subscribing

Subscribe.

For Shizzle

subscribe

+1

subscribing.

subscribing....

subscribe

subscribe

subscribe

I have created an initial UI to see if this is what we are looking for.

Views 2, and 3 already provide a mechanism to group filters using OR. We only need a user interface to configure this settings.

Since views 3 introduces Group By, these filters cannot be grouped into the same groups that non aggregated filters.

For this reason, this UI provide an special group to put all "COUNT. SUM, MAX, etc " filters.

A similar UI can be defined to #141342: Allow disjunctive conjunction (OR) of arguments

I used firebug to produce this UI, sorry a patch for this UI is a little complex and we first need to know what kind of UI are we looking for.

Hi dagmar

It's so great you're working on this! I've been trying to learn Drupal insides better to look at just this problem... still a ways off tho!

I think what you've done looks excellent - just the kind of thing we need. First of all can I check I understand correctly. The example you gave evaluates as

( (NID >= 3) OR (Type == Page) OR (NID != 26) OR (Published) ) OR //group 0
( (NID >= 3) AND (Type == Page) ) OR //group 1
( (NID >= 3) AND (Type == Story) AND (NID != 500) AND (Published) ) OR //group 3
( (Count >= 3) ) //aggregator group

Is that right?

I was wondering... is it necessary to have all those choices of AND/OR? I find logic a sticky area, but it seems to me you could get the same effective results as the screenshot with this.

( a AND b AND c ... ) OR ( d AND e AND f ... ) OR ...

IE the groups are always ORed and within groups the logic is always AND. The only problem I can see with that for normal (non-aggregated groups) is for XOR. A simple test like ( a AND b ) XOR ( c AND d) can't be done AFAICT with this system. However it would still make the filters much more powerful IMHO, even without that capability.

Regarding aggregated filters, I don't know Views 3, and I'm not sure I fully understand what you said about them. From what I can grok, I think they might need their own set of groups (rather than a single group), in case I want to do something like...

( ( count > 3) AND ( max > 100 ) ) OR 
( ( sum > 300 ) )

Does that make sense?

Some other possible ideas:

• Allow named boolean variables. This would allow compound tests, and also potentially make some filters easier to understand, but maybe it makes the learning curve too steep? The XOR example above could be done like this for example (assuming we don't allow native XOR handling).
  

  cond1 = a AND b
  cond2 = c AND d
  ( cond1 AND !cond2 ) OR
  ( cond2 AND !cond1 )
  

  This example isn't a pretty one mind. The UI wouldn't be too difficult, with a Create new variable button, and a new field entry for the variable.

• Allow subgroups. This would be the equivalent of using parentheses in expressions. So we might want
  

  ( a AND b AND !( c AND d) ) OR
  ( c AND d AND !( a AND b) )
  

  I know you're really asking about UI, and I'm not sure the best way to do this without cluttering the interface and confusing users. Maybe something similar to the hierarchical drag and drop for organising eg. menu items?

Btw, I'm using the XOR in these two examples arbitrarily - the methods can be used to achieve other kinds of logic.

I hope all of this is meaningful! Thanks for the work you've done so far, this is a real important piece of functionality IMHO.

subscribe

I think what you've done looks excellent - just the kind of thing we need. First of all can I check I understand correctly. The example you gave evaluates as
...
Is that right?

Yes, it is correct.

I was wondering... is it necessary to have all those choices of AND/OR? I find logic a sticky area, but it seems to me you could get the same effective results as the screenshot with this.

( a AND b AND c ... ) OR ( d AND e AND f ... ) OR ...

Mmm I'm afraid that this is not correct. There is some kind of filters that needs this structure (a OR b) and (c OR d) and they cannot be easily converted into an (w AND x) OR (y AND z) form.

Regarding aggregated filters, I don't know Views 3, and I'm not sure I fully understand what you said about them. From what I can grok, I think they might need their own set of groups (rather than a single group), in case I want to do something like...

( ( count > 3) AND ( max > 100 ) ) OR
( ( sum > 300 ) )

I think this is possible too, I only have to include another button to create a aggregation group. I search into views 3 api and this is currently possible by code, so, it can be implemented using the UI.

Allow named boolean variables. This would allow compound tests, and also potentially make some filters easier to understand, but maybe it makes the learning curve too steep? The XOR example above could be done like this for example (assuming we don't allow native XOR handling).
cond1 = a AND b
cond2 = c AND d
( cond1 AND !cond2 ) OR
( cond2 AND !cond1 )

This example isn't a pretty one mind. The UI wouldn't be too difficult, with a Create new variable button, and a new field entry for the variable.
Allow subgroups. This would be the equivalent of using parentheses in expressions. So we might want
( a AND b AND !( c AND d) ) OR
( c AND d AND !( a AND b) )

I know you're really asking about UI, and I'm not sure the best way to do this without cluttering the interface and confusing users. Maybe something similar to the hierarchical drag and drop for organising eg. menu items?

Mmm this is not currently possible. The secret of how groups are joined resides into function condition_sql() in views_plugin_query_default.inc, as you can see here views uses implode to join all these groups using "$this->group_operator" so, create subgroups it isn't currently possible.

Anyway, with the actual api of view there are a lot of possiblilities, I think that we first should provide the UI for this api, and next see if we can implement other features.

I'm with you, thanks for the reply. Then IMHO it seems you've got a really nice UI if you can handle those aggregated filters like you mentioned.

This is the new UI with the discussed changes.

subscribe

I don't believe that aggregated filters will need their own groups. At least, I don't understand why they should. From the perspective of this level, they're just filters with extra goo on them, aren't they?

Otherwise, I like this UI. My initial thoughts were to use tablesort's hierarchy but I think just using different tables for the OR groups is better.

Subscribe

Sub

Here's a first pass. I am sure this needs some work, I haven't actually done a lot of testing since I've been working on this for a good 7 or 8 hours now, I'm pretty beat. Will look more at this later when I have my brain back, but I'd love it if people (preferably people who understand this topic) take a look at this.

I finally see why the aggregated filters need to be grouped separately. I don't want to do that if I can avoid it, though, because there is not going to be any way to explain to the end user why it has to be this way.

I think we need to find a way to mark some filters as ungroupable and stick them in their own special group(s) and maybe that's the case here. For example, I'm not sure taxonomy filters are groupable if using depth, because they do several add_where() clauses.

Caching was broken in that one. And probably still needs some work actually.

Also I forgot to modify the actual list of filters to display everything, so that definitely still needs work.

if you have an exposed filter alone in one group, views displays a sql error.

I have done, some tests. This works fine if:

Groups contains at least one non exposed filter and only is accepted the last group to be empty. When the default filter doesn't contains non exposed filters, sql fails.

The drag and drop seems to be working fine, there is an small bug when a user delete all filters into a group, this group should display "This group is empty" but it doesn't.

Here are another comments:

+++ css/views-admin.css	27 Dec 2009 04:59:49 -0000
@@ -665,3 +665,34 @@ html.js .views-hidden {
+ 	display: none;

Small indentation bug.

+++ includes/view.inc	27 Dec 2009 04:59:53 -0000
@@ -557,6 +557,15 @@ class view extends views_db_object {
+    // Set the filtering groups.
+    $filter_groups = $this->display_handler->get_option('filter_groups');
+    if ($filter_groups) {
+      $this->query->set_group_operator($filter_groups['operator']);
+      foreach($filter_groups['groups'] as $id => $operator) {
+        $this->query->set_where_group($operator, $id);
+      }
+    }

This should check that the created group will contain a valid filter before create the group.

I think exposed filters, and those custom filters like taxonomy with depth enabled should return FALSE to some function like is_groupable() and one group should be created only if contains at least one filter with is_groupable() == TRUE.

+++ js/ajax.js	27 Dec 2009 04:59:53 -0000
@@ -316,3 +316,53 @@ Drupal.behaviors.ViewsAjaxLinks = functi
+    var checkEmptyRegions = function(table, rowObject) {

This function doesn't check if a region is empty when all filters of a group were deleted.

Also it seems that GROUP BY is still not implemented right?

I think that this is a really advanced feature of view, and end user should read the documentation to know how it works, it really difficult merge WHERE and HAVING groups into a same "visual" group. In fact, many times this is simply not possible, SQL doesn't support filters like:

SELECT all nodes WHERE (type = page AND number of nodes > 50) OR (type = story AND number of nodes > 30)

Visually, an user may want create a filter like this, however this is not possible without use UNION afaik.

For this reason, I support the idea of create isolated groups for aggregated filters, we can provide only one group to simplify the UI if we want, but probably, they will be needed to support GROUP BY properly.

Ok, fixed the problems with deleting filters via the remove button, made the actual list of filters show the and/or logic, fixed the caching issues and fixed the issue with exposed filters and empty filter groups (a simple array_filter() took care of that one).

There is still no provision for 'ungroupable' filters and grouping differently for filters that will use HAVING instead of GROUP BY. Still need to think on that one.

superscribe

The Rules module has similar AND/OR functionality... you might look at their UI for some tips. But I like that the Views UI would be draggable... that saves like 20 clicks.

subscribing

I have cleaned up the temporary caching and ungroupable issues. For now, all HAVING filters are simply ungroupable.

We're going to need to identify core filters that are ungroupable and properly set them to be ungroupable.

We're going to need to document how other modules are going to have to deal with this.

Here's a screenshot:

@merlinofchaos
Could you attach the patch, please? I guess you didn't commit the patch to d7 :)

We're going to need to identify core filters that are ungroupable and properly set them to be ungroupable.

Is there something like a rule? Mutliple add_wheres, for example?

This won't ever be backported to DRUPAL-6--2, would it? What solution would be the recommended one for the 6.x-2.x branch?

@60: no, suggest you either 1. wait for 6.x-3.x, 2. help speed 6.x-3.x towards full release by testing the alpha release, 3. use Views Or module pro tem.

Thanks, I will definitely give Views 6.x-3.0-alpha2 (or whatever 3.0 pre-release will be out) once CCK #717128: Release of 6.x-2.7 has been done.

In the meanwhile, I went with the Views Or module which does the job for me using Views 2 :-)

[Edit: In response to comment #65, I've published this comment separately. Looks like the attachments are staying though!]

subscribing

#63 should be posted as a new issue, I think. Please?

@kbk

Clear the theme registry, and it will be fine. And if you don't know how, search for it :)

subscribing - sorry cannot help out.
thanks the excellent feature coming.

Subscribe

lol, thanks

Subscribing

I tried or-ing my 3.x view today but got unexpected results.

Wanted to filter on (content_type=X) OR (content_type=Y AND Y_cck_field = Z).

This will only show Y_cck_field = Z, when I watched the query views generated I noticed that it did an INNER JOIN on Y_CCK_field, so I guess that's why I got no content_type X in my results.

This a bug or should I rearrange my filters in another way ?

Tnx

Can you please create a new issue for this?

The issue itself is already closed, so it's recommended to create a new one, because it's much easier to handle the problems :) Thanks

Yep noticed it 2 late :-), done on http://drupal.org/node/935984