Details
-
Type: Bug
-
Status: Open
-
Priority: Trivial
-
Resolution: Unresolved
-
Affects Version/s: 4.7
-
Fix Version/s: None
-
Component/s: None
-
Labels:None
-
Versioning Impact:Patch (backwards-compatible bug fixes)
-
Documentation Required?:None
-
Funding Source:Needs Funding
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)
else
{ $value = array_keys($value); } }}}
{{and adds:
if (isset($value))
}}
So when $value =
array(4)
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. So my proposition is to change:
$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] = $values[2] = $this->getGroupsFromTypeCriteria($value);
- $this->_paramLookup['group'][0][3] = $grouping;
$this->_paramLookup['group'][0][4] = $wildcard;*
$this->group($values);
return;}}