How to perform searches with groups of OR statements joined by AND statements

Question

Search Builder in CiviCRM allows groups of AND statements to be connected by OR statements, but not the other way around.

That is, I can perform a search like:

(X AND Y AND Z) OR (A AND B AND C)

but not:

(X OR Y OR Z) AND A AND B AND (C OR D).

If I want to do the latter, I have to rewrite the entire query for each of the elements in the OR statements. That gets pretty tedious pretty quickly.

My questions are:

1. Why is it like this?

2. Is there some way to search (within CiviCRM with all the benefits and options for search results) as per my second example?

Thanks, Lewis

Answer 1

The best way to do what you want is to :

• create a smart group for (X OR Y OR Z) using search builder
• create a smart group for (C OR D) using search builder
• use search builder to combine all the criteria using Contact -> Group -> '=' -> (the group you have created)

I agree it's pretty tedious... but someone will have to re-work the search builder to allow the logic you want. It's not a simple task.

Answer 2

There are also Custom Searches which allow for AND / OR statements. Under the Search pull-down go to the last item "Custom Search".

Answer 3

As well as the excellent answers already given, I thought I'd introduce De Morgan's Laws.

In short, you can do everything you need to through the interface as is :)

There's also scope to rework it to use http://querybuilder.js.org/ if you've a need. It's more featureful.

Answer 4

Re your question "2.Is there some way to search (within CiviCRM with all the benefits and options for search results) as per my second example?"

check out the API Explorer - go to Support -> Developer -> API Explorer.

Not an ideal solution but it should work.