Details
-
Type: Bug
-
Status: Done/Fixed
-
Priority: Major
-
Resolution: Fixed/Completed
-
Affects Version/s: 4.5.6, 4.6.11
-
Fix Version/s: None
-
Component/s: CiviCRM Search
-
Labels:
-
Documentation Required?:None
-
Funding Source:Contributed Code
Description
If we use the OR operation to connect two search conditions, one of them dealing with Activities, many contacts disappear from the result set. For instance, if we list all our individuals (by Contact Type), the number of them is about 13 thousand. But if we add a condition of 'including also contacts' with Activity type 'incoming mail', we expect the result set should be greater or equal in size (it may include some extra organization type contacts with mail activities), but it turns out the system applies an unwanted restriction, and returns only about 1.800 contacts for this disjunctive query.
[It occures both by Advanced search and Search Builder. We noticed this weird behaviour after sending out many wrong mails and brochures, therefore it was rather embarrassing and costy.]
We are using CiviCRM version 4.5.6 over WordPress and the wrong behaviour can be reproduced in the CiviCRM demo site as well, for instance: select Search Builder and search Contacts with Contact Type = Individual, then add 'also include contacts where', select Activities, Activity Type = incoming phone or whatever. The query result set is greater if you remove the 'also include contats' part with the activities constraint, although it should less or equal.
I managed to localize the source of the problem: in CRM/Activity/BAO/Query.php there is this code part:
public static function from($name, $mode, $side) { $from = NULL; switch ($name) { case 'civicrm_activity': //CRM-7480 we are going to civicrm_activity table either //from civicrm_activity_target or civicrm_activity_assignment. //as component specific activities does not have entry in //activity target table so lets consider civicrm_activity_assignment. $from .= " INNER JOIN civicrm_activity_contact ON ( civicrm_activity_contact.contact_id = contact_a.id ) "; $from .= " INNER JOIN civicrm_activity ON ( civicrm_activity.id = civicrm_activity_contact.activity_id AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 )"; break;
I don't understand why INNER JOINs stand here because at all other places of this function the parameter $side is used. We corrected this in our deployment like this but don't know yet if it has side-effects:
$from .= " $side JOIN civicrm_activity_contact ON ( civicrm_activity_contact.contact_id = contact_a.id ) "; $from .= " $side JOIN civicrm_activity ON ( civicrm_activity.id = civicrm_activity_contact.activity_id AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 )";
This can be probably the bug fix, but should be verified. At least it works under our deployment. If you find it useful, you may verify and correct it for the next version - if possible.
I reported this issue first in StackExchange, but haven't got any answers or comments:
https://civicrm.stackexchange.com/questions/7370/search-builder-and-advanced-search-or-query-problem-with-activities