Adding sql ORing capability with filters

sarvab - February 13, 2007 - 15:56

Project:	Views
Version:	6.x-3.x-dev
Component:	Code
Category:	feature request
Priority:	normal
Assigned:	merlinofchaos
Status:	active
Issue tags:	views 3.x roadmap

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?

» Login or register to post comments

#1

merlinofchaos - February 14, 2007 - 05:23

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

merlinofchaos - May 1, 2008 - 04:30

Assigned to:

Anonymous

» merlinofchaos

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

#3

plach - August 1, 2008 - 09:31

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

#4

solimeno - September 23, 2008 - 17:33

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?

Attachment	Size
table.jpg	47.53 KB

#5

gpk - April 13, 2009 - 12:20

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

scottrigby - April 13, 2009 - 17:39

This will be such a killer feature ;)

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

This module (temporary measure, hopefully folded into Views @ some point): views_or
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

#7

gpk - April 13, 2009 - 18:26

Thanks scottrigby.

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

#8

Flying Drupalist - April 16, 2009 - 03:08

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

#9

sidharth_k - May 20, 2009 - 18:33

#10

emi_bcn - May 23, 2009 - 20:02

#11

arojoal - May 24, 2009 - 06:19

#12

enboig - May 28, 2009 - 08:57

#13

Apollo610 - May 28, 2009 - 20:25

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

#14

drewish - June 2, 2009 - 21:05

subscribing

#15

Drinkie - June 12, 2009 - 15:49

Subscribed.

#16

rapsli - June 18, 2009 - 13:15

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

#17

drupaloo - July 4, 2009 - 20:06

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

dereine - July 4, 2009 - 19:45

Version:

6.x-2.x-dev

» 6.x-3.x-dev

adding a tag und pumping to 3.x

#19

HnLn - August 11, 2009 - 20:43

#20

sagannotcarl - August 20, 2009 - 18:07

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

gagarine - September 9, 2009 - 17:59

track

#22

jdwfly - September 9, 2009 - 21:08

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

#23

AndyF - September 19, 2009 - 11:23

#24

sinasalek - September 19, 2009 - 20:32

+1 Subscribe

#25

BenK - September 28, 2009 - 03:00

+1 subscribing

#26

Murz - September 28, 2009 - 11:55

Subscribe, +1

#27

Anybody - September 30, 2009 - 17:49

Anything new about it?

#28

mysterlune - October 5, 2009 - 18:10

Subscribing

#29

neil.brown - October 6, 2009 - 11:55

Subscribe.

#30

divinevette - October 8, 2009 - 17:53

For Shizzle

#31

AntiNSA - October 9, 2009 - 17:07

#32

allenshaw - October 11, 2009 - 18:25

#33

lonelyrobot - October 12, 2009 - 16:36

subscribing.

#34

asak - October 19, 2009 - 21:26

subscribing....

#35

davidhunt - November 3, 2009 - 20:32

#36

cels - November 13, 2009 - 10:09

#37

4drian - November 18, 2009 - 03:07

#38

dagmar - November 22, 2009 - 19:24

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.

Attachment	Size
views-sql-or.png	63.7 KB

#39

AndyF - November 25, 2009 - 11:32

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.

#40

christopher_skauss - November 25, 2009 - 13:27

#41

dagmar - November 25, 2009 - 14:11

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.

#42

AndyF - November 27, 2009 - 08:37

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

dagmar - November 28, 2009 - 16:53

This is the new UI with the discussed changes.

Attachment	Size
views-or-2.png	82.38 KB

#44

izmeez - November 29, 2009 - 20:15

#45

merlinofchaos - November 30, 2009 - 22:01

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.

Adding sql ORing capability with filters

Jump to:

User login

Contributor links