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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Julian HansenCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
ste5anSenior 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 PrewCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.