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

Advanced Search issue when searching using Group Type(s)

    Details

    • Type: Bug
    • Status: Done/Fixed
    • Priority: Major
    • Resolution: Fixed/Completed
    • Affects Version/s: 4.7
    • Fix Version/s: 4.7.15
    • Component/s: None
    • Labels:
    • Versioning Impact:
      Patch (backwards-compatible bug fixes)
    • Documentation Required?:
      None
    • Funding Source:
      Core Team Funds

      Description

      Testing on clean installation set up with buildkit (Drupal + CiviCRM 4.7.9-rc) :
      civibuild create hr216 --type drupal-demo --civi-ver 4.7.9-rc --web-root /home/kacper/www/hr216

      Searching Contacts using Advanced Search with specified "Group Types(s)" causes SQL syntax error.

      Reason:

      Since 4.7 there is a change in CRM_Contact_BAO_Query group() method:
      https://github.com/civicrm/civicrm-core/commit/7cc09daf41619d471ecb0d5007b914c202d5b82c#diff-e54381bfdf51e31cab376c71ca0d66ffL2810

      which removes:

      if ($value) {
      if (strpos($op, 'IN') === FALSE)

      { $value = key($value); }

      else

      { $value = array_keys($value); }

      }

      and adds:

      if (isset($value))

      { $value = CRM_Utils_Array::value($op, $value, $value); }

      So when $value =
      array(4)

      { [1]=> string(14) "Administrators" [4]=> string(14) "Advisory Board" [2]=> string(22) "Newsletter Subscribers" [3]=> string(25) "Summer Program Volunteers" }

      then after the change, $value bacomes an array of strings instead of integer group IDs. This causes an SQL syntax error:

      SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicrm_contact contact_a LEFT JOIN civicrm_group_contact `civicrm_group_contact-Administrators,Advisory Board,Newsletter Subscribers,Summer Program Volunteers` ON (contact_a.id = `civicrm_group_contact-Administrators,Advisory Board,Newsletter Subscribers,Summer Program Volunteers`.contact_id AND `civicrm_group_contact-Administrators,Advisory Board,Newsletter Subscribers,Summer Program Volunteers`.status IN ("Added")) LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_Administrators,Advisory Board,Newsletter Subscribers,Summer Program Volunteers` ON contact_a.id = `civicrm_group_contact_cache_Administrators,Advisory Board,Newsletter Subscribers,Summer Program Volunteers`.contact_id WHERE ( ( ( `civicrm_group_contact-Administrators,Advisory Board,Newsletter Subscribers,Summer Program Volunteers`.group_id IN ( Administrators,Advisory Board,Newsletter Subscribers,Summer Program Volunteers ) ) OR ( `civicrm_group_contact_cache_Administrators,Advisory Board,Newsletter Subscribers,Summer Program Volunteers`.group_id IN ("Administrators", "Advisory Board", "Newsletter Subscribers", "Summer Program Volunteers") ) ) ) AND (contact_a.is_deleted = 0) ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Board,Newsletter Subscribers,Summer Program Volunteers ) ) OR ( `civicrm_group_c' at line 1]

      In main 'switch case' block which is an entry point for Group Type(s) search we have:

      case 'group_type':
      list($name, $op, $value, $grouping, $wildcard) = $values;
      $values[0] = 'group';
      $values[1] = 'IN';
      $this->_paramLookup['group'][0][0] = 'group';
      $this->_paramLookup['group'][0][1] = 'IN';
      $this->_paramLookup['group'][0][2] = $values[2] = $this->getGroupsFromTypeCriteria($value);
      $this->group($values);
      return;

      At the bottom, we execute $this->group($values) with $values[2] having that Groups array (with Group ID => Group Name structure). Instead of reverting the group() method changes we can just change $values[2] content to not having these Group names which are generated by $this->getGroupsFromTypeCriteria($value); method.

      Possible solution:

      $this->_paramLookup['group'][0][2] = $values[2] = $this->getGroupsFromTypeCriteria($value);
      to:
      $this->_paramLookup['group'][0][2] = $this->getGroupsFromTypeCriteria($value);
      so the group() method would again receive group IDs instead of group names in [2] index of its attribute array.

      Additionally, there were two PHP notices after this change:
      {{Notice: Undefined offset: 4 in CRM_Contact_BAO_Query->query() (line 1364 of /home/casterama/www/hr216/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php).
      Notice: Undefined offset: 3 in CRM_Contact_BAO_Query->query() (line 1364 of /home/casterama/www/hr216/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php).}}

      caused by this line:
      list($name, $op, $value, $grouping, $wildcard) = $this->_paramLookup['group'][0];

      In the case 'group_type' there wasn't a definition for [3] and [4] array index so that's why the notices appear. To fix it we can add two lines (bolded) :

      case 'group_type':
      list($name, $op, $value, $grouping, $wildcard) = $values;
      $values[0] = 'group';
      $values[1] = 'IN';
      $this->_paramLookup['group'][0][0] = 'group';
      $this->_paramLookup['group'][0][1] = 'IN';
      $this->_paramLookup['group'][0][2] = $this->getGroupsFromTypeCriteria($value);
      $this->_paramLookup['group'][0][3] = $grouping;
      $this->_paramLookup['group'][0][4] = $wildcard;
      $this->group($values);
      return;

        Attachments

          Activity

            People

            • Assignee:
              jitendra.purohit Jitendra Purohit
              Reporter:
              coldrunKacper Kacper Warda
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: