4 * Include dataProvider for tests
8 class CRM_Contact_BAO_QueryTest
extends CiviUnitTestCase
{
10 use CRMTraits_Financial_FinancialACLTrait
;
11 use CRMTraits_Financial_PriceSetTrait
;
14 * @return CRM_Contact_BAO_QueryTestDataProvider
16 public function dataProvider() {
17 return new CRM_Contact_BAO_QueryTestDataProvider();
21 * Clean up after test.
25 public function tearDown(): void
{
26 $this->quickCleanUpFinancialEntities();
28 'civicrm_group_contact',
30 'civicrm_saved_search',
36 $this->quickCleanup($tablesToTruncate);
41 * Test CRM_Contact_BAO_Query::searchQuery().
43 * @dataProvider dataProvider
49 * @throws \CRM_Core_Exception
51 public function testSearch($fv, $count, $ids) {
52 $this->callAPISuccess('SavedSearch', 'create', ['form_values' => 'a:9:{s:5:"qfKey";s:32:"0123456789abcdef0123456789abcdef";s:13:"includeGroups";a:1:{i:0;s:1:"3";}s:13:"excludeGroups";a:0:{}s:11:"includeTags";a:0:{}s:11:"excludeTags";a:0:{}s:4:"task";s:2:"14";s:8:"radio_ts";s:6:"ts_all";s:14:"customSearchID";s:1:"4";s:17:"customSearchClass";s:36:"CRM_Contact_Form_Search_Custom_Group";}']);
53 $this->callAPISuccess('SavedSearch', 'create', ['form_values' => 'a:9:{s:5:"qfKey";s:32:"0123456789abcdef0123456789abcdef";s:13:"includeGroups";a:1:{i:0;s:1:"3";}s:13:"excludeGroups";a:0:{}s:11:"includeTags";a:0:{}s:11:"excludeTags";a:0:{}s:4:"task";s:2:"14";s:8:"radio_ts";s:6:"ts_all";s:14:"customSearchID";s:1:"4";s:17:"customSearchClass";s:36:"CRM_Contact_Form_Search_Custom_Group";}']);
55 $tag7 = $this->ids
['Tag'][7] = $this->tagCreate(['name' => 'Test Tag 7', 'description' => 'Test Tag 7'])['id'];
56 $tag9 = $this->ids
['Tag'][9] = $this->tagCreate(['name' => 'Test Tag 9', 'description' => 'Test Tag 9'])['id'];
57 $tag10 = $this->ids
['Tag'][10] = $this->tagCreate(['name' => 'Test Tag 10', 'description' => 'Test Tag 10', 'parent_id' => $tag9])['id'];
58 $tag11 = $this->ids
['Tag'][11] = $this->tagCreate(['name' => 'Test Tag 11', 'description' => 'Test Tag 11', 'parent_id' => $tag10])['id'];
61 3 => ['name' => 'Test Group 3'],
62 4 => ['name' => 'Test Smart Group 4', 'saved_search_id' => 1],
63 5 => ['name' => 'Test Group 5'],
64 6 => ['name' => 'Test Smart Group 6', 'saved_search_id' => 2],
67 foreach ($groups as $id => $group) {
68 $this->ids
['Group'][$id] = $this->groupCreate(array_merge($group, ['title' => $group['name']]));
71 ['first_name' => 'Test', 'last_name' => 'Test Contact 9', 'gender_id' => 1, 'prefix_id' => 1, 'suffix_id' => 1],
72 ['first_name' => 'Test', 'last_name' => 'Test Contact 10', 'gender_id' => 2, 'prefix_id' => 2, 'suffix_id' => 2, 'api.entity_tag.create' => ['tag_id' => $tag9]],
73 ['first_name' => 'Test', 'last_name' => 'Test Contact 11', 'gender_id' => 3, 'prefix_id' => 3, 'suffix_id' => 3, 'api.entity_tag.create' => ['tag_id' => $tag7]],
75 'first_name' => 'Test',
76 'last_name' => 'Test Contact 12',
80 'api.entity_tag.create' => ['tag_id' => $tag9],
81 'api.entity_tag.create.2' => ['tag_id' => $tag7],
83 ['first_name' => 'Test', 'last_name' => 'Test Contact 13', 'gender_id' => 2, 'prefix_id' => 2, 'suffix_id' => 2],
84 ['first_name' => 'Test', 'last_name' => 'Test Contact 14', 'gender_id' => 3, 'prefix_id' => 4, 'suffix_id' => 4, 'api.entity_tag.create' => ['tag_id' => $tag9]],
85 ['first_name' => 'Test', 'last_name' => 'Test Contact 15', 'gender_id' => 3, 'prefix_id' => 4, 'suffix_id' => 5, 'api.entity_tag.create' => ['tag_id' => $tag7]],
87 'first_name' => 'Test',
88 'last_name' => 'Test Contact 16',
92 'api.entity_tag.create' => ['tag_id' => $tag9],
93 'api.entity_tag.create.2' => ['tag_id' => $tag7],
95 ['first_name' => 'Test', 'last_name' => 'Test Contact 17', 'gender_id' => 2, 'prefix_id' => 4, 'suffix_id' => 7],
96 ['first_name' => 'Test', 'last_name' => 'Test Contact 18', 'gender_id' => 2, 'prefix_id' => 4, 'suffix_id' => 4, 'api.entity_tag.create' => ['tag_id' => $tag9]],
97 ['first_name' => 'Test', 'last_name' => 'Test Contact 19', 'gender_id' => 2, 'prefix_id' => 4, 'suffix_id' => 6, 'api.entity_tag.create.2' => ['tag_id' => $tag7]],
99 'first_name' => 'Test',
100 'last_name' => 'Test Contact 20',
104 'api.entity_tag.create' => ['tag_id' => $tag9],
105 'api.entity_tag.create.2' => ['tag_id' => $tag7],
107 ['first_name' => 'Test', 'last_name' => 'Test Contact 21', 'gender_id' => 3, 'prefix_id' => 1, 'suffix_id' => 6],
108 ['first_name' => 'Test', 'last_name' => 'Test Contact 22', 'gender_id' => 1, 'prefix_id' => 1, 'suffix_id' => 1, 'api.entity_tag.create' => ['tag_id' => $tag9]],
109 ['first_name' => 'Test', 'last_name' => 'Test Contact 23', 'gender_id' => 3, 'prefix_id' => 1, 'suffix_id' => 1, 'api.entity_tag.create' => ['tag_id' => $tag7]],
111 'first_name' => 'Test',
112 'last_name' => 'Test Contact 24',
116 'api.entity_tag.create' => ['tag_id' => $tag9],
117 'api.entity_tag.create.2' => ['tag_id' => $tag7],
119 ['first_name' => 'Test', 'last_name' => 'Test Contact 25', 'api.entity_tag.create' => ['tag_id' => $tag10]],
120 ['first_name' => 'Test', 'last_name' => 'Test Contact 26', 'api.entity_tag.create' => ['tag_id' => $tag11]],
122 foreach ($individuals as $individual) {
123 $this->ids
['Contact'][$individual['last_name']] = $this->individualCreate($individual);
143 foreach ($groupContacts as $group) {
144 $groupID = $this->ids
['Group'][key($group)];
145 $contactID = $this->ids
['Contact']['Test Contact ' . reset($group)];
146 $this->callAPISuccess('GroupContact', 'create', ['group_id' => $groupID, 'contact_id' => $contactID, 'status' => 'Added']);
149 // We have migrated from a hard-coded dataset to a dynamic one but are still working with the same
150 // dataprovider at this stage -> wrangle.
151 foreach ($fv as $key => $value) {
152 $entity = ucfirst($key);
153 if (!array_key_exists($entity, $this->ids
)) {
156 if (is_numeric($value)) {
157 $fv[$key] = $this->ids
[$entity][$value];
159 elseif (!empty($value[0])) {
160 foreach ($value as $index => $oldGroup) {
161 $fv[$key][$index] = $this->ids
[$entity][$oldGroup];
165 foreach (array_keys($value) as $index) {
166 unset($fv[$key][$index]);
167 $fv[$key][$this->ids
[$entity][$index]] = 1;
172 $params = CRM_Contact_BAO_Query
::convertFormValues($fv);
173 $obj = new CRM_Contact_BAO_Query($params);
175 // let's set useGroupBy=true since we are listing contacts here who might belong to
176 // more than one group / tag / notes etc.
177 $obj->_useGroupBy
= TRUE;
179 $dao = $obj->searchQuery();
182 while ($dao->fetch()) {
183 $contacts[] = $dao->contact_id
;
186 sort($contacts, SORT_NUMERIC
);
189 foreach ($ids as $id) {
190 $expectedIDs[] = $this->ids
['Contact']['Test Contact ' . $id];
193 $this->assertEquals($expectedIDs, $contacts);
197 * Check that we get a successful result querying for home address.
198 * CRM-14263 search builder failure with search profile & address in criteria
200 * @throws \CRM_Core_Exception
202 public function testSearchProfileHomeCityCRM14263() {
203 $contactID = $this->individualCreate();
204 Civi
::settings()->set('defaultSearchProfileID', 1);
205 $this->callAPISuccess('address', 'create', [
206 'contact_id' => $contactID,
207 'city' => 'Cool City',
208 'location_type_id' => 1,
219 $returnProperties = [
221 'contact_sub_type' => 1,
225 $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
227 $resultDAO = $queryObj->searchQuery();
228 $this->assertTrue($resultDAO->fetch());
230 catch (PEAR_Exception
$e) {
231 $err = $e->getCause();
232 $this->fail('invalid SQL created' . $e->getMessage() . ' ' . $err->userinfo
);
238 * Check that we get a successful result querying for home address.
239 * CRM-14263 search builder failure with search profile & address in criteria
241 * @throws \CRM_Core_Exception
243 public function testSearchProfileHomeCityNoResultsCRM14263() {
244 $contactID = $this->individualCreate();
245 Civi
::settings()->set('defaultSearchProfileID', 1);
246 $this->callAPISuccess('address', 'create', [
247 'contact_id' => $contactID,
248 'city' => 'Cool City',
249 'location_type_id' => 1,
260 $returnProperties = [
262 'contact_sub_type' => 1,
266 $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
268 $resultDAO = $queryObj->searchQuery();
269 $this->assertFalse($resultDAO->fetch());
271 catch (PEAR_Exception
$e) {
272 $err = $e->getCause();
273 $this->fail('invalid SQL created' . $e->getMessage() . ' ' . $err->userinfo
);
279 * Test searchPrimaryDetailsOnly setting.
281 * @throws \CRM_Core_Exception
283 public function testSearchPrimaryLocTypes() {
284 $contactID = $this->individualCreate();
286 'contact_id' => $contactID,
287 'email' => 'primary@example.com',
290 $this->callAPISuccess('email', 'create', $params);
292 unset($params['is_primary']);
293 $params['email'] = 'secondary@team.com';
294 $this->callAPISuccess('email', 'create', $params);
296 foreach ([0, 1] as $searchPrimary) {
297 Civi
::settings()->set('searchPrimaryDetailsOnly', $searchPrimary);
303 2 => 'sEcondary@example.com',
308 $returnProperties = [
310 'contact_sub_type' => 1,
314 $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
315 $resultDAO = $queryObj->searchQuery();
317 if ($searchPrimary) {
318 $this->assertEquals($resultDAO->N
, 0);
321 //Assert secondary email gets included in search results.
322 while ($resultDAO->fetch()) {
323 $this->assertEquals('secondary@example.com', $resultDAO->email
);
327 // API should always return primary email.
328 $result = $this->callAPISuccess('Contact', 'get', ['contact_id' => $contactID]);
329 $this->assertEquals('primary@example.com', $result['values'][$contactID]['email']);
334 * Test created to prove failure of search on state when location
335 * display name is different form location name (issue 607)
337 * @throws \CRM_Core_Exception
339 public function testSearchOtherLocationUpperLower() {
343 0 => 'state_province-4',
350 $returnProperties = [
352 'contact_sub_type' => 1,
356 'location_type' => 4,
357 'state_province' => 1,
362 // update with the api does not work because it updates both the name and the
363 // the display_name. Plain SQL however does the job
364 CRM_Core_DAO
::executeQuery('update civicrm_location_type set name=%2 where id=%1',
367 2 => ['other', 'String'],
370 $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
372 $resultDAO = $queryObj->searchQuery();
377 * CRM-14263 search builder failure with search profile & address in criteria.
379 * We are retrieving primary here - checking the actual sql seems super prescriptive - but since the massive query object has
380 * so few tests detecting any change seems good here :-)
382 * @dataProvider getSearchProfileData
384 * @param array $params
385 * @param string $selectClause
386 * @param string $whereClause
388 * @throws \CRM_Core_Exception
390 public function testSearchProfilePrimaryCityCRM14263($params, $selectClause, $whereClause) {
391 $contactID = $this->individualCreate();
392 Civi
::settings()->set('defaultSearchProfileID', 1);
393 $this->callAPISuccess('address', 'create', [
394 'contact_id' => $contactID,
395 'city' => 'Cool CITY',
396 'street_address' => 'Long STREET',
397 'location_type_id' => 1,
399 $returnProperties = [
401 'contact_sub_type' => 1,
404 $expectedSQL = 'SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, contact_a.contact_sub_type as `contact_sub_type`, contact_a.sort_name as `sort_name`, civicrm_address.id as address_id, ' . $selectClause . " FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) WHERE ( ( " . $whereClause . " ) ) AND ( 1 ) AND (contact_a.is_deleted = 0) ORDER BY `contact_a`.`sort_name` ASC, `contact_a`.`id` ";
405 $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
407 $this->assertLike($expectedSQL, $queryObj->getSearchSQL());
408 list($select, $from, $where, $having) = $queryObj->query();
409 $dao = CRM_Core_DAO
::executeQuery("$select $from $where $having");
411 $this->assertEquals('Anderson, Anthony', $dao->sort_name
);
413 catch (PEAR_Exception
$e) {
414 $err = $e->getCause();
415 $this->fail('invalid SQL created' . $e->getMessage() . ' ' . $err->userinfo
);
421 * Get data sets to test for search.
423 public function getSearchProfileData() {
426 [['city', '=', 'Cool City', 1, 0]],
427 'civicrm_address.city as `city`',
428 "civicrm_address.city = 'Cool City'",
431 // Note that in the query 'long street' is lower cased. We eventually want to change that & not mess with the vars - it turns out
432 // it doesn't work on some charsets. However, the the lcasing affects more vars & we are looking to stagger removal of lcasing 'in case'
433 // (although we have been removing without blowback since 2017)
434 [['street_address', '=', 'Long Street', 1, 0]],
435 'civicrm_address.street_address as `street_address`',
436 "civicrm_address.street_address LIKE '%Long Street%'",
442 * Test similarly handled activity fields qill and where clauses.
444 * @throws \CRM_Core_Exception
446 public function testSearchBuilderActivityType() {
447 $queryObj = new CRM_Contact_BAO_Query([['activity_type', '=', '3', 1, 0]]);
448 $this->assertStringContainsString('WHERE ( ( civicrm_activity.activity_type_id = 3 )', $queryObj->getSearchSQL());
449 $this->assertEquals('Activity Type = Email', $queryObj->_qill
[1][0]);
451 $queryObj = new CRM_Contact_BAO_Query([['activity_type_id', '=', '3', 1, 0]]);
452 $this->assertStringContainsString('WHERE ( ( civicrm_activity.activity_type_id = 3 )', $queryObj->getSearchSQL());
453 $this->assertEquals('Activity Type ID = Email', $queryObj->_qill
[1][0]);
455 $queryObj = new CRM_Contact_BAO_Query([['activity_status', '=', '3', 1, 0]]);
456 $this->assertStringContainsString('WHERE ( ( civicrm_activity.status_id = 3 )', $queryObj->getSearchSQL());
457 $this->assertEquals('Activity Status = Cancelled', $queryObj->_qill
[1][0]);
459 $queryObj = new CRM_Contact_BAO_Query([['activity_status_id', '=', '3', 1, 0]]);
460 $this->assertStringContainsString('WHERE ( ( civicrm_activity.status_id = 3 )', $queryObj->getSearchSQL());
461 $this->assertEquals('Activity Status = Cancelled', $queryObj->_qill
[1][0]);
463 $queryObj = new CRM_Contact_BAO_Query([['activity_engagement_level', '=', '3', 1, 0]]);
464 $this->assertStringContainsString('WHERE ( ( civicrm_activity.engagement_level = 3 )', $queryObj->getSearchSQL());
465 $this->assertEquals('Engagement Index = 3', $queryObj->_qill
[1][0]);
467 $queryObj = new CRM_Contact_BAO_Query([['activity_id', '=', '3', 1, 0]]);
468 $this->assertStringContainsString('WHERE ( ( civicrm_activity.id = 3 )', $queryObj->getSearchSQL());
469 $this->assertEquals('Activity ID = 3', $queryObj->_qill
[1][0]);
471 $queryObj = new CRM_Contact_BAO_Query([['activity_campaign_id', '=', '3', 1, 0]]);
472 $this->assertStringContainsString('WHERE ( ( civicrm_activity.campaign_id = 3 )', $queryObj->getSearchSQL());
473 $this->assertEquals('Campaign ID = 3', $queryObj->_qill
[1][0]);
475 $queryObj = new CRM_Contact_BAO_Query([['activity_priority_id', '=', '3', 1, 0]]);
476 $this->assertStringContainsString('WHERE ( ( civicrm_activity.priority_id = 3 )', $queryObj->getSearchSQL());
477 $this->assertEquals('Priority = Low', $queryObj->_qill
[1][0]);
479 $queryObj = new CRM_Contact_BAO_Query([['activity_subject', '=', '3', 1, 0]]);
480 $this->assertStringContainsString("WHERE ( ( civicrm_activity.subject = '3' )", $queryObj->getSearchSQL());
481 $this->assertEquals("Subject = '3'", $queryObj->_qill
[1][0]);
485 * Test set up to test calling the query object per GroupContactCache BAO usage.
487 * CRM-17254 ensure that if only the contact_id is required other fields should
490 * @throws \CRM_Core_Exception
492 public function testGroupContactCacheAddSearch() {
493 $returnProperties = ['contact_id'];
494 $params = [['group', 'IN', [1], 0, 0]];
496 $query = new CRM_Contact_BAO_Query(
497 $params, $returnProperties,
498 NULL, TRUE, FALSE, 1,
503 list($select) = $query->query();
504 $this->assertEquals('SELECT contact_a.id as contact_id', $select);
508 * Test smart groups with non-numeric don't fail on range queries.
510 * @see https://issues.civicrm.org/jira/browse/CRM-14720
512 * @throws \CRM_Core_Exception
514 public function testNumericPostal() {
515 // Precaution as hitting some inconsistent set up running in isolation vs in the suite.
516 CRM_Core_DAO
::executeQuery('UPDATE civicrm_address SET postal_code = NULL');
518 $this->individualCreate(['api.address.create' => ['postal_code' => 5, 'location_type_id' => 'Main']]);
519 $this->individualCreate(['api.address.create' => ['postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main']]);
520 $this->individualCreate(['api.address.create' => ['postal_code' => '4', 'location_type_id' => 'Main']]);
521 $this->individualCreate(['api.address.create' => ['postal_code' => '6', 'location_type_id' => 'Main']]);
522 $this->individualCreate(['api.address.create' => ['street_address' => 'just a street', 'location_type_id' => 'Main']]);
523 $this->individualCreate(['api.address.create' => ['postal_code' => '12345678444455555555555555555555555555555555551314151617181920', 'location_type_id' => 'Main']]);
525 $params = [['postal_code_low', '=', 5, 0, 0]];
526 CRM_Contact_BAO_Query
::convertFormValues($params);
528 $query = new CRM_Contact_BAO_Query(
529 $params, ['contact_id'],
530 NULL, TRUE, FALSE, 1,
535 $sql = $query->query();
536 $result = CRM_Core_DAO
::executeQuery(implode(' ', $sql));
537 $this->assertEquals(2, $result->N
);
539 // We save this as a smart group and then load it. With mysql warnings on & CRM-14720 this
540 // results in mysql warnings & hence fatal errors.
541 /// I was unable to get mysql warnings to activate in the context of the unit tests - but
542 // felt this code still provided a useful bit of coverage as it runs the various queries to load
543 // the group & could generate invalid sql if a bug were introduced.
544 $groupParams = ['title' => 'postal codes', 'formValues' => $params, 'is_active' => 1];
545 $group = CRM_Contact_BAO_Group
::createSmartGroup($groupParams);
546 CRM_Contact_BAO_GroupContactCache
::load($group);
550 * Test searches are case insensitive.
552 * @throws \CRM_Core_Exception
554 public function testCaseInsensitive() {
555 $orgID = $this->organizationCreate(['organization_name' => 'BOb']);
557 'display_name' => 'Minnie Mouse',
558 'first_name' => 'Minnie',
559 'last_name' => 'Mouse',
560 'employer_id' => $orgID,
561 'contact_type' => 'Individual',
562 'nick_name' => 'Mins',
564 $this->callAPISuccess('Contact', 'create', $params);
565 unset($params['contact_type']);
566 foreach ($params as $key => $value) {
567 if ($key === 'employer_id') {
568 $searchParams = [['current_employer', '=', 'bob', 0, 1]];
571 $searchParams = [[$key, '=', strtolower($value), 0, 1]];
574 [$result] = CRM_Contact_BAO_Query
::apiQuery($searchParams);
575 $this->assertCount(1, $result, 'search for ' . $key);
576 $contact = reset($result);
577 $this->assertEquals('Minnie Mouse', $contact['display_name']);
578 $this->assertEquals('BOb', $contact['current_employer']);
583 * Test smart groups with non-numeric don't fail on equal queries.
585 * @see https://issues.civicrm.org/jira/browse/CRM-14720
587 * @throws \CRM_Core_Exception
589 public function testNonNumericEqualsPostal() {
590 $this->individualCreate(['api.address.create' => ['postal_code' => 5, 'location_type_id' => 'Main']]);
591 $this->individualCreate(['api.address.create' => ['postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main']]);
592 $this->individualCreate(['api.address.create' => ['postal_code' => '4', 'location_type_id' => 'Main']]);
593 $this->individualCreate(['api.address.create' => ['postal_code' => '6', 'location_type_id' => 'Main']]);
595 $params = [['postal_code', '=', 'EH10 4RB-889', 0, 0]];
596 CRM_Contact_BAO_Query
::convertFormValues($params);
598 $query = new CRM_Contact_BAO_Query(
599 $params, ['contact_id'],
600 NULL, TRUE, FALSE, 1,
605 $sql = $query->query(FALSE);
606 $this->assertEquals("WHERE ( civicrm_address.postal_code = 'EH10 4RB-889' ) AND (contact_a.is_deleted = 0)", $sql[2]);
607 $result = CRM_Core_DAO
::executeQuery(implode(' ', $sql));
608 $this->assertEquals(1, $result->N
);
613 * Test relationship description.
615 * @throws \CRM_Core_Exception
617 public function testRelationshipDescription() {
618 $relType = $this->callAPISuccess('RelationshipType', 'create', [
619 'name_a_b' => 'blah',
620 'name_b_a' => 'other blah',
622 $contactID_a = $this->individualCreate([], 1);
623 $contactID_b = $this->individualCreate([], 2);
624 $contactID_c = $this->individualCreate([], 3);
625 $contactID_d = $this->individualCreate([], 4);
626 $desc = uniqid('rel', TRUE);
627 $this->callAPISuccess('Relationship', 'create', [
628 'contact_id_a' => $contactID_a,
629 'contact_id_b' => $contactID_b,
630 'relationship_type_id' => $relType['id'],
632 'description' => $desc,
634 $this->callAPISuccess('Relationship', 'create', [
635 'contact_id_a' => $contactID_c,
636 'contact_id_b' => $contactID_d,
637 'relationship_type_id' => $relType['id'],
639 'description' => 'nothing of interest',
642 ['relation_description', '=', substr($desc, 3, 18), 0, 0],
645 $query = new CRM_Contact_BAO_Query($params);
646 $dao = $query->searchQuery();
647 // This is a little weird but seems consistent with the behavior of the search form in general.
648 // Technically there are 2 contacts who share a relationship with the description searched for,
649 // so one might expect the search form to return both of them instead of just Contact A... but it doesn't.
650 $this->assertEquals('1', $dao->N
, 'Search query returns exactly 1 result?');
651 $this->assertTrue($dao->fetch(), 'Search query returns success?');
652 $this->assertEquals($contactID_a, $dao->contact_id
, 'Search query returns contact A?');
656 * Test non-reciprocal relationship.
658 * @throws \CRM_Core_Exception
660 public function testNonReciprocalRelationshipTargetGroupIsCorrectResults() {
661 $contactID_a = $this->individualCreate();
662 $contactID_b = $this->individualCreate();
663 $this->callAPISuccess('Relationship', 'create', [
664 'contact_id_a' => $contactID_a,
665 'contact_id_b' => $contactID_b,
666 'relationship_type_id' => 1,
669 // Create a group and add contact A to it.
670 $groupID = $this->groupCreate();
671 $this->callAPISuccess('GroupContact', 'create', ['group_id' => $groupID, 'contact_id' => $contactID_a, 'status' => 'Added']);
673 // Add another (sans-relationship) contact to the group,
674 $contactID_c = $this->individualCreate();
675 $this->callAPISuccess('GroupContact', 'create', ['group_id' => $groupID, 'contact_id' => $contactID_c, 'status' => 'Added']);
679 0 => 'relation_type_id',
689 0 => 'relation_target_group',
700 $query = new CRM_Contact_BAO_Query($params);
701 $dao = $query->searchQuery();
702 $this->assertEquals('1', $dao->N
, 'Search query returns exactly 1 result?');
703 $this->assertTrue($dao->fetch(), 'Search query returns success?');
704 $this->assertEquals($contactID_b, $dao->contact_id
, 'Search query returns parent of contact A?');
708 * Relationship search with custom fields.
710 * @throws \CRM_Core_Exception
712 public function testReciprocalRelationshipWithCustomFields() {
714 'extends' => 'Relationship',
716 $customGroup = $this->customGroupCreate($params);
717 $customFieldId = $this->customFieldCreate(['custom_group_id' => $customGroup['id']])['id'];
718 $contactID_a = $this->individualCreate();
719 $contactID_b = $this->individualCreate();
720 $relationship = $this->callAPISuccess('Relationship', 'create', [
721 'contact_id_a' => $contactID_a,
722 'contact_id_b' => $contactID_b,
723 'relationship_type_id' => 2,
725 "custom_{$customFieldId}" => 'testvalue',
729 0 => 'relation_type_id',
739 0 => "custom_{$customFieldId}",
747 $query = new CRM_Contact_BAO_Query($params);
748 $dao = $query->searchQuery();
749 $this->assertEquals('2', $dao->N
);
750 $this->callAPISuccess('Relationship', 'delete', ['id' => $relationship['id']]);
751 $this->callAPISuccess('Contact', 'delete', ['id' => $contactID_a, 'skip_undelete' => 1]);
752 $this->callAPISuccess('Contact', 'delete', ['id' => $contactID_b, 'skip_undelete' => 1]);
753 $this->callAPISuccess('CustomField', 'delete', ['id' => $customFieldId, 'skip_undelete' => 1]);
754 $this->callAPISuccess('CustomGroup', 'delete', ['id' => $customGroup]);
758 * @throws \CRM_Core_Exception
760 public function testReciprocalRelationshipTargetGroupIsCorrectResults() {
761 $contactID_a = $this->individualCreate();
762 $contactID_b = $this->individualCreate();
763 $this->callAPISuccess('Relationship', 'create', [
764 'contact_id_a' => $contactID_a,
765 'contact_id_b' => $contactID_b,
766 'relationship_type_id' => 2,
769 // Create a group and add contact A to it.
770 $groupID = $this->groupCreate();
771 $this->callAPISuccess('GroupContact', 'create', ['group_id' => $groupID, 'contact_id' => $contactID_a, 'status' => 'Added']);
773 // Add another (sans-relationship) contact to the group,
774 $contactID_c = $this->individualCreate();
775 $this->callAPISuccess('GroupContact', 'create', ['group_id' => $groupID, 'contact_id' => $contactID_c, 'status' => 'Added']);
779 0 => 'relation_type_id',
789 0 => 'relation_target_group',
800 $query = new CRM_Contact_BAO_Query($params);
801 $dao = $query->searchQuery();
802 $this->assertEquals('1', $dao->N
, 'Search query returns exactly 1 result?');
803 $this->assertTrue($dao->fetch(), 'Search query returns success?');
804 $this->assertEquals($contactID_b, $dao->contact_id
, 'Search query returns spouse of contact A?');
808 * Test correct temporary table in reciprocal relationship search.
810 * @throws \CRM_Core_Exception
812 public function testReciprocalRelationshipTargetGroupUsesTempTable() {
813 $groupID = $this->groupCreate();
816 0 => 'relation_type_id',
826 0 => 'relation_target_group',
836 $sql = CRM_Contact_BAO_Query
::getQuery($params);
837 $this->assertStringContainsStringIgnoringCase('INNER JOIN civicrm_tmp_e', $sql, 'Query appears to use temporary table of compiled relationships?');
841 * Test relationship permission clause.
843 * @throws \CRM_Core_Exception
845 public function testRelationshipPermissionClause() {
846 $params = [['relation_type_id', 'IN', ['1_b_a'], 0, 0], ['relation_permission', 'IN', [2], 0, 0]];
847 $sql = CRM_Contact_BAO_Query
::getQuery($params);
848 $this->assertStringContainsString('(civicrm_relationship.is_permission_a_b IN (2))', $sql);
852 * Test Relationship Clause
854 * @throws \CRM_Core_Exception
856 public function testRelationshipClause() {
857 $today = date('Ymd');
858 $from1 = ' FROM civicrm_contact contact_a LEFT JOIN civicrm_relationship ON (civicrm_relationship.contact_id_a = contact_a.id ) LEFT JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_b = contact_b.id )';
859 $from2 = ' FROM civicrm_contact contact_a LEFT JOIN civicrm_relationship ON (civicrm_relationship.contact_id_b = contact_a.id ) LEFT JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_a = contact_b.id )';
861 civicrm_relationship.is_active = 1 AND
862 ( civicrm_relationship.end_date IS NULL OR civicrm_relationship.end_date >= {$today} ) AND
863 ( civicrm_relationship.start_date IS NULL OR civicrm_relationship.start_date <= {$today} )
864 ) AND (contact_b.is_deleted = 0) AND civicrm_relationship.relationship_type_id IN (8) ) AND (contact_a.is_deleted = 0)";
866 civicrm_relationship.is_active = 1 AND
867 ( civicrm_relationship.end_date IS NULL OR civicrm_relationship.end_date >= {$today} ) AND
868 ( civicrm_relationship.start_date IS NULL OR civicrm_relationship.start_date <= {$today} )
869 ) AND (contact_b.is_deleted = 0) AND civicrm_relationship.relationship_type_id IN (8,10) ) AND (contact_a.is_deleted = 0)";
870 // Test Traditional single select format
871 $params1 = [['relation_type_id', '=', '8_a_b', 0, 0]];
872 $query1 = new CRM_Contact_BAO_Query(
873 $params1, ['contact_id'],
874 NULL, TRUE, FALSE, 1,
878 $sql1 = $query1->query();
879 $this->assertLike($from1, $sql1[1]);
880 $this->assertLike($where1, $sql1[2]);
881 // Test single relationship type selected in multiple select.
882 $params2 = [['relation_type_id', 'IN', ['8_a_b'], 0, 0]];
883 $query2 = new CRM_Contact_BAO_Query(
884 $params2, ['contact_id'],
885 NULL, TRUE, FALSE, 1,
889 $sql2 = $query2->query(FALSE);
890 $this->assertLike($from1, $sql2[1]);
891 $this->assertLike($where1, $sql2[2]);
892 // Test multiple relationship types selected.
893 $params3 = [['relation_type_id', 'IN', ['8_a_b', '10_a_b'], 0, 0]];
894 $query3 = new CRM_Contact_BAO_Query(
895 $params3, ['contact_id'],
896 NULL, TRUE, FALSE, 1,
900 $sql3 = $query3->query(FALSE);
901 $this->assertLike($from1, $sql3[1]);
902 $this->assertLike($where2, $sql3[2]);
903 // Test Multiple Relationship type selected where one doesn't actually exist.
904 $params4 = [['relation_type_id', 'IN', ['8_a_b', '10_a_b', '14_a_b'], 0, 0]];
905 $query4 = new CRM_Contact_BAO_Query(
906 $params4, ['contact_id'],
907 NULL, TRUE, FALSE, 1,
911 $sql4 = $query4->query();
912 $this->assertLike($from1, $sql4[1]);
913 $this->assertLike($where2, $sql4[2]);
915 // Test Multiple b to a Relationship type .
916 $params5 = [['relation_type_id', 'IN', ['8_b_a', '10_b_a', '14_b_a'], 0, 0]];
917 $query5 = new CRM_Contact_BAO_Query(
918 $params5, ['contact_id'],
919 NULL, TRUE, FALSE, 1,
923 $sql5 = $query5->query(FALSE);
924 $this->assertLike($from2, $sql5[1]);
925 $this->assertLike($where2, $sql5[2]);
929 * Test we can narrow a group get by status.
931 * @throws \CRM_Core_Exception
933 public function testGetByGroupWithStatus() {
934 $groupID = $this->groupCreate();
935 $this->groupContactCreate($groupID, 3);
936 $groupContactID = $this->callAPISuccessGetSingle('GroupContact', ['group_id' => $groupID, 'options' => ['limit' => 1]])['id'];
937 $this->callAPISuccess('GroupContact', 'create', ['id' => $groupContactID, 'status' => 'Removed']);
938 $queryObj = new CRM_Contact_BAO_Query([['group', '=', $groupID, 0, 0], ['group_contact_status', 'IN', ['Removed' => 1], 0, 0]]);
939 $resultDAO = $queryObj->searchQuery();
940 $this->assertEquals(1, $resultDAO->N
);
942 $queryObj = new CRM_Contact_BAO_Query([['group', '=', $groupID, 0, 0], ['group_contact_status', 'IN', ['Added' => 1], 0, 0]]);
943 $resultDAO = $queryObj->searchQuery();
944 $this->assertEquals(2, $resultDAO->N
);
946 $queryObj = new CRM_Contact_BAO_Query([['group', '=', $groupID, 0, 0]]);
947 $resultDAO = $queryObj->searchQuery();
948 $this->assertEquals(2, $resultDAO->N
);
952 * Test we can narrow a group get by status.
956 public function testGetByGroupWithStatusSmartGroup() {
957 $groupID = $this->smartGroupCreate();
958 // This means they are actually all hard-added, which is fine for this purpose.
959 $this->groupContactCreate($groupID, 3);
960 $groupContactID = $this->callAPISuccessGetSingle('GroupContact', ['group_id' => $groupID, 'options' => ['limit' => 1]])['id'];
961 $this->callAPISuccess('GroupContact', 'create', ['id' => $groupContactID, 'status' => 'Removed']);
963 $queryObj = new CRM_Contact_BAO_Query([['group', '=', $groupID, 0, 0], ['group_contact_status', 'IN', ['Removed' => 1], 0, 0]]);
964 $resultDAO = $queryObj->searchQuery();
965 $this->assertEquals(1, $resultDAO->N
);
967 $queryObj = new CRM_Contact_BAO_Query([['group', 'IS NOT EMPTY', '', 0, 0], ['group_contact_status', 'IN', ['Removed' => 1], 0, 0]]);
968 $resultDAO = $queryObj->searchQuery();
969 $this->assertEquals(1, $resultDAO->N
);
973 * Test the group contact clause does not contain an OR.
975 * The search should return 3 contacts - 2 households in the smart group of
976 * Contact Type = Household and one Individual hard-added to it. The
977 * Household that meets both criteria should be returned once.
981 public function testGroupClause() {
982 $this->householdCreate();
983 $householdID = $this->householdCreate();
984 $individualID = $this->individualCreate();
985 $groupID = $this->smartGroupCreate();
986 $this->callAPISuccess('GroupContact', 'create', ['group_id' => $groupID, 'contact_id' => $individualID, 'status' => 'Added']);
987 $this->callAPISuccess('GroupContact', 'create', ['group_id' => $groupID, 'contact_id' => $householdID, 'status' => 'Added']);
989 // Refresh the cache for test purposes. It would be better to alter to alter the GroupContact add function to add contacts to the cache.
990 CRM_Contact_BAO_GroupContactCache
::invalidateGroupContactCache($groupID);
992 $sql = CRM_Contact_BAO_Query
::getQuery(
993 [['group', 'IN', [$groupID], 0, 0]],
997 $dao = CRM_Core_DAO
::executeQuery($sql);
998 $this->assertEquals(3, $dao->N
);
999 $this->assertFalse(strstr($sql, ' OR '));
1001 $sql = CRM_Contact_BAO_Query
::getQuery(
1002 [['group', 'IN', [$groupID], 0, 0]],
1003 ['contact_id' => 1, 'group' => 1]
1006 $dao = CRM_Core_DAO
::executeQuery($sql);
1007 $this->assertEquals(3, $dao->N
);
1008 $this->assertFalse(strstr($sql, ' OR '), 'Query does not include or');
1009 while ($dao->fetch()) {
1010 $this->assertTrue(($dao->groups
== $groupID ||
$dao->groups
== ',' . $groupID ||
$dao->groups
== $groupID . ',' . $groupID), $dao->groups
. ' includes ' . $groupID);
1015 * CRM-19562 ensure that only ids are used for contact_id searching.
1017 public function testContactIDClause() {
1019 ['mark_x_2', '=', 1, 0, 0],
1020 ['mark_x_foo@example.com', '=', 1, 0, 0],
1022 $returnProperties = [
1025 'do_not_email' => 1,
1028 'display_name' => 1,
1029 'preferred_mail_format' => 1,
1031 $numberOfContacts = 2;
1034 CRM_Contact_BAO_Query
::apiQuery($params, $returnProperties, NULL, NULL, 0, $numberOfContacts);
1036 catch (Exception
$e) {
1037 $this->assertEquals(
1038 'One of parameters (value: foo@example.com) is not of the type Positive',
1041 $this->assertTrue(TRUE);
1044 $this->fail('Test failed for some reason which is not good');
1048 * Test the sorting on the contact ID query works.
1050 * Checking for lack of fatal.
1052 * @param string $sortOrder
1053 * Param reflecting how sort is passed in.
1054 * - 1_d is column 1 descending.
1056 * @dataProvider getSortOptions
1058 public function testContactIDQuery($sortOrder) {
1059 $selector = new CRM_Contact_Selector(NULL, ['radio_ts' => 'ts_all'], NULL, ['sort_name' => 1]);
1060 $selector->contactIDQuery([], $sortOrder);
1064 * Test the sorting on the contact ID query works with a profile search.
1066 * Checking for lack of fatal.
1068 * @throws \CRM_Core_Exception
1070 public function testContactIDQueryProfileSearchResults() {
1071 $profile = $this->callAPISuccess('UFGroup', 'create', ['group_type' => 'Contact', 'name' => 'search', 'title' => 'search']);
1072 $this->callAPISuccess('UFField', 'create', [
1073 'uf_group_id' => $profile['id'],
1074 'field_name' => 'postal_code',
1075 'field_type' => 'Contact',
1076 'in_selector' => TRUE,
1077 'is_searchable' => TRUE,
1078 'label' => 'postal code',
1079 'visibility' => 'Public Pages and Listings',
1081 $selector = new CRM_Contact_Selector(NULL, ['radio_ts' => 'ts_all', 'uf_group_id' => $profile['id']], NULL, ['sort_name' => 1]);
1082 $selector->contactIDQuery([], '2_d');
1086 * Get search options to reflect how a UI search would look.
1090 public function getSortOptions() {
1102 * Test the summary query does not add an acl clause when acls not enabled..
1104 * @throws \CRM_Core_Exception
1106 public function testGetSummaryQueryWithFinancialACLDisabled() {
1107 $this->createContributionsForSummaryQueryTests();
1109 // Test the function directly
1110 $where = $from = NULL;
1111 $queryObject = new CRM_Contact_BAO_Query();
1112 $queryObject->appendFinancialTypeWhereAndFromToQueryStrings($where,
1114 $this->assertEquals(NULL, $where);
1115 $this->assertEquals(NULL, $from);
1117 // Test the function in action
1118 $queryObject = new CRM_Contact_BAO_Query([['contribution_source', '=', 'SSF', '', '']]);
1119 $summary = $queryObject->summaryContribution();
1120 $this->assertEquals([
1122 'avg' => '$ 233.33',
1123 'amount' => '$ 1,400.00',
1128 'amount' => '$ 100.00',
1135 * Test the summary query accurately adds financial acl filters.
1137 * @throws \CRM_Core_Exception
1139 public function testGetSummaryQueryWithFinancialACLEnabled() {
1140 $where = $from = NULL;
1141 $this->createContributionsForSummaryQueryTests();
1142 $this->enableFinancialACLs();
1143 $this->createLoggedInUserWithFinancialACL();
1145 // Test the function directly
1146 $queryObject = new CRM_Contact_BAO_Query();
1147 $queryObject->appendFinancialTypeWhereAndFromToQueryStrings($where,
1149 $donationTypeID = CRM_Core_PseudoConstant
::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation');
1150 $this->assertEquals(
1151 " LEFT JOIN civicrm_line_item li
1152 ON civicrm_contribution.id = li.contribution_id AND
1153 li.entity_table = 'civicrm_contribution' AND li.financial_type_id NOT IN ({$donationTypeID}) ", $from);
1155 // Test the function in action
1156 $queryObject = new CRM_Contact_BAO_Query([['contribution_source', '=', 'SSF', '', '']]);
1157 $summary = $queryObject->summaryContribution();
1158 $this->assertEquals([
1160 'avg' => '$ 200.00',
1161 'amount' => '$ 400.00',
1166 'amount' => '$ 50.00',
1170 $this->disableFinancialACLs();
1174 * Test relative date filters to ensure they generate correct SQL.
1176 * @dataProvider relativeDateFilters
1178 * @param string $filter
1179 * @param string $expectedWhere
1181 * @throws \CRM_Core_Exception
1183 public function testRelativeDateFilters($filter, $expectedWhere) {
1184 $params = [['created_date_relative', '=', $filter, 0, 0]];
1186 $dates = CRM_Utils_Date
::getFromTo($filter, NULL, NULL);
1187 $expectedWhere = str_replace(['date0', 'date1'], [$dates[0], $dates[1]], $expectedWhere);
1189 $query = new CRM_Contact_BAO_Query(
1191 NULL, TRUE, FALSE, 1,
1196 list($select, $from, $where, $having) = $query->query();
1197 $this->assertEquals($expectedWhere, $where);
1201 * Data provider to relative date filter configurations.
1205 public function relativeDateFilters() {
1206 $dataProvider[] = ['this.year', "WHERE ( contact_a.created_date BETWEEN 'date0' AND 'date1' ) AND (contact_a.is_deleted = 0)"];
1207 $dataProvider[] = ['greater.day', "WHERE ( contact_a.created_date >= 'date0' ) AND (contact_a.is_deleted = 0)"];
1208 $dataProvider[] = ['earlier.week', "WHERE ( contact_a.created_date <= 'date1' ) AND (contact_a.is_deleted = 0)"];
1209 return $dataProvider;
1213 * Create contributions to test summary calculations.
1215 * financial type | cancel_date |total_amount| source | line_item_financial_types |number_line_items| line_amounts
1216 * Donation |NULL | 100.00 |SSF | Donation | 1 | 100.00
1217 * Member Dues |NULL | 100.00 |SSF | Member Dues | 1 | 100.00
1218 * Donation |NULL | 300.00 |SSF | Event Fee,Event Fee | 2 | 200.00,100.00
1219 * Donation |NULL | 300.00 |SSF | Event Fee,Donation | 2 | 200.00,100.00
1220 * Donation |NULL | 300.00 |SSF | Donation,Donation | 2 | 200.00,100.00
1221 * Donation |2019-02-13 00:00:00 | 50.00 |SSF | Donation | 1 | 50.00
1222 * Member Dues |2019-02-13 00:00:00 | 50.00 |SSF | Member Dues | 1 | 50.00
1224 * @throws \CRM_Core_Exception
1226 protected function createContributionsForSummaryQueryTests() {
1227 $contactID = $this->individualCreate();
1228 $this->contributionCreate(['contact_id' => $contactID]);
1229 $this->contributionCreate([
1230 'contact_id' => $contactID,
1231 'total_amount' => 100,
1232 'financial_type_id' => 'Member Dues',
1234 $eventFeeType = CRM_Core_PseudoConstant
::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Event Fee');
1235 $this->createContributionWithTwoLineItemsAgainstPriceSet(['contact_id' => $contactID, 'source' => 'SSF']);
1236 $this->createContributionWithTwoLineItemsAgainstPriceSet(['contact_id' => $contactID, 'source' => 'SSF'], [
1237 CRM_Core_PseudoConstant
::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'),
1240 $this->createContributionWithTwoLineItemsAgainstPriceSet(['contact_id' => $contactID, 'source' => 'SSF'], [
1241 CRM_Core_PseudoConstant
::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'),
1242 CRM_Core_PseudoConstant
::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'),
1244 $this->createContributionWithTwoLineItemsAgainstPriceSet(['contact_id' => $contactID, 'source' => 'SSF', 'financial_type_id' => $eventFeeType], [
1245 CRM_Core_PseudoConstant
::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'),
1246 CRM_Core_PseudoConstant
::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'),
1248 $this->contributionCreate([
1249 'contact_id' => $contactID,
1250 'total_amount' => 50,
1251 'contribution_status_id' => 'Cancelled',
1252 'cancel_date' => 'yesterday',
1254 $this->contributionCreate([
1255 'contact_id' => $contactID,
1256 'total_amount' => 50,
1257 'contribution_status_id' => 'Cancelled',
1258 'cancel_date' => 'yesterday',
1259 'financial_type_id' => 'Member Dues',
1264 * Test the options are handled for the qill.
1266 public function testQillOptions() {
1267 $qill = CRM_Contact_BAO_Query
::buildQillForFieldValue('CRM_Activity_BAO_Activity', 'activity_type_id', 2, '=');
1268 $this->assertEquals(['=', 'Phone Call'], $qill);
1270 $qill = CRM_Contact_BAO_Query
::buildQillForFieldValue('CRM_Activity_BAO_Activity', 'priority_id', 2, '=');
1271 $this->assertEquals(['=', 'Normal'], $qill);
1275 * Test tests that a value on 'any entity' with the right metadata will be handled.
1277 * @throws \CRM_Core_Exception
1279 public function testGenericWhereHandling() {
1280 $query = new CRM_Contact_BAO_Query([['suffix_id', '=', 2, 0]]);
1281 $this->assertEquals('contact_a.suffix_id = 2', $query->_where
[0][0]);
1282 $this->assertEquals('Individual Suffix = Sr.', $query->_qill
[0][0]);
1283 $this->assertNotTrue(isset($query->_tables
['civicrm_activity']));
1285 $query = new CRM_Contact_BAO_Query([['prefix_id', '=', 2, 0]]);
1286 $this->assertEquals('contact_a.prefix_id = 2', $query->_where
[0][0]);
1287 $this->assertEquals('Individual Prefix = Ms.', $query->_qill
[0][0]);
1288 $this->assertNotTrue(isset($query->_tables
['civicrm_activity']));
1290 $query = new CRM_Contact_BAO_Query([['gender_id', '=', 2, 0]]);
1291 $this->assertEquals('contact_a.gender_id = 2', $query->_where
[0][0]);
1292 $this->assertEquals('Gender = Male', $query->_qill
[0][0]);
1293 $this->assertNotTrue(isset($query->_tables
['civicrm_activity']));
1295 $query = new CRM_Contact_BAO_Query([['communication_style_id', '=', 2, 0]]);
1296 $this->assertEquals('contact_a.communication_style_id = 2', $query->_where
[0][0]);
1297 $this->assertEquals('Communication Style = Familiar', $query->_qill
[0][0]);
1299 $query = new CRM_Contact_BAO_Query([['communication_style_id', '=', 2, 0]]);
1300 $this->assertEquals('contact_a.communication_style_id = 2', $query->_where
[0][0]);
1301 $this->assertEquals('Communication Style = Familiar', $query->_qill
[0][0]);
1303 $query = new CRM_Contact_BAO_Query([['contact_type', '=', 'Household', 0]]);
1304 $this->assertEquals("contact_a.contact_type = 'Household'", $query->_where
[0][0]);
1305 $this->assertEquals('Contact Type = Household', $query->_qill
[0][0]);
1307 $query = new CRM_Contact_BAO_Query([['on_hold', '=', 0, 0]]);
1308 $this->assertEquals('civicrm_email.on_hold = 0', $query->_where
[0][0]);
1309 $this->assertEquals('On Hold = 0', $query->_qill
[0][0]);
1311 $query = new CRM_Contact_BAO_Query([['on_hold', '=', 1, 0]]);
1312 $this->assertEquals('civicrm_email.on_hold = 1', $query->_where
[0][0]);
1313 $this->assertEquals('On Hold = 1', $query->_qill
[0][0]);
1315 $query = new CRM_Contact_BAO_Query([['world_region', '=', 3, 0]]);
1316 $this->assertEquals('civicrm_worldregion.id = 3', $query->_where
[0][0]);
1317 $this->assertEquals('World Region = Middle East and North Africa', $query->_qill
[0][0]);