Reputation: 16352

Does anyone have an example of a User Interface for creating a SQL Where clause?

I'm having trouble trying to map nested conditions onto an intuitive interface.

eg. How would you represent ((Condition1 AND Condition2) OR (Condition1 AND Condition5)) AND Condition4

Upvotes: 5

Answers (11)

Bryan Oakley

Reputation: 386010

Here's a screenshot of prototype I did for a linux app a few years ago. You could click on the +/- icons to add rows to a group and click on the "add new..." and "remove last..." buttons to remove the bottom-most group.

Above each group was a couple of menubuttons that had the choices of "AND items that match..." / "OR items that match..." (except for the first group which varied slightly), and "ANY of the following" / "ALL of the following". Each row was type-aware, so if you selected a string for the variable the conditions would be "IS", "IS NOT", "BEGINS WITH" and so on. For integers you would get "IS", "GREATER THAN", etc, and for dates "ON", "BEFORE", "ON OR BEFORE" and so on.

Where you see the word "or" before the second and third row of the first group, that would be "or" if "ANY of the following" was selected, and "and" if "ALL of the following:" was selected to reinforce the choice and make it easier to "read" the dialog.

It wouldn't let you do any conceivable query but I think it covered about 90% of what an average user would want to do and did it in what I thought was a fairly usable way.

_{(source: clearlight.com)}

Upvotes: 7

Constantin

Reputation: 28164

Some people would argue this is as intuitive as it gets.

logic

Upvotes: 3

Joel Anair

Reputation: 13962

Check out any of the live demos for EasyQuery:

http://devtools.korzh.com/easyquery/livedemos/

It's commercial software but the "Conditions" section allows you to add single conditions or groups of conditions, which removes a lot of the complexity from clauses with multiple ANDs and/or ORs. It's very well done and easy to use.

(I'm not affiliated with EasyQuery, just impressed with their query builder.)

Upvotes: 1

NeuroSys

Reputation: 41

Microsoft SQL Server has a interface like that, I have used it in SQL Server 2000 but I bet it's in 2005 express too so you can take a look if you want.

Upvotes: 1

ramanman

Reputation: 873

It is kind of specific to its domain, but f-spot has a nice way of doing this. It is photo management software, and if you click on one of the tags to find pictures by tag, it displays a bar across the top of your search results. You then can drag and drop tags onto that bar, and right click to select negation, and can drag the tags around in the bar to group into and and or clauses. I'm not sure how well that scales for tons of tags (or non-enumerated conditions), but it is dead simple to figure out and nicely interactive.

Upvotes: 0

GSerg

Reputation: 78185

TheBat! has the best interface for that I personally hit on. (Used for mail sort rules.)

It goes:

Source folder is not one of \\Google\Inbox

AND

Subject ends with "new comment"

OR Subject match "some string"

Upvotes: 2

Nescio

Reputation: 28423

I used to work on a system where we aligned boolean logic similar to the below.

The right columns (Inner) and (Outer) provide two levels of logic.

Variable       Inner     Outer
Condition1     And
Condition2               Or
Condition1     And
Condition5               And
Condition4               

Or more optimized...

Condition4               And
Condition1               And       
Condition2               Or
Condition5

Upvotes: 0

Leigh Caldwell

Reputation: 11066

If this is important enough to spend a lot of time on, I'd consider using Venn diagrams. The visualisation will represent the result sets rather than the query terms. So to demonstrate AND you would show two circles representing the results, and highlight the overlap between them (intersection).

To demonstrate OR you would show the two circles and highlight the union of both.

Then to show the whole multi-part query you can either show five circles with some combination of union and intersection, or else combine each parenthesis and then hide the detail, making the results into a new circle to combine with other elements. Lots of drag-and-drop here, and dynamic resizing of subclauses for clarity.

To make this intuitive and easy to use would take quite some work, but for some applications it would be a really powerful interface.

Upvotes: 1

Bahadır Yağan

Reputation: 5967

You can check how MS Access does it. I won't call it intuitive but it is simple.

Upvotes: 0

JustinD

Reputation: 1676

Assuming .NET, I'd go with a DataGridView to store each condition and to assign each an ID as it is created and have a textbox that allows you enter the particular query conditions.

You could then, once all conditions are written, allow for combining 2 at a time with either an AND or an OR and then displaying the resulting query for verification

Condition1

Condition2

Condition3

Condition4

Condition5

in your case, once you add each one to your dataset and populate the DataGridView, you would then do (i imagine a form with 3 dropdown boxes, top one and bottom one allow for conditions or "compounds" and the middle dropdown is AND/OR only:

Condition1 AND Condition2 = "Compound1"

Condition1 AND Condition5 = "Compound2"

Compound1 OR Compound2 = "Compound3"

Compound3 AND Condition4 = "Compound4"

and compound4 is your final query

make sense?

Upvotes: 2

Austin Salonen

Reputation: 50225

The best interface I've seen for this was a home-grown control that drew a tree to clearly show the order of operations. I've never seen a third-party control that did this but I haven't looked for one either.

Upvotes: 0

Does anyone have an example of a User Interface for creating a SQL Where clause?

Answers (11)

Related Questions