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?
Comment | File | Size | Author |
---|---|---|---|
#63 | viewsAlpha_RearrangeFilters.png | 132.03 KB | kbk |
#63 | test_views_alpha_settings.png | 210.18 KB | kbk |
#57 | or-and-group-by-in-the-same-view.png | 48.54 KB | merlinofchaos |
#52 | 118672-allow-or-in-UI.patch | 30.85 KB | merlinofchaos |
#50 | views_or_ui-bug.png | 45.36 KB | dagmar |
Comments
Comment #1
merlinofchaos CreditAttribution: merlinofchaos commentedI 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.
Comment #2
merlinofchaos CreditAttribution: merlinofchaos commentedThis plan got punted to Views 2.1 -- asisigning ot myself so I can find this for marking duplicates.
Comment #3
plachI am very interested in this feature, if you need help of any kind I'll be glad to jump in.
Comment #4
solimeno CreditAttribution: solimeno commentedI 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?
Comment #5
gpk CreditAttribution: gpk commentedIs 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!
Comment #6
scottrigbyThis will be such a killer feature ;)
After IRC with merlinofchaos, i wanted to note two things:
Comment #7
gpk CreditAttribution: gpk commentedThanks scottrigby.
FWIW, adding a link to views_or, which looks very promising: http://drupal.org/project/views_or.
Comment #8
Flying Drupalist CreditAttribution: Flying Drupalist commentedIs this coming soon? Should we use views or at this stage?
Comment #9
sidharth_k CreditAttribution: sidharth_k commented+1
Comment #10
emi_bcn CreditAttribution: emi_bcn commented+1
Comment #11
arojoal CreditAttribution: arojoal commented+1
Comment #12
enboig CreditAttribution: enboig commented+1
Comment #13
Apollo610 CreditAttribution: Apollo610 commented+1 here. Using views_or which is fantastic, just want to keep updated for when the migration to Views2 takes place.
Comment #14
drewish CreditAttribution: drewish commentedsubscribing
Comment #15
Drinkie CreditAttribution: Drinkie commentedSubscribed.
Comment #16
rapsli CreditAttribution: rapsli commentedguess I'm going to try the views or,...
Comment #17
drupaloo-1 CreditAttribution: drupaloo-1 commentedSubscribed.
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.
Comment #18
dawehneradding a tag und pumping to 3.x
Comment #19
HnLn CreditAttribution: HnLn commentedsubscribe
Comment #20
sagannotcarl CreditAttribution: sagannotcarl commentedView_or just broke for me with the new dev version. I'm excited to see this making it's way into views proper.
Comment #21
gagarine CreditAttribution: gagarine commentedtrack
Comment #22
jdwfly CreditAttribution: jdwfly commented+1 using views or currently but looking for when it will be included into views
Comment #23
AndyF CreditAttribution: AndyF commented+1
Comment #24
sinasalek CreditAttribution: sinasalek commented+1 Subscribe
Comment #25
BenK CreditAttribution: BenK commented+1 subscribing
Comment #26
MurzSubscribe, +1
Comment #27
AnybodyAnything new about it?
Comment #28
mysterlune CreditAttribution: mysterlune commentedSubscribing
Comment #29
neil.brown CreditAttribution: neil.brown commentedSubscribe.
Comment #30
Royce-1 CreditAttribution: Royce-1 commentedFor Shizzle
Comment #31
AntiNSA CreditAttribution: AntiNSA commentedsubscribe
Comment #32
TwoMice CreditAttribution: TwoMice commented+1
Comment #33
lonelyrobot CreditAttribution: lonelyrobot commentedsubscribing.
Comment #34
asak CreditAttribution: asak commentedsubscribing....
Comment #35
dnotes CreditAttribution: dnotes commentedsubscribe
Comment #36
cels CreditAttribution: cels commentedsubscribe
Comment #37
4drian CreditAttribution: 4drian commentedsubscribe
Comment #38
dagmarI 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.
Comment #39
AndyF CreditAttribution: AndyF commentedHi 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
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...
Does that make sense?
Some other possible ideas:
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.
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.
Comment #40
chrisroditis CreditAttribution: chrisroditis commentedsubscribe
Comment #41
dagmarYes, 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.
Comment #42
AndyF CreditAttribution: AndyF commentedI'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.
Comment #43
dagmarThis is the new UI with the discussed changes.
Comment #44
izmeez CreditAttribution: izmeez commentedsubscribe
Comment #45
merlinofchaos CreditAttribution: merlinofchaos commentedI 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.
Comment #46
tomgf CreditAttribution: tomgf commentedSubscribe
Comment #47
jrabeemer CreditAttribution: jrabeemer commentedSub
Comment #48
merlinofchaos CreditAttribution: merlinofchaos commentedHere'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.
Comment #49
merlinofchaos CreditAttribution: merlinofchaos commentedCaching 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.
Comment #50
dagmarif you have an exposed filter alone in one group, views displays a sql error.
Comment #51
dagmarI 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:
Small indentation bug.
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.
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:
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.
Comment #52
merlinofchaos CreditAttribution: merlinofchaos commentedOk, 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.
Comment #53
bleen CreditAttribution: bleen commentedsuperscribe
Comment #54
joelstein CreditAttribution: joelstein commentedThe 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.
Comment #55
MBroberg CreditAttribution: MBroberg commentedsubscribing
Comment #56
merlinofchaos CreditAttribution: merlinofchaos commentedI 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.
Comment #57
merlinofchaos CreditAttribution: merlinofchaos commentedHere's a screenshot:
Comment #58
dawehner@merlinofchaos
Could you attach the patch, please? I guess you didn't commit the patch to d7 :)
Is there something like a rule? Mutliple add_wheres, for example?
Comment #60
roball CreditAttribution: roball commentedThis 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?
Comment #61
gpk CreditAttribution: gpk commented@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.
Comment #62
roball CreditAttribution: roball commentedThanks, 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 :-)
Comment #63
kbk CreditAttribution: kbk commented[Edit: In response to comment #65, I've published this comment separately. Looks like the attachments are staying though!]
Comment #64
aleksey.tk CreditAttribution: aleksey.tk commentedsubscribing
Comment #65
merlinofchaos CreditAttribution: merlinofchaos commented#63 should be posted as a new issue, I think. Please?
Comment #66
dawehner@kbk
Clear the theme registry, and it will be fine. And if you don't know how, search for it :)
Comment #67
maomaohuhu CreditAttribution: maomaohuhu commentedsubscribing - sorry cannot help out.
thanks the excellent feature coming.
Comment #68
webel CreditAttribution: webel commentedSubscribe
Comment #69
kenorb CreditAttribution: kenorb commentedlol, thanks
Comment #70
not_Dries_Buytaert CreditAttribution: not_Dries_Buytaert commentedSubscribing
Comment #71
HnLn CreditAttribution: HnLn commentedI 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
Comment #72
dawehnerCan 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
Comment #73
HnLn CreditAttribution: HnLn commentedYep noticed it 2 late :-), done on http://drupal.org/node/935984