How can I implement user-friendly boolean logic in a web form GUI?

Question

Currently I have a web application where a user can use dropdown lists to generate SQL SELECT statements like so:

Column Select Dropdown | Operator Dropdown (= != > < <= >=) | Value select dropdown

The user can do this multiple times, and the "filters" are currently all ANDed together.

I want to add the possibility of creating OR statements. I could very easily add ORs in the case where the columns are the same, but what about complex logic statements like

((A OR B OR C) AND (D OR E)) OR (F AND G)?

How can I let users create such statements in a user-friendly way?

EDIT: To specify, user-friendly for a general audience. Currently I work with developers that occasionally hand-code SQL queries for a non-technical client that needs specific information from our database. The goal is that this web app will remove the need for us to hand-code them by giving the client an easy-to-use tool to do it themselves.

EDIT2: Currently the app is not being used by end users. The only data I have as to its use are previous handwritten SQL queries and thus the kind of queries the client asks for. Given that I could simplify it (e.g. limit the users' ability to generate queries to the kinds of queries they tend to ask for), but I want to see if anybody has experience communicating boolean logic simply and completely in GUIs.

Thank you for your time.

Answer 1

Mac OS X offers very nice GUI widgets for doing exactly this type of thing. You can model your GUI after this type of layout/interaction. If you're not familiar with the UI, here's a screenshot of the Finder's built-in search function.

Answer 2

Another option is something like SQL Server Management Studio query builder interface - several rows and columns, where rows represents ANDs, and columns ORs (or vice versa, I don't remember).

You can do real-time update of resulting query to help users (just like SQL Server updates the resulting SQL).

Answer 3

This is difficult to represent even in a WinForms app.

What you need to do is implement the concept of a condition group, which consists of one or more statements, and a conditional operator.

The best implementation of this I've see was from GameSpy server filtering -- I just tried to search to find a screenshot, but I came up empty (does that program still exist?). From what I recall, they did something like this:

(
    Condition 1
) OPERATOR
(
    Condition 2
) OPERATOR
(
    (
        Condition 3
    ) OPERATOR
    (
        Condition 4
    )
)

Answer 4

When you need to handle ( (A or B) and C) or (D or E or F), you're working with a tree-like data structure. In my experience, there's no easy way to represent decision trees to users in a "pretty" or "intuitive" way. Its doubly hard in ASP.NET webforms.

However, one tried and true approach is the following: single textbox accepting a where clause. Trust me, the single-input approach really is the most simple and intuitive user interface, and it also has the advantage* of allowing rapid input/modification of query filters.

** Another advantage, from the technical side, is being able write your own lexer/parser and AST. How often do you get to do that in a basic crud app :)*

You're already going to be training your users how to use your ad hoc query engine, you may as well train them that typing (account.Balance < -2000 and account.Type == 'Checking') OR (account.Number = 123456) returns exactly what it says it returns.

If you go with this approach, provide the user with a dropdown list of available columns, so that double-clicking on an item inserts the item into the textbox at the cursor location.

Answer 5

When I see a problem like this, I can't help but thinking about implementing it as a stack, similar to how RPN would solve this problem.

The problem here is that it doesn't seem too intuitive

Sample UI: ([Button] <a text box for user input> {list}

Value : < > [Push] [And] [Or]

Stack {

} (HP RPN calculators put the stack above the editing area)

So, if I wanted to write the expression ((A and B) or (C and D)), I would do this: A [push] (stack would contain "A") B [push] (stack would contain "B", "A") [and] (stack would contain "(A and B)") C [push] (stack would contain "C", "(A and B)") D [push] (stack would contain "D", "C", "(A and B)") [and] (stack would contain "(C and D)", "(A and B)") [or] (stack would contain "((A and B) or (C and D)")

if you wanted to add other operators, and there weren't too many, you could just add additional buttons, or make a separate textbox for the operator

Value: < > [Push] Operator < > [Combine]

If you wanted to support unary operators, you'd need to keep track of whether it's a prefix or postfix operator, or just assume prefix (the boolean unary operator "not" is generally prefix). Ternary operators generally have two infix designators, so there's more complexity if you want to support them. Some binary (and n-ary)operators have a prefix, infix, and suffix component "CallMethod(A,B)" So it really comes down to how complex you want to make it.

Just one idea.

Answer 6

How about something like the Yahoo Pipes interface? I haven't used them in a while, but I seem to recall they would allow custom boolean logic. They are also heavily GUI based, and would graphically represent the tree-like structure.

The diagram below shows a (non-working) example of what your expression may look like.

I've not quite got the hang of this posting images malarkey:
Link to bigger image