130

I'm interested to know how people have, or would construct an interface which simplifies constructing logical boolean conditions? (Yes, it's a database query builder) In my mind a bad interface has them typing lots of AND, NOT IN, OR keywords etc. Or else has a million combo boxes.

I'm wondering if anyone has a good example I could glean ideas from? Or some suggestions?

At the moment I'm considering a split screen with a grid for composing the query and a canvas to display a visual representation of the sets of conditions and how they overlap etc. Like a Venn Diagram. This still gives me a problem with the usability/intuitiveness of the controls on the grid.

EDIT: I'm very interested in ideas which simplify it for less technical users. However, ideas for a version of the UI for advanced users are very helpful as well.

  • 3
    Be careful of using the word 'intuitive'. In my experience there is no such thing. :) The 'best' solution for one person may be confusing for the next person. – Stewbob Sep 30 '10 at 22:32
  • 1
    @Stewbob See this question for discussion on the meaning of "intuitive": ui.stackexchange.com/questions/1282/… – Rahul Oct 3 '10 at 14:55
  • If it fits your situation, it can really help if the user can start with a set of examples. For instance, if I'm creating an e-mail filter, I could select a load of emails which should match it. From that it's a relatively simple task to suggest a starting point for the filter, which the user can fine-tune. You can also help the user in their editing process by showing live examples of which records match and which records don't. – Peter Dec 16 '14 at 11:08
  • 2
    reviewing the answers below, I think one of the keys is replacing the nomenclature of AND/OR with ALL/ANY. – Dvir Adler Oct 1 '15 at 19:07
  • 1
    Dvir, AND/OR is just the starting point. Moving aware from those terms is the essential first step. – Edward Williams Apr 7 '16 at 4:13

19 Answers 19

59

See also iTunes 9 or later. It adds the ability to do "nested" AND / OR expressions, akin to how a programmer would do it with parenthesis:

alt text

  • 1
    Can AND and OR be combined with this interface? Or is it either "all ANDs" or "all ORs"? – unor Nov 21 '12 at 10:01
  • 4
    iTunes 11 has hidden the nested boolean logic somewhat; you must now Option-click the plus button. – James Emerton Mar 11 '13 at 21:42
  • 7
    Although the UI is capable, I dont think of it as particular intuitive. Granted this is a very difficult requirement to make intuitive. – Glen Lipka Feb 15 '16 at 18:24
  • 6
    We implemented this on our solution about 2 years ago. Even though it does work, the feedbacks we get is that it's a total hell to use. Still looking for an alternative... – Florian F. Mar 8 '16 at 21:20
  • @FlorianF. did you ever come up with a better alternative? – Christopher Padfield Oct 16 at 16:06
45

The main problem non-technical users have with Boolean logic is understanding the difference between AND and OR because it doesn’t always correspond to natural language (e.g., “show me orders from New York and New Jersey” almost certainly means Location = NY OR Location = NJ). Often users tend to interpret "or" to be an an exclusive OR. Then there’s the issue of losing track of nested parentheses.

One solution that avoids both of these issues is to represent the logic graphically with a plumbing or electrical metaphor. There have been a couple lines of work taking this approach:

Shneiderman, B (1991). Visual User Interfaces for Information Exploration. Proceedings of the 54th Annual Meeting of The American Society for Information Sciences, 28, 379-384.

Murray NS, Paton NW, Goble CA, Bryce J (2000) Kaleidoquery—a flow-based visual language and its evaluation. Journal of Visual Languages & Computing, 11(2), 151-189.

Query builders are one of the few places where it makes sense to have separate Basic and Advanced modes. You’ll probably find that 90% of your users' queries fit only a few patterns (e.g., “Customers with name beginning with Xxxx,” “Accounts I’m responsible for with outstanding bills,” “Orders made between date a and b”). It makes sense to have these as canned or semi-canned queries that can be selected and specified easily while putting something like Kaleidoquery under Advanced for the rarer ad hoc querying.

43

This one tested well with both the technical and non-technical users and can generate pretty much any possible database query...

db query builder

The benefits are that it's very clear and a user can drag and drop (or delete) any expression or group of expressions in the tree.

The down side is how much space it consumes.

  • 6
    I like this. Is it online somewhere to view? Either way, thanks for the inspiration. – John Pancoast Aug 24 '15 at 16:47
  • 2
    This is a great solution- I came up with something similar but it still felt unclear- the colour coding really finish it off! – Shawson May 6 '16 at 15:18
  • 1
    @JohnPancoast - 2 years late but it looks like the UI is from SolarWinds (it lets you define alerts on Nodes) – user635090 Jun 3 '17 at 23:49
  • @user635090 Two years late to notice your reply :) Apparently not getting notified of SO notifications. Thank you. – John Pancoast Oct 1 at 7:58
13

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

Jquery QueryBuilder Screenshot

  • 1
    Just because plugins exist, that doesn't mean they're any good. From a UX perspective, is this good? Is it intuitive? WHY is / isn't it? This isn't really an answer to the question, it's just a screenshot and a link with no description or reasoning. – JonW Mar 22 '16 at 14:44
  • 5
    @JonW This is an answer to the question I'm wondering if anyone has a good example I could glean ideas from? – dryobs Mar 23 '16 at 11:38
  • Examples as parts of answers are fine, but you still need to explain them - state why they are good. – JonW Mar 23 '16 at 11:41
  • Agreed, this is a great option. – plainclothes Apr 6 '16 at 4:21
10

I like the way Apple Mail's rules work:

screenshot

  • I like that interface too. It gets around the problem @Michael mentioned: people who don't know boolean logic by definition don't understand what AND and OR mean. – Patrick McElhaney Sep 30 '10 at 12:50
  • 2
    I think Apple avoids boolean logic quite well by using "any" (:= or) and "all" (:= and). – LennyUserExperience Oct 1 '10 at 7:24
  • 3
    However, it only allows very simple queries -- if you want to do A AND (B OR C), you can't. Which is odd, because it would be rather useful, and Apple do allow you to construct such queries in iTunes and Finder. – calum_b Jun 17 '11 at 20:30
9

It depends on the level of sophistication of your users. We have something similar in the current version of the interface have left out grouping and fixed the conjunction between to terms to OR. Each term can be negated. Most users are fine with that kind of querying and most of them would not be able to formulate more advanced queries correctly. We are now implementing a two step process in which the result of a query can be used to limit the population in a following query (still leaving out explicit grouping in the UI).

The UI for this is a basic list of constraints that can be added, removed, activated and deactivated and negated. This has been sufficient for most queries that users want to create. We had a design for a new version that was based on a dataflow concept (taking strong inspiration from vistrails, see below) but that got never executed. Vistrails is a product that can be used to created VTK pipelines. If you want to go the route of lots of pieces of UI, dataflow works for formulating queries, but also works for formulating output formatting.

Look for inspiration in

  • Edited question to specify my audience type better. Answer: both, ideally. Thanks for the examples, had forgotten about yahoo pipes. – Edward Williams Sep 30 '10 at 10:57
9

There's quite a few good ideas/references here, especially to some existing approaches. Often, though not always, Apple's approach is a good place to start - but perhaps in your case it may not be. I get the impression (though you haven't actually said it) that you're working with an awful lot of data comprised of many many fields/variables.

I agree that trying to find a way to simplify it for less technical users is a good idea, so long as you expect to have less technical users using the system. Otherwise developing a less complicated interface may be a lot of work for little gain. I also like the idea of a Venn diagram - would be interesting to see how that pans out.

However, in terms of actual suggestions on how to simplify this, another approach would be to just create some sort of "Wizard" process that steps the user through the process with a combination of natural language and a familiar 'web' look and feel. Below is a mockup of how this could work, using an example of a motor vehicle database:

enter image description here

The above shows how Step 1 could work, giving users a number of options they can select by ticking the relevant checkbox. They can select one or more checkboxes as necessary (or maybe not any?!). If they select a checkbox for an option that requires further info then the relevant words are hyperlinked. Clicking on the hyperlinked words would present the user with something like the example below:

enter image description here

So, the example above shows what a user may see if they've selected the "Vehicle is produced by particular manufacturers" checkbox and then clicked on the hyperlinked text to select the manufacturer(s) they want included in the search results. Of course your approach may differ depending on whether they should see a free text field, a drop-down list of options, etc.

Now, to deal with any "exceptions" to the search criteria, you would basically recreate the first window again, but with different wording, like:

enter image description here

So, after selecting the fuel exception above, the user clicks on the hyperlinked words "particular fuel" to choose the exception as below:

enter image description here

Once again, this could be a drop-down list, radio buttons, etc depending on what best suits the condition. They would also go through the same process to select the country they didn't want the vehicle to be manufactured in.

Of course, I see this "Wizard" approach as being for less technical users. You would also offer an "Advanced" option for users comfortable with a more complicated approach that may be streamlined compared to the Wizard approach.

ADDENDUM

Okay, this kept me up last night. I actually see this Wizard approach as being quite a good way to go, so thought it worth coming back to improve on my answer as it may help someone else if not you.

I've updated the mockup images above and wanted to expand on your idea of using a split screen.

Originally I thought that after the last step was complete, then something like your Venn diagram idea could be used to visually indicate what the user has chosen. But then I thought about my original assertion that there should also be a way for the user to go back and forth to amend their criteria. So now I'm thinking that using your split screen you could achieve both. Below is a new image to illustrate what I'm thinking:

Split screen view

So, the above is an example of what could appear in the second part of your split screen. As the user selects their criteria, this updates to indicate what their selections have been. This approach also uses the familiar web look and feel of highlighting their choices with hyperlinks and, if they wanted to, they could change their criteria by clicking on the hyperlinks within this screen, rather than having to go back and forth between screens to redo their steps. Of course, if they wanted to select a new condition, they would need to perhaps go back to the relevant step. But you get the idea.

The only other thing I'd like to mention is that without knowing the complexity of your data, you may need to refine this Wizard approach. My simple vehicle database only needs two steps, but a complicated one may need more steps. The point of this approach though, is not so much in the number of steps, but in the fact that the Wizard 'talks' people through the steps using natural language as much as possible.

Anyway, I hope this, along with the other answers provided, gives you some food for thought. And perhaps some others. This is a good topic and one which I think will be relevant to many users.

Good luck!

7

As opposed to re-using pivot tables as I previously answered, this is an experimental UI that I thought of to handle the repetitive need for writing AND or OR.

It relies on one element you must learn that ANDs are horizontal and ORs are vertical. It manages to deal with fairly complex Boolean logic though.

Overview

Assume that A, B, C, D, and E are Boolean expressions.

For testing the concept here is how you would draw the two different sides of a standard Boolean equivalence:

(A and B) or C === (A or C) and (B or C)

enter image description here

This extends to more complex queries:

((A and B and C) or D) and E

enter image description here

Actual UI

For this table of data:

enter image description here

The screen is split in two:

  1. A set of filters (effectively AND queries)
  2. A combination of the filters

You set the filters (these are just typed with basic conditional log) and then drag that across to 'merge' it with the full query.

enter image description here

After dragging the new set of filters across, the left-hand side returns to an unfiltered list and the right-hand side shows the 'merged' data set:

enter image description here

You should then be able to drag around and edit the expressions on the right-hand side - but that needs a lot more work.

  • Great idea - I had a similar thought. I think this is particularly suited to contexts where a lot of similar things are searched for. For instance in the interface I'm working on there are a lot of synonyms (something like (A or Ay) and (B or Be or Bee)) so OR as column/list feels intuitive. Do you have any examples of this in the wild? – Keith Apr 17 '18 at 7:32
  • @Keith No this is purely out of my head, I keep telling myself to get round to building it one day – icc97 Apr 17 '18 at 19:21
6

If your users are advanced enough to know the hierarchy of a query, any graphical interface you give them has to be fluid enough not to get in their way. I think an interface based on dragging and dropping elements to create an implicit hierarchy is ideal. Here's an extended annotated visual example of how a user might construct the query (A and B) or ((not C) or D):

Drop A onto panel.
+---+
| A |
+---+

Drop "and" after A.
+-------------------+
| +---+     +-----+ |
| | A | and | ... | |
| +---+     +-----+ |
+-------------------+

Drop B onto "...".
+-----------------+
| +---+     +---+ |
| | A | and | B | |
| +---+     +---+ |
+-----------------+

Drop "or" after "and".
+--------------------------------+
| +-----------------+            |
| | +---+     +---+ |    +-----+ |
| | | A | and | B | | or | ... | |
| | +---+     +---+ |    +-----+ |
| +-----------------+            |
+--------------------------------+

Drop C onto "...".
+------------------------------+
| +-----------------+          |
| | +---+     +---+ |    +---+ |
| | | A | and | B | | or | C | |
| | +---+     +---+ |    +---+ |
| +-----------------+          |
+------------------------------+

Drop "not" onto C.
+--------------------------------------+
| +-----------------+    +-----------+ |
| | +---+     +---+ |    |     +---+ | |
| | | A | and | B | | or | not | C | | |
| | +---+     +---+ |    |     +---+ | |
| +-----------------+    +-----------+ |
+--------------------------------------+

Drop "or" after "not C".
+-----------------------------------------------------+
|                        +--------------------------+ |
| +-----------------+    | +-----------+            | |
| | +---+     +---+ |    | |     +---+ |    +-----+ | |
| | | A | and | B | | or | | not | C | | or | ... | | |
| | +---+     +---+ |    | |     +---+ |    +-----+ | |
| +-----------------+    | +-----------+            | |
|                        +--------------------------+ |
+-----------------------------------------------------+

Drop D onto "...".
+---------------------------------------------------+
|                        +------------------------+ |
| +-----------------+    | +-----------+          | |
| | +---+     +---+ |    | |     +---+ |    +---+ | |
| | | A | and | B | | or | | not | C | | or | D | | |
| | +---+     +---+ |    | |     +---+ |    +---+ | |
| +-----------------+    | +-----------+          | |
|                        +------------------------+ |
+---------------------------------------------------+

Individual query elements (A, B, etc.) are constructed before being dropped into the panel, using combo boxes or whatever elements are necessary. Small margins and alternating colours could make this very readable, as well as display rules that make, for example, a chain of ors display at a single level:

+-------------------------+
| +---+    +---+    +---+ |
| | A | or | B | or | C | |
| +---+    +---+    +---+ |
+-------------------------+

Naturally, query elements could be expanded, collapsed, re-ordered, and edited after being dropped into the composing panel.

I'm not saying this is the easiest way to construct a system for this purpose. In fact, from a development standpoint, it's probably about as difficult as it can be. But it's the most efficient and intuitive thing I can come up with, and anyway it's basically just a clone of the Apple Mail rule UI but with more emphasis on hierarchy.

Hope this proves useful in your search for what's right.

  • 7
    If the users are smart enough to understand the difference between and and or in boolean logic, they sure are able to type or select `and' and 'or' in a control. Making them drag the word 'and' is very exhaustive. – GolezTrol Jun 9 '11 at 20:41
  • 3
    Example of this type of UI is implemented at datasift's query builder tool : dev.datasift.com/editor/getting-started/demo. – Nitin Jadhav Apr 2 '13 at 9:25
6

Microsoft Access had a reasonable attempt at a simple database query UI by producing a visual version of "Query by Example"

It has a more natural language that avoids need for nested UI, at the expense of occasionally slightly more redundant entries in lines.

mockup

download bmml source – Wireframes created with Balsamiq Mockups

6

It's an old question, but I thought I'd contribute in case anyone is interested. Although a lot of interesting answers have already been provided, I designed for our app the following:

enter image description here

Initially there's just the first group expression and one rule. Clicking on the 'Add condition' adds a new condition above it, while the 'Add group' adds a new group directly below it; additional groups from the parent group are siblings while you can create infinite nesting using the 'Add group' button from within a nested group.

On mobile devices the conditions stack, along with the 'remove' action button - which now changes its text to 'Remove condition'.

enter image description here

The design works well, looks good, is responsive and doesn't consume too much space either.

Added bonus: Above the group a single line shows the end produced condition, eg

[ Date greater than XXX AND (Name equal to Nick) ]

edit: In order to make it more approachable by non-technical people, I would follow what was said by others in this thread - follow Apple's path. Instead of AND/OR I would use ALL/ANY + context.

  • Nice. I really like it. My original question was searching for ways to use words other than boolean operators AND and OR as some people probably stumble in applying them. For us, it's how we think but for others, not so much. – Edward Williams Apr 26 '17 at 2:39
  • As to the "old question" remark, yes it is. However, I'm really surprised at how popular my question has been. It's nice seeing the attention it still gets, six or so years on. – Edward Williams Apr 26 '17 at 2:48
  • @EdwardWilliams boolean operations are usually not meant for 'average folk' but rather for advanced users. Average users would be better off with simpler filters but more work to apply them. – scooterlord Apr 26 '17 at 6:02
  • 1
    It's one of the factors of the question, to make it approachable and work in a user expected way. – Edward Williams Apr 26 '17 at 6:16
  • 1
    @EdwardWilliams I updated the answer to reflect this factor. Tbh there are some things that cannot be really 'simplified' for all people or all interfaces - you just have to compromise. My day job is to design complex dashboards and interfaces and many times I have to answer the question of where I should make something 'simple and easy to understand' sacrificing speed, or the other way around which is 'spend time to learn and digest an interface' and speed things up in the long run. However, most of the times I am in favor of UI's that are self-explanatory. – scooterlord Apr 26 '17 at 7:45
4

Here's an interface for composing boolean logic.

Interface for composing boolean logic

A Few Thoughts

  • The interface starts out simple
  • If it gets complicated, it's because the user built it step by step
  • No editing or drag/drop - just creating and deleting branches
  • Conditions are a simple dropdown in this example, but could be more complicated or possibly negated

Ambiguity

As an aside, I've also been concerned with the ambiguity of "show me red and blue" shirts since the user might technically means "red or blue" shirts.

I think if you ask them describe the singular version (a "shirt") then the problem is somewhat reduced. For example, they wouldn't say "Show me a red and blue shirt" if they mean "red or blue".

4

CognitoForms has the most intuitive AND/OR solution I've come across enter image description here

2

My favourite UI for rule building is ATG's Scenario server. Check this out:

alt text

  • 1
    Can I see more screenshots of this? I'd know how to access them on the Oracle website. – Fresheyeball Apr 21 '15 at 16:15
2

I am working on a redesign of a web app which uses Boolean and the image below shows how it is currently being done. The user can delete the brackets where needed or add them back in. I am struggling to find a better way to do this so may end up keeping this part which users seem to be using quite well.

UI boolean

  • I take it the app is targeted at technical people? The brackets and use of AND/OR seem to point to that. – Edward Williams Apr 7 '16 at 22:02
2
+25

For creating relatively complex single table queries, pivot tables are very useful.

Good stuff

  1. You can get SUM, AVG and GROUP with relatively little knowledge.
  2. By splitting fields across columns vs rows you get AND queries
  3. The totals give you OR queries
  4. You can properly 'build' queries - i.e. you can quickly see a master set, then add rows / columns and then add in filters which shows you which data is available to filter out

Bad stuff

  1. I guess you'll hit limits for if you try to combine more than one table / data set.
  2. Depending on how deeply nested you want your AND / OR queries you might have problems

But at least you don't have a ton of combo boxes, and the combo boxes you do have are more intutive.

Here's one I made earlier with some lovely fantasy football stats to go with it.

enter image description here

  • 1
    pivot tables are powerful, but I always feel like I need to find an expert in quantum string theory to make them work. I don't feel like they're a good UX/IU example at all. (but +1 becauase it's a great addition to the conversation!) – Baronz Apr 8 '16 at 15:07
  • @Baronz I guess I'm used to working with analysts for whom pivot tables were their bread and butter. I was taught about pivot tables by them rather than the other way round :) The great thing is the immediate feedback, but I can see how they might be a problem for novice users. – icc97 Apr 8 '16 at 16:45
2

Adding example from the ticketing app we developed.

Instead of grouping "AND / OR" we settled on an "all / any / none" dropdown at the top. Exactly because of the above mentioned example: when non-technical people say "gimme orders from New York and New Jersey" tit actually means "logical OR".

enter image description here

We also decided against grouping multiple complex AND/OR combinations, since people got confused. We offer them a "recursion" instead, offering to "trigger another rule" (at the bottom of the screenshot).

  • 1
    This is very nice :) I do love how this question keeps growing in terms of answers – Edward Williams Feb 22 at 2:35
1

It is hard to pin down exactly what is intuitive for something that has traditionally been dealt with by technical users (that won't necessarily find interfaces more user-friend compared to command prompts). There is a particular reason for this efficiency, as queries can be unambiguously specified and executed in a command-line prompt. Also, it probably won't surprise anyone that traditional interface design will probably break when it comes to more complex queries.

Nevertheless, I think it is probably safe to say that when it comes to Boolean logic, the most common/familiar theme has to be the Venn diagram. So the question might be how to take the precise statements of database queries and combine it with the simplicity of a Venn diagram like interface?

I think conceptually a possible solution would be to combine both spatial layout with user interactions that reflect the nature of the operations that is being performed. This way you will get the concept of the Venn diagram across to the users while making the operations 'intuitive' enough to understand. So that is effectively taking what @bendytree and @sboye suggested for doing the actual data entry but also outputting the result of those operations in the form of a Venn diagram so that the user can see immediately whether they have performed the right type of boolean operation. But you might want to make the input even easier by implementing some drag'n'drop for the boolean operators and datasets/fields that you want to display, taking inspirations from some of the interactive Venn diagrams that you can find on Google searches.

  • Related interface, showing joins based off a simple Venn Diagram: sql-joins.leopard.in.ua. Not sure how well Venn Diagrams work for a single table though. – icc97 Apr 13 '16 at 15:07
0

If it's mobile, have a button for each operation that simply enters it into a text box. Provide a link to help on boolean algebra and the like. That would be both easy and flexible.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.