4 * Include dataProvider for tests
7 class CRM_Contact_BAO_QueryTest
extends CiviUnitTestCase
{
10 * @return CRM_Contact_BAO_QueryTestDataProvider
12 public function dataProvider() {
13 return new CRM_Contact_BAO_QueryTestDataProvider();
16 public function setUp() {
20 public function tearDown() {
21 $tablesToTruncate = array(
22 'civicrm_group_contact',
24 'civicrm_saved_search',
30 $this->quickCleanup($tablesToTruncate);
34 * Test CRM_Contact_BAO_Query::searchQuery()
35 * @dataProvider dataProvider
41 public function testSearch($fv, $count, $ids, $full) {
42 $op = new PHPUnit_Extensions_Database_Operation_Insert();
43 $op->execute($this->_dbconn
,
44 $this->createFlatXMLDataSet(
45 dirname(__FILE__
) . '/queryDataset.xml'
49 $params = CRM_Contact_BAO_Query
::convertFormValues($fv);
50 $obj = new CRM_Contact_BAO_Query($params);
52 // let's set useGroupBy=true since we are listing contacts here who might belong to
53 // more than one group / tag / notes etc.
54 $obj->_useGroupBy
= TRUE;
56 $dao = $obj->searchQuery();
59 while ($dao->fetch()) {
60 $contacts[] = $dao->contact_id
;
63 sort($contacts, SORT_NUMERIC
);
65 $this->assertEquals($ids, $contacts);
69 * Check that we get a successful result querying for home address.
70 * CRM-14263 search builder failure with search profile & address in criteria
72 public function testSearchProfileHomeCityCRM14263() {
73 $contactID = $this->individualCreate();
74 CRM_Core_Config
::singleton()->defaultSearchProfileID
= 1;
75 $this->callAPISuccess('address', 'create', array(
76 'contact_id' => $contactID,
77 'city' => 'Cool City',
78 'location_type_id' => 1,
89 $returnProperties = array(
91 'contact_sub_type' => 1,
95 $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
97 $resultDAO = $queryObj->searchQuery(0, 0, NULL,
101 $this->assertTrue($resultDAO->fetch());
103 catch (PEAR_Exception
$e) {
104 $err = $e->getCause();
105 $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo
);
111 * Check that we get a successful result querying for home address.
112 * CRM-14263 search builder failure with search profile & address in criteria
114 public function testSearchProfileHomeCityNoResultsCRM14263() {
115 $contactID = $this->individualCreate();
116 CRM_Core_Config
::singleton()->defaultSearchProfileID
= 1;
117 $this->callAPISuccess('address', 'create', array(
118 'contact_id' => $contactID,
119 'city' => 'Cool City',
120 'location_type_id' => 1,
131 $returnProperties = array(
133 'contact_sub_type' => 1,
137 $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
139 $resultDAO = $queryObj->searchQuery(0, 0, NULL,
143 $this->assertFalse($resultDAO->fetch());
145 catch (PEAR_Exception
$e) {
146 $err = $e->getCause();
147 $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo
);
153 * CRM-14263 search builder failure with search profile & address in criteria
154 * We are retrieving primary here - checking the actual sql seems super prescriptive - but since the massive query object has
155 * so few tests detecting any change seems good here :-)
157 public function testSearchProfilePrimaryCityCRM14263() {
158 $contactID = $this->individualCreate();
159 CRM_Core_Config
::singleton()->defaultSearchProfileID
= 1;
160 $this->callAPISuccess('address', 'create', array(
161 'contact_id' => $contactID,
162 'city' => 'Cool City',
163 'location_type_id' => 1,
174 $returnProperties = array(
176 'contact_sub_type' => 1,
179 $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, civicrm_address.city as `city` FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) WHERE ( ( LOWER(civicrm_address.city) = 'cool city' ) ) AND (contact_a.is_deleted = 0) ORDER BY `contact_a`.`sort_name` asc, `contact_a`.`id` ";
180 $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
182 $this->assertEquals($expectedSQL, $queryObj->searchQuery(0, 0, NULL,
187 catch (PEAR_Exception
$e) {
188 $err = $e->getCause();
189 $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo
);
195 * Test set up to test calling the query object per GroupContactCache BAO usage.
197 * CRM-17254 ensure that if only the contact_id is required other fields should
200 public function testGroupContactCacheAddSearch() {
201 $returnProperties = array('contact_id');
202 $params = array(array('group', 'IN', array(1), 0, 0));
204 $query = new CRM_Contact_BAO_Query(
205 $params, $returnProperties,
206 NULL, TRUE, FALSE, 1,
211 list($select) = $query->query(FALSE);
212 $this->assertEquals('SELECT contact_a.id as contact_id', $select);
216 * Test smart groups with non-numeric don't fail on range queries.
220 public function testNumericPostal() {
221 $this->individualCreate(array('api.address.create' => array('postal_code' => 5, 'location_type_id' => 'Main')));
222 $this->individualCreate(array('api.address.create' => array('postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main')));
223 $this->individualCreate(array('api.address.create' => array('postal_code' => '4', 'location_type_id' => 'Main')));
224 $this->individualCreate(array('api.address.create' => array('postal_code' => '6', 'location_type_id' => 'Main')));
226 $params = array(array('postal_code_low', '=', 5, 0, 0));
227 CRM_Contact_BAO_Query
::convertFormValues($params);
229 $query = new CRM_Contact_BAO_Query(
230 $params, array('contact_id'),
231 NULL, TRUE, FALSE, 1,
236 $sql = $query->query(FALSE);
237 $result = CRM_Core_DAO
::executeQuery(implode(' ', $sql));
238 $this->assertEquals(2, $result->N
);
240 // We save this as a smart group and then load it. With mysql warnings on & CRM-14720 this
241 // results in mysql warnings & hence fatal errors.
242 /// I was unable to get mysql warnings to activate in the context of the unit tests - but
243 // felt this code still provided a useful bit of coverage as it runs the various queries to load
244 // the group & could generate invalid sql if a bug were introduced.
245 $groupParams = array('title' => 'postal codes', 'formValues' => $params, 'is_active' => 1);
246 $group = CRM_Contact_BAO_Group
::createSmartGroup($groupParams);
247 CRM_Contact_BAO_GroupContactCache
::load($group, TRUE);
251 * Test smart groups with non-numeric don't fail on equal queries.
255 public function testNonNumericEqualsPostal() {
256 $this->individualCreate(array('api.address.create' => array('postal_code' => 5, 'location_type_id' => 'Main')));
257 $this->individualCreate(array('api.address.create' => array('postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main')));
258 $this->individualCreate(array('api.address.create' => array('postal_code' => '4', 'location_type_id' => 'Main')));
259 $this->individualCreate(array('api.address.create' => array('postal_code' => '6', 'location_type_id' => 'Main')));
261 $params = array(array('postal_code', '=', 'EH10 4RB-889', 0, 0));
262 CRM_Contact_BAO_Query
::convertFormValues($params);
264 $query = new CRM_Contact_BAO_Query(
265 $params, array('contact_id'),
266 NULL, TRUE, FALSE, 1,
271 $sql = $query->query(FALSE);
272 $this->assertEquals("WHERE ( civicrm_address.postal_code = 'eh10 4rb-889' ) AND (contact_a.is_deleted = 0)", $sql[2]);
273 $result = CRM_Core_DAO
::executeQuery(implode(' ', $sql));
274 $this->assertEquals(1, $result->N
);
279 * Test the group contact clause does not contain an OR.
281 * The search should return 3 contacts - 2 households in the smart group of
282 * Contact Type = Household and one Individual hard-added to it. The
283 * Household that meets both criteria should be returned once.
285 public function testGroupClause() {
286 $this->householdCreate();
287 $householdID = $this->householdCreate();
288 $individualID = $this->individualCreate();
289 $groupID = $this->smartGroupCreate();
290 $this->callAPISuccess('GroupContact', 'create', array('group_id' => $groupID, 'contact_id' => $individualID, 'status' => 'Added'));
291 $this->callAPISuccess('GroupContact', 'create', array('group_id' => $groupID, 'contact_id' => $householdID, 'status' => 'Added'));
293 // Refresh the cache for test purposes. It would be better to alter to alter the GroupContact add function to add contacts to the cache.
294 CRM_Contact_BAO_GroupContactCache
::remove($groupID, FALSE);
296 $sql = CRM_Contact_BAO_Query
::getQuery(
297 array(array('group', 'IN', array($groupID), 0, 0)),
301 $dao = CRM_Core_DAO
::executeQuery($sql);
302 $this->assertEquals(3, $dao->N
);
303 $this->assertFalse(strstr($sql, ' OR '));
305 $sql = CRM_Contact_BAO_Query
::getQuery(
306 array(array('group', 'IN', array($groupID), 0, 0)),
307 array('contact_id' => 1, 'group' => 1)
310 $dao = CRM_Core_DAO
::executeQuery($sql);
311 $this->assertEquals(3, $dao->N
);
312 $this->assertFalse(strstr($sql, ' OR '), 'Query does not include or');
313 while ($dao->fetch()) {
314 $this->assertTrue(($dao->groups
== $groupID ||
$dao->groups
== ',' . $groupID), $dao->groups
. ' includes ' . $groupID);
319 * CRM-19562 ensure that only ids are used for contactid searching.
321 public function testContactIDClause() {
323 array("mark_x_93980", "=", 1, 0, 0),
324 array("mark_x_foo@example.com", "=", 1, 0, 0),
326 $returnProperties = array(
333 "preferred_mail_format" => 1,
335 $numberofContacts = 2;
336 $query = new CRM_Contact_BAO_Query($params, $returnProperties);
338 $query->apiQuery($params, $returnProperties, NULL, NULL, 0, $numberofContacts);
340 catch (Exception
$e) {
341 $this->assertEquals("A fatal error was triggered: One of parameters (value: foo@example.com) is not of the type Positive",