From 9f6a15566344119b0492547ab535cfc3b3623fb6 Mon Sep 17 00:00:00 2001 From: Jitendra Purohit Date: Fri, 22 Jun 2018 19:53:37 +0530 Subject: [PATCH] Fix issues with smart group search --- CRM/Contact/BAO/Query.php | 27 +++++++++++++--- .../CRM/Contact/BAO/GroupContactCacheTest.php | 31 +++++++++++++++++-- 2 files changed, 52 insertions(+), 6 deletions(-) diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index 3858435b48..861a2f2ca4 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -3041,9 +3041,25 @@ class CRM_Contact_BAO_Query { } //CRM-19589: contact(s) removed from a Smart Group, resides in civicrm_group_contact table - if (count($smartGroupIDs)) { - $groupContactCacheClause = $this->addGroupContactCache($smartGroupIDs, NULL, "contact_a", $op); + $groupContactCacheClause = ''; + if (count($smartGroupIDs) || empty($value)) { + $isNullOp = (strpos($op, 'NULL') !== FALSE); + $gccTableAlias = "`civicrm_group_contact_cache_"; + $gccTableAlias .= ($isNullOp) ? "a`" : implode(',', $smartGroupIDs) . "`"; + $groupContactCacheClause = $this->addGroupContactCache($smartGroupIDs, $gccTableAlias, "contact_a", $op); if (!empty($groupContactCacheClause)) { + if ($isNotOp) { + $groupIds = implode(',', (array) $smartGroupIDs); + $gcTable = "`civicrm_group_contact-{$groupIds}`"; + $joinClause = array("contact_a.id = {$gcTable}.contact_id"); + $this->_tables[$gcTable] = $this->_whereTables[$gcTable] = " LEFT JOIN civicrm_group_contact {$gcTable} ON (" . implode(' AND ', $joinClause) . ")"; + if (strpos($op, 'IN') !== FALSE) { + $groupClause[] = "{$gcTable}.group_id $op ( $groupIds ) AND {$gccTableAlias}.group_id IS NULL"; + } + else { + $groupClause[] = "{$gcTable}.group_id $op $groupIds AND {$gccTableAlias}.group_id IS NULL"; + } + } $groupClause[] = " ( {$groupContactCacheClause} ) "; } } @@ -3124,7 +3140,7 @@ WHERE $smartGroupClause CRM_Contact_BAO_GroupContactCache::load($group); } } - if ($group->N == 0) { + if ($group->N == 0 && $op != 'NOT IN') { return NULL; } @@ -3132,8 +3148,11 @@ WHERE $smartGroupClause $tableAlias = "`civicrm_group_contact_cache_"; $tableAlias .= ($isNullOp) ? "a`" : implode(',', (array) $groupsIds) . "`"; } - $this->_tables[$tableAlias] = $this->_whereTables[$tableAlias] = " LEFT JOIN civicrm_group_contact_cache {$tableAlias} ON {$joinTable}.{$joinColumn} = {$tableAlias}.contact_id "; + + if ($op == 'NOT IN') { + return "{$tableAlias}.contact_id NOT IN (SELECT contact_id FROM civicrm_group_contact_cache cgcc WHERE cgcc.group_id IN ( " . implode(',', (array) $groupsIds) . " ) )"; + } return self::buildClause("{$tableAlias}.group_id", $op, $groups, 'Int'); } diff --git a/tests/phpunit/CRM/Contact/BAO/GroupContactCacheTest.php b/tests/phpunit/CRM/Contact/BAO/GroupContactCacheTest.php index 0b4f8ee25c..4fc0348fca 100644 --- a/tests/phpunit/CRM/Contact/BAO/GroupContactCacheTest.php +++ b/tests/phpunit/CRM/Contact/BAO/GroupContactCacheTest.php @@ -437,8 +437,13 @@ class CRM_Contact_BAO_GroupContactCacheTest extends CiviUnitTestCase { FALSE, FALSE, FALSE ); + $ids = $query->searchQuery(0, 0, NULL, + FALSE, FALSE, FALSE, + TRUE, FALSE + ); $expectedWhere = "`civicrm_group_contact_cache_{$group2->id}`.group_id IN (\"{$group2->id}\")"; $this->assertContains($expectedWhere, $query->_whereClause); + $this->_assertContactIds($query, "group_id = {$group2->id}"); $params = array(array('group', '!=', $group->id, 1, 0)); $query = new CRM_Contact_BAO_Query( @@ -448,8 +453,9 @@ class CRM_Contact_BAO_GroupContactCacheTest extends CiviUnitTestCase { FALSE, FALSE ); //Assert if proper where clause is present. - $expectedWhere = "`civicrm_group_contact_cache_{$group->id}`.group_id NOT IN (\"{$group->id}\")"; + $expectedWhere = "`civicrm_group_contact-{$group->id}`.group_id != {$group->id} AND `civicrm_group_contact_cache_{$group->id}`.group_id IS NULL OR ( `civicrm_group_contact_cache_{$group->id}`.contact_id NOT IN (SELECT contact_id FROM civicrm_group_contact_cache cgcc WHERE cgcc.group_id IN ( {$group->id} ) ) )"; $this->assertContains($expectedWhere, $query->_whereClause); + $this->_assertContactIds($query, "group_id != {$group->id}"); $params = array(array('group', 'IN', array($group->id, $group2->id), 1, 0)); $query = new CRM_Contact_BAO_Query( @@ -460,6 +466,7 @@ class CRM_Contact_BAO_GroupContactCacheTest extends CiviUnitTestCase { ); $expectedWhere = "`civicrm_group_contact_cache_{$group->id},{$group2->id}`.group_id IN (\"{$group->id}\", \"{$group2->id}\")"; $this->assertContains($expectedWhere, $query->_whereClause); + $this->_assertContactIds($query, "group_id IN ({$group->id}, {$group2->id})"); $params = array(array('group', 'NOT IN', array($group->id), 1, 0)); $query = new CRM_Contact_BAO_Query( @@ -468,8 +475,28 @@ class CRM_Contact_BAO_GroupContactCacheTest extends CiviUnitTestCase { FALSE, FALSE, FALSE ); - $expectedWhere = "`civicrm_group_contact_cache_{$group->id}`.group_id NOT IN (\"{$group->id}\")"; + $expectedWhere = "`civicrm_group_contact-{$group->id}`.group_id NOT IN ( {$group->id} ) AND `civicrm_group_contact_cache_{$group->id}`.group_id IS NULL OR ( `civicrm_group_contact_cache_{$group->id}`.contact_id NOT IN (SELECT contact_id FROM civicrm_group_contact_cache cgcc WHERE cgcc.group_id IN ( {$group->id} ) ) )"; $this->assertContains($expectedWhere, $query->_whereClause); + $this->_assertContactIds($query, "group_id NOT IN ({$group->id})"); + } + + /** + * Check if contact ids are fetched correctly. + * + * @param object $query + * @param string $groupWhereClause + */ + public function _assertContactIds($query, $groupWhereClause) { + $contactIds = explode(',', $query->searchQuery(0, 0, NULL, + FALSE, FALSE, FALSE, + TRUE, FALSE + )); + $expectedContactIds = array(); + $groupDAO = CRM_Core_DAO::executeQuery("SELECT contact_id FROM civicrm_group_contact_cache WHERE {$groupWhereClause}"); + while ($groupDAO->fetch()) { + $expectedContactIds[] = $groupDAO->contact_id; + } + $this->assertEquals(sort($expectedContactIds), sort($contactIds)); } } -- 2.25.1