| Project: | Views |
| Version: | 6.x-3.x-dev |
| Component: | Code |
| Category: | feature request |
| Priority: | normal |
| Assigned: | merlinofchaos |
| Status: | closed (fixed) |
| Issue tags: | views 3.x roadmap, views grouping, views or |
Issue Summary
Unless I'm mistaken, I don't think its possible to OR filters together instead of ANDing them when setting up a view.
Let me explain where I'm coming from with this.
With my project, I have a content type "Project" with 3 user reference fields - Team Manager, Designers, and Programmers. I would like to create a view of "My Projects" where my user is referenced in either one of the three user reference fields.
From a UI perspective, I had in mind an addition to the filters table called "Group" which would either be a textfield or a dropdown of numbers (say 0-20). When you setup your filters, you put the fields you want ORed together into the same group. All the groups will be ANDed together while all fields inside a group are ORed against each other.
Using my example, I would have 4 filters:
- Node Type = Project, no group
- Team Manager = Current User, group 1
- Designer = Current User, group 1
- Programmer = Current User, group 1
The resulting query having something like: node.type = "content_project" AND (content_field_manager = 1 OR content_field_designer = 1 OR content_field_programmer = 1)
Does that sound reasonable, and if so what about the UI to handle it? I get the feeling that it may be a big deal to get this working properly as there are a lot of things that may need to change (database structure?) and some that may break (exposed filters?).
Thoughts? Comments? Worth implementing?
Comments
#1
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.
#2
This plan got punted to Views 2.1 -- asisigning ot myself so I can find this for marking duplicates.
#3
I am very interested in this feature, if you need help of any kind I'll be glad to jump in.
#4
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?
#5
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!
#6
This will be such a killer feature ;)
After IRC with merlinofchaos, i wanted to note two things:
#7
Thanks scottrigby.
FWIW, adding a link to views_or, which looks very promising: http://drupal.org/project/views_or.
#8
Is this coming soon? Should we use views or at this stage?
#9
+1
#10
+1
#11
+1
#12
+1
#13
+1 here. Using views_or which is fantastic, just want to keep updated for when the migration to Views2 takes place.
#14
subscribing
#15
Subscribed.
#16
guess I'm going to try the views or,...
#17
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.
#18
adding a tag und pumping to 3.x
#19
subscribe
#20
View_or just broke for me with the new dev version. I'm excited to see this making it's way into views proper.
#21
track
#22
+1 using views or currently but looking for when it will be included into views
#23
+1
#24
+1 Subscribe
#25
+1 subscribing
#26
Subscribe, +1
#27
Anything new about it?
#28
Subscribing
#29
Subscribe.
#30
For Shizzle
#31
subscribe
#32
+1
#33
subscribing.
#34
subscribing....
#35
subscribe
#36
subscribe
#37
subscribe
#38
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.
#39
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:
cond1 = a AND bcond2 = c AND d
( cond1 AND !cond2 ) OR
( cond2 AND !cond1 )
( 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.
#40
subscribe
#41
Yes, it is correct.
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.
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.
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.
#42
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.
#43
This is the new UI with the discussed changes.
#44
subscribe
#45
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.
#46
Subscribe
#47
Sub
#48
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.
#49
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.
#50
if you have an exposed filter alone in one group, views displays a sql error.
#51
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.
#52
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.
#53
superscribe
#54
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.
#55
subscribing
#56
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.
#57
Here's a screenshot:
#58
@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?
#59
Automatically closed -- issue fixed for 2 weeks with no activity.
#60
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?
#61
@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.
#62
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 :-)
#63
[Edit: In response to comment #65, I've published this comment separately. Looks like the attachments are staying though!]
#64
subscribing
#65
#63 should be posted as a new issue, I think. Please?
#66
@kbk
Clear the theme registry, and it will be fine. And if you don't know how, search for it :)
#67
subscribing - sorry cannot help out.
thanks the excellent feature coming.
#68
Subscribe
#69
lol, thanks
#70
Subscribing
#71
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
#72
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
#73
Yep noticed it 2 late :-), done on http://drupal.org/node/935984