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

Cur开发者_StackOverflow中文版rently 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.

There is a jquery plugin to do this, called QueryBuilder, which do this in an interesting way : http://mistic100.github.io/jQuery-QueryBuilder/

Apple seems to have found a way to design a GUI for nested boolean expressions: see the accepted answer on UX.stackexchange.

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.

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
    )
)

I honestly don't see business value in writing the custom "where", "select", "from" or any other SQL commands proxies. Especially, in this particular context (DB access and custom on the fly query) the client opens security gates of hell.

Letting "dummies" (who I'd assume are not capable of using the regular SQL tools) compose "intuitive" query is a disaster waiting to happen. I guess BJ's club credit card info bust of 2003 or 2004 was pretty close to this in spirit. I guess (and it is only a guess!!!) some big marketing boss said "We will save the credit card stripe info so we can leverage that info later." "Do you want only publicly available info in one table and PII statistically bucketed" - asked the developer..... "No, we don't yet know how we want to use that information, develop us a tool to query it in a CUSTOM WAY....." was the first stepping stone on the path to the disaster. :(

Meanwhile, there definitely are places when the UI is needed for composing/parsing expressions (security policy analysis tools, boolean/switch algebra research, etc.) I believe that the best UI is yet to be created (always:)), but if it were created, I'd envision it having possibility to:

1. Parse the expression (should be trivial as one can build parsing tree as user adds expression chunks piece by piece)
2. Show the expression in a parsing tree form (that should be fun drawing it).
3. Show true/false table for the BF presented by the expression
4. Draw BF hypercube (especially valuable for "monotone-like" functions)
5. Create a Karnaugh Map with topological linking (good luck with high dimensional expressions though)
6. Dynamically generate Venn diagram for the expression.
7. Highlight non-essential variables or "expression chunks".
8. Use McCluskey or Petrick method of boolean expression minimization.

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.

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.

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).

Property Filter Form (click to see an image of the form)

I had a similar task, filtering files based on property values. The users are mechanical engineers, so not exactly a "general audience."

In the form, the user creates a set of conditions. Each is listed separately and assigned a letter (A, B, ...).

A default boolean expression (A AND B AND ...) is automatically generated. However, the user can optionally change it as needed, e.g. A AND (B OR C).

It has been in production for over a year, with no major confusion or complaints so far.