Uploaded image for project: 'CiviCRM'
  1. CiviCRM
  2. CRM-17560

Search Builder and Advanced Search - 'OR' query problem with Activities

    Details

    • 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

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              zarandras András J. Molnár
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - 2 hours
                2h
                Remaining:
                Remaining Estimate - 2 hours
                2h
                Logged:
                Time Spent - Not Specified
                Not Specified