Web-Based SQL Query Builder

Question

I've built a simple web-based query builder using a series of dropdown lists such that the only typed input is the final value field.

In the example above simply adding the filters consecutively would not produce the desired result set, each of the "OR" clauses should be grouped together.

I'm struggling to come up with a way of providing the user with a simple interface to define/display/edit groups. I don't think it's possible to infer filter groups (using the current interface) based solely on the order of AND/OR filters. Is it?

Answer 1

You can have a look at the Gestalt principles of grouping to get ideas. Following these principles could mean using, among others:

• Identation as mentioned by @ethrbunny to give a sense of nested queries,

• Using the <hr> to group conditions (i.e. remove from each and every row, and include only where a group starts and ends),

• A special row which would include just an AND or an OR could indicate where a group starts. That would suppose you give the user to "add condition" or "add group".
• Allow saving queries, so that a query can be set as a condition in another query, thus allowing the user to effectively use a pipeline of subqueries.

Other similar ideas might exist. Hope that helps! :)

Answer 2

Are you free to redesign/change the interface?

If so, have you considered separating the control options into predefined logical groups? If you isolate the most common queries and optimize the UI for those, you should be able to simplify things a bit.

This way you could split the filters into two groups, positive (include) and negative (not including):

Show products with [IDs]: 40, 88, 102, 144... (Represented by a single row for simplicity) Show products with [Title] [equal to, containing, not containing]: "Foobar" Exclude products with [description] [equal to, containing, not containing] "Test" Exclude products with [IDs]: 9, 15, 111

[ ] denotes a drop-down selection

The logic/query on the back end would look something like:

SELECT * FROM products WHERE (productID IN('40, 88, 102, 144') OR productTitle = 'Foobar') AND (productDescription != 'Test' OR productID NOT IN('9, 15, 111')

You are trading the ability to create finely tuned filters/queries for simplicity. In most cases this is preferred when the users of the tool doesn't possess the knowledge of how the filters interact with each other.

Answer 3

How about if you had some kind of indentation to indicate linked groups? Once they were indented you could select them and mark "AND/OR" for the block.