3 require_once 'CiviTest/Contact.php';
6 * Include dataProvider for tests
9 class CRM_Contact_BAO_QueryTest
extends CiviUnitTestCase
{
12 * @return CRM_Contact_BAO_QueryTestDataProvider
14 public function dataProvider() {
15 return new CRM_Contact_BAO_QueryTestDataProvider();
18 public function setUp() {
22 public function tearDown() {
23 $tablesToTruncate = array(
24 'civicrm_group_contact',
26 'civicrm_saved_search',
32 $this->quickCleanup($tablesToTruncate);
36 * Test CRM_Contact_BAO_Query::searchQuery()
37 * @dataProvider dataProvider
43 public function testSearch($fv, $count, $ids, $full) {
44 $op = new PHPUnit_Extensions_Database_Operation_Insert();
45 $op->execute($this->_dbconn
,
46 $this->createFlatXMLDataSet(
47 dirname(__FILE__
) . '/queryDataset.xml'
51 $params = CRM_Contact_BAO_Query
::convertFormValues($fv);
52 $obj = new CRM_Contact_BAO_Query($params);
54 // let's set useGroupBy=true since we are listing contacts here who might belong to
55 // more than one group / tag / notes etc.
56 $obj->_useGroupBy
= TRUE;
58 $dao = $obj->searchQuery();
61 while ($dao->fetch()) {
62 $contacts[] = $dao->contact_id
;
65 sort($contacts, SORT_NUMERIC
);
67 $this->assertEquals($ids, $contacts);
71 * Check that we get a successful result querying for home address.
72 * CRM-14263 search builder failure with search profile & address in criteria
74 public function testSearchProfileHomeCityCRM14263() {
75 $contactID = $this->individualCreate();
76 CRM_Core_Config
::singleton()->defaultSearchProfileID
= 1;
77 $this->callAPISuccess('address', 'create', array(
78 'contact_id' => $contactID,
79 'city' => 'Cool City',
80 'location_type_id' => 1,
91 $returnProperties = array(
93 'contact_sub_type' => 1,
97 $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
99 $resultDAO = $queryObj->searchQuery(0, 0, NULL,
103 $this->assertTrue($resultDAO->fetch());
105 catch (PEAR_Exception
$e) {
106 $err = $e->getCause();
107 $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo
);
113 * Check that we get a successful result querying for home address.
114 * CRM-14263 search builder failure with search profile & address in criteria
116 public function testSearchProfileHomeCityNoResultsCRM14263() {
117 $contactID = $this->individualCreate();
118 CRM_Core_Config
::singleton()->defaultSearchProfileID
= 1;
119 $this->callAPISuccess('address', 'create', array(
120 'contact_id' => $contactID,
121 'city' => 'Cool City',
122 'location_type_id' => 1,
133 $returnProperties = array(
135 'contact_sub_type' => 1,
139 $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
141 $resultDAO = $queryObj->searchQuery(0, 0, NULL,
145 $this->assertFalse($resultDAO->fetch());
147 catch (PEAR_Exception
$e) {
148 $err = $e->getCause();
149 $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo
);
155 * CRM-14263 search builder failure with search profile & address in criteria
156 * We are retrieving primary here - checking the actual sql seems super prescriptive - but since the massive query object has
157 * so few tests detecting any change seems good here :-)
159 public function testSearchProfilePrimaryCityCRM14263() {
160 $contactID = $this->individualCreate();
161 CRM_Core_Config
::singleton()->defaultSearchProfileID
= 1;
162 $this->callAPISuccess('address', 'create', array(
163 'contact_id' => $contactID,
164 'city' => 'Cool City',
165 'location_type_id' => 1,
176 $returnProperties = array(
178 'contact_sub_type' => 1,
181 $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` ";
182 $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
184 $this->assertEquals($expectedSQL, $queryObj->searchQuery(0, 0, NULL,
189 catch (PEAR_Exception
$e) {
190 $err = $e->getCause();
191 $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo
);
197 * Test set up to test calling the query object per GroupContactCache BAO usage.
199 * CRM-17254 ensure that if only the contact_id is required other fields should
202 public function testGroupContactCacheAddSearch() {
203 $returnProperties = array('contact_id');
204 $params = array(array('group', 'IN', array(1), 0, 0));
206 $query = new CRM_Contact_BAO_Query(
207 $params, $returnProperties,
208 NULL, TRUE, FALSE, 1,
213 list($select) = $query->query(FALSE);
214 $this->assertEquals('SELECT contact_a.id as contact_id', $select);
218 * Test smart groups with non-numeric don't fail on range queries.
222 public function testNumericPostal() {
223 $this->individualCreate(array('api.address.create' => array('postal_code' => 5, 'location_type_id' => 'Main')));
224 $this->individualCreate(array('api.address.create' => array('postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main')));
225 $this->individualCreate(array('api.address.create' => array('postal_code' => '4', 'location_type_id' => 'Main')));
226 $this->individualCreate(array('api.address.create' => array('postal_code' => '6', 'location_type_id' => 'Main')));
228 $params = array(array('postal_code_low', '=', 5, 0, 0));
229 CRM_Contact_BAO_Query
::convertFormValues($params);
231 $query = new CRM_Contact_BAO_Query(
232 $params, array('contact_id'),
233 NULL, TRUE, FALSE, 1,
238 $sql = $query->query(FALSE);
239 $result = CRM_Core_DAO
::executeQuery(implode(' ', $sql));
240 $this->assertEquals(2, $result->N
);
242 // We save this as a smart group and then load it. With mysql warnings on & CRM-14720 this
243 // results in mysql warnings & hence fatal errors.
244 /// I was unable to get mysql warnings to activate in the context of the unit tests - but
245 // felt this code still provided a useful bit of coverage as it runs the various queries to load
246 // the group & could generate invalid sql if a bug were introduced.
247 $groupParams = array('title' => 'postal codes', 'formValues' => $params, 'is_active' => 1);
248 $group = CRM_Contact_BAO_Group
::createSmartGroup($groupParams);
249 CRM_Contact_BAO_GroupContactCache
::load($group, TRUE);
253 * Test smart groups with non-numeric don't fail on equal queries.
257 public function testNonNumericEqualsPostal() {
258 $this->individualCreate(array('api.address.create' => array('postal_code' => 5, 'location_type_id' => 'Main')));
259 $this->individualCreate(array('api.address.create' => array('postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main')));
260 $this->individualCreate(array('api.address.create' => array('postal_code' => '4', 'location_type_id' => 'Main')));
261 $this->individualCreate(array('api.address.create' => array('postal_code' => '6', 'location_type_id' => 'Main')));
263 $params = array(array('postal_code', '=', 'EH10 4RB-889', 0, 0));
264 CRM_Contact_BAO_Query
::convertFormValues($params);
266 $query = new CRM_Contact_BAO_Query(
267 $params, array('contact_id'),
268 NULL, TRUE, FALSE, 1,
273 $sql = $query->query(FALSE);
274 $this->assertEquals("WHERE ( civicrm_address.postal_code = 'eh10 4rb-889' ) AND (contact_a.is_deleted = 0)", $sql[2]);
275 $result = CRM_Core_DAO
::executeQuery(implode(' ', $sql));
276 $this->assertEquals(1, $result->N
);