Query Builder - should I use AND/OR ?

I'm building a "query builder". User selects columns, then "conditions" which is the "where" clause.

Our users aren't technical.

1. My manager says to make it look like what TFS does....  AND, OR and then maybe parenthesis to see what conditions should be in parenthesis and grouped together.

This is TFS query builder

tfs.png

2. I was researching this  and came across this  Query Builder Example - Demo

Please look at the "Condition" section. It has "all", "any", "none" and "not all".  No And/Or and parenthesis and grouping the AND/OR together.

example
I want to do the "all", "any", none" and "not all". Much simpler than AND/Or and parenthesis... too complicated.

How would you guys do it? how can I explain this to my manager that "all", "any", "none" and "not all" is simpler? how can I convince him? Other examples I can look at?
LVL 8
CamilliaAsked:
Who is Participating?
 
Julian HansenConnect With a Mentor Commented:
You are facing the age old IT problem of trying to make things that computer scientists understand easy for lay people. The more power you give users to do different things - the more ways they will find to fubar it.

That is why you need to look at the use case. How often will users REALLY want to do an (A AND B AND C) OR (D AND E) OR ... given that most users won't even understand what it is they are doing. Your average user is not going to have the foggiest notion that the order of operators has any effect - 9/10 you would have lost them at the word operator.

So, the question is - how can you build the interface to do what you know the user wants without providing the user with too many options to completely mess things up.

The Any / All approach is the most sensible because even the most IT naive user can understand that (if they don't they have no business sitting in front of a computer).

Unless there is an immovable business case for the more complex scenario of (...AND...) OR (... AND ...) then ignore it for now.
1
 
ste5anSenior DeveloperCommented:
Well, normal users do in most cases AND only and some times OR only. But it strongly depends on the use-case. Who's working on what kind of data?

ALL is AND and ANY is OR.
0
 
Julian HansenCommented:
They are just hiding the parenthesis from you but as Ste5an has already pointed out you can think of AND as ALL and ANY as OR

The simplest solution is to ask for conditions and then the ANY / ALL.

It gets complicated when you want something like

WHERE (A AND B AND C) OR (D AND E AND F)

Open in new window


Do-able but needs a bit more coding.

Personally I would do the simple one first.

The problem with your original image is that you are not taking precedence into account. AND has higher precedence than OR

So
WHERE A AND B OR C

Open in new window

Is actually
WHERE (A AND B) OR C

Open in new window

But the user might want
WHERE A AND (B OR C)

Open in new window


In this case OR is usually used to join one or more AND clauses together - so you could build an interface that creates groups of conditions - WHERE it is assumed there is an OR between each group and an AND between the items within each group.
1
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
CamilliaAuthor Commented:
Who's working on what kind of data?
Project managers and finance people want to run report to look at their lease history, who has leased what and when

The problem with your original image is that you are not taking precedence into account

Exactly! an average user doesn't know the precedence so my manager said something like having parenthesis and groupings... like TFS...with a checkbox next to AND and OR.

you can think of AND as ALL and ANY as OR
I agree. Just have to put an email together and convince my manager that having parenthesis and building all this AND / OR is too complicated
0
 
Bill PrewCommented:
I have often found indentation and graphical representations help a lot when trying to bridge the gap between end users and technical staff.  In this area, take a look at these metaphors, I find them fairly easy to grasp, even putting on my user hat.  

q1.png
q2.png
q3.png

»bp
0
 
ste5anSenior DeveloperCommented:
@Bill: can you show us your user hat?

Many of mine users would fail even with these graphical interfaces.
Ok, they also fail at the Excel style AND(OR(AND(a=1;b=2);c=3);d=4) ;)
0
 
Bill PrewCommented:
@Bill: can you show us your user hat?

hat.jpg

»bp
1
 
CamilliaAuthor Commented:
Bill, that user interface is not user friendly for an average project manager or an assistant :)

TFS is different... developers or someone in IT creates the queries.

I like Julian and ste5an's  explanation. I'll build on that with my manager.
0
 
ste5anConnect With a Mentor Senior DeveloperCommented:
btw, when you look at the different graphical possibilities, then the way to go is easy to determine:

Start without parentheses. You can add them later. Cause the inner blocks are the re-useable part. In (kind of) BNF.

The simple case:

<simpleCondition> := <column> <conditionOperator> <value>
<conditions> := <condition>  [<conditions>]
<view> := <viewOperator> <conditions>

<conditionOperator> := LT | GT | EQ (what ever operator makes sense on your data)
<viewOperator> := AND | OR | ALL | ANY

Getting complex:
<complexConditions> := <viewOperator> <conditions>|<view>

where <simpleCondition> and <complexCondition> are compatible to <condition>. So is <complexConditions> and <conditions>.
1
 
Bill PrewConnect With a Mentor Commented:
And at the risk of adding work to the effort, perhaps you mock up a couple of different approaches, and let the actual end users have a say in how it is implemented and presented to them...


»bp
0
 
CamilliaAuthor Commented:
Yes, mocking up and starting with something simple.
0
All Courses

From novice to tech pro — start learning today.