Author Topic: Searching contacts marked by group and tag. (Read 1813 times)

raymond · October 26, 2010, 05:36:57 am

I have tried to search contacts by including a particular group, and excluded a particular Tag.
The result is however, that the system can not match this to items.
Is this a bug in CIVI CRM ?

Raymond Nethe

Erik Hommel · October 26, 2010, 05:58:41 am

Just adding some information as I spoke with Raymond:
he is using the standard custom report CRM_Contact_Form_Search_Custom_Group. In this he wants to define a search:
including group A
excluding tag B

this seems to result in only seeing contacts excluding tag B? And I have tested this on the demo site and it does not do the expected there either.
Erik

kathc · October 26, 2010, 08:32:00 am

I just found this later on in this forum - There's a form on the Custom Search page that does exactly that (I just needed to search for members of Group1 who are not members of Group2, Group3 or Group4.

Erik Hommel · October 26, 2010, 08:34:11 am

I think I used the report you are mentioning, and I believe it works for groups but NOT for the combination of include group / exclude tag?

Donald Lobo · October 26, 2010, 10:05:17 am

hey erik:

u might want to check the queries, but i think there is an OR between the groups and tags clauses. so that query is:

all contacts in group A OR (all contacts in the DB who do not have tag B)

ideally would be great if the custom search had an operator which said if you want AND / OR between groups and tags. There is also a pretty awesome test case for that search which u should check out

lobo

Erik Hommel · October 26, 2010, 10:16:10 am

Thanks Lobo, I will discuss this with the customer and see what they would like!

petednz · October 26, 2010, 11:38:36 am

Search Builder may be ugly and unloved but it will do what you want in this case won't it?

Erik Hommel · October 26, 2010, 11:39:43 am

Uhm no that was my first try :-)

xavier · October 27, 2010, 05:26:49 am

For what it's worth, got a very custom search TAG and TAG and Tag, with each tag being a subset of all tags (based on the tag hierarchy).

Right now, the 3 "parent" tags are hard coded and a few other stuff, probably less than 4 hours to make it generic, happy to share the code with whoever is willing to make it happen (tm)

X+

Erik Hommel · October 27, 2010, 05:32:05 am

Merci bien Xavier (to make up for all the vriendelijke groeten

). I will dive into this and find out what the easiest option is.....
Erik

Susanh · October 28, 2010, 08:49:37 am

I just tried this on my database because I know it will be something that I need to do in the near future. It seemed to work with a Search Builder for me. I got correct results when defining both conditions in the first search box which allows for 'and' searches; if you use the "Also include contacts where" box for the second condition, it seems to return contacts that meet either and/or both conditions.

Erik Hommel · October 28, 2010, 09:04:07 am

Thanks Susan, I will have a go with the also too, I did not try that!

Erik Hommel · October 30, 2010, 09:18:39 am

Quote

if you use the "Also include contacts where" box for the second condition, it seems to return contacts that meet either and/or both conditions.

I did have a try, but it did not show the desired results. I need contacts that are in group A and do NOT have tab B. It shows me contacts that are in group A and/or do NOT have tab B. That means I still get all the members of group A. But thanks for the help anyway Susan. If the client wants to I will modify the Custom Search.
Erik

Erik Hommel · November 15, 2010, 11:55:13 am

Right, I have done some investigation.
The CRM_Contact_Form_Custom_Search_Group at the moment only does this when both tags and groups are selected:

Code: [Select]

$from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
$from .= " LEFT JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)";

This gets all the data from Include Groups (Ig) joined with all the data from Include Tags (It). So if I do include a group and include a tag I do get all the members, but if I do include a group and exclude a tag I only get the members of the group.

I will change this to:
* when include Tags and exclude groups LEFT JOIN It WHERE contact_id NOT IN (SELECT * FROM Xg) (contacts in excluded groups)
* when include Groups and exclude Tags LEFT JOIN Ig WHERE contact_id NOT IN (SELECT * FROM Xt) (contacts in excluded tags)
* when both excluded LEFT JOIN Xg LEFT JOIN Xt

Do we want this change in core or should this be a custom search?
Erik

Donald Lobo · November 15, 2010, 08:05:43 pm

might be easier to chat a bit more on IRC about this. A few quick comments:

1. Please do use and improve the test for this custom search at:

tests/phpunit/CRM/Contact/Form/Search/Custom/GroupTest.php

2. I think you are changing the semantic to an AND ONLY under the conditions:

when include Tags and exclude groups
when include Groups and exclude Tags

3. I still think a better approach might be to add an explicit AND / OR operator.

so either

Group A AND (NOT Tag B)

or

Group A OR (NOT Tag B)

and becos of the LEFT JOIN i suspect we do the latter

lobo

CiviCRM Community Forums (archive)

News:

Author Topic: Searching contacts marked by group and tag. (Read 1813 times)

raymond

Searching contacts marked by group and tag.

Erik Hommel

Re: Searching contacts marked by group and tag.

kathc

Re: Searching contacts marked by group and tag.

Erik Hommel

Re: Searching contacts marked by group and tag.

Donald Lobo

Re: Searching contacts marked by group and tag.

Erik Hommel

Re: Searching contacts marked by group and tag.

petednz

Re: Searching contacts marked by group and tag.

Erik Hommel

Re: Searching contacts marked by group and tag.

xavier

Re: Searching contacts marked by group and tag.

Erik Hommel

Re: Searching contacts marked by group and tag.

Susanh

Re: Searching contacts marked by group and tag.

Erik Hommel

Re: Searching contacts marked by group and tag.

Erik Hommel

Re: Searching contacts marked by group and tag.

Erik Hommel

Re: Searching contacts marked by group and tag.

Donald Lobo

Re: Searching contacts marked by group and tag.