Author Topic: Want to run search with sets of 'OR' criteria joined by 'AND' (Read 1325 times)

2ways · October 10, 2011, 09:30:28 pm

Hi Folks,

I want to identify a group of people who both live in one of several locations and have one of several roles and meet other specific criteria.

The Search Builder function is set out as sets of 'AND' criteria joined by 'OR', but my query needs to be run the other way around.

How do I do that?

I have locations (l) a, b, and c, and roles (r) x, y, and z, and then, let's say, two other pieces of data, d1 and d2, that all contacts have to match.

The simplest form of my query then looks like: (la OR lb OR lc) AND (rx OR ry OR rz) AND d1 AND d2.

However, in Search Builder, the only way to do it is to make an AND set of every permutation, i.e. (la AND rx AND d1 AND d2) OR (lb AND rx AND d1 AND d2) OR etc.

Very tedious. Any suggestions?

Thanks,
Lewis

Donald Lobo · October 11, 2011, 06:35:55 am

your other option is to write a custom searc restricted to the criteria u r interested in

might also want to check out views integration and see if you can generate a view for what you need

lobo

2ways · October 11, 2011, 07:14:01 am

Thanks. It looks like custom search will be the way to go. Ultimately, views might do it, but I'm not running a front end at all yet, just using drupal as the host for civi.

Any idea why the search builder is set up with that sort of restriction? Could we conceive of a future release where search builder has a wider range of SQL functionality?

Anyway, I haven't tried custom search yet, so I'll give that a go before I start pining for new features...

Thanks again,
Lewis

Donald Lobo · October 11, 2011, 08:46:54 am

building a generic query engine is not a trivial project, IMO. Hence search builder does not include it.

That said, views does a pretty awesome job (and getting better every release) of a generic / extensible / customizable query builder. So our focus is more on extending the integration with views rather than trying to do it within civicrm

lobo

Eileen · October 12, 2011, 05:51:01 pm

You can get a bit further by creating the saved query & then going into the database table & 'tweaking' it - not an official approach but OK sometimes

2ways · October 12, 2011, 06:22:37 pm

Hmm... this is interesting

Do you mean:
1. run a search (could be anything, I suppose)
2. use it to create a smart group
3. go into phpMyAdmin
4. find the database record that defines that smart group
5. substitute in my desired definition
6. save record

It preserves Civi's understanding of this thing as a smart group, so that I get all the accompanying features, e.g. email the group, but I can easily design the query directly in SQL in the backend.

Is that the idea?

Thanks for the tip,
Lewis

Eileen · October 12, 2011, 06:48:03 pm

7) Don't tell Lobo

2ways · October 12, 2011, 06:55:17 pm

tell him what...?

Eileen · October 12, 2011, 07:21:57 pm

Hmm - just test it because he pointed out that some code changes may have happened to caching since I last tried it

CiviCRM Community Forums (archive)

News:

Author Topic: Want to run search with sets of 'OR' criteria joined by 'AND' (Read 1325 times)

2ways

Want to run search with sets of 'OR' criteria joined by 'AND'

Donald Lobo

Re: Want to run search with sets of 'OR' criteria joined by 'AND'

2ways

Re: Want to run search with sets of 'OR' criteria joined by 'AND'

Donald Lobo

Re: Want to run search with sets of 'OR' criteria joined by 'AND'

Eileen

Re: Want to run search with sets of 'OR' criteria joined by 'AND'

2ways

Re: Want to run search with sets of 'OR' criteria joined by 'AND'

Eileen

Re: Want to run search with sets of 'OR' criteria joined by 'AND'

2ways

Re: Want to run search with sets of 'OR' criteria joined by 'AND'

Eileen

Re: Want to run search with sets of 'OR' criteria joined by 'AND'