2 require_once 'CiviTest/CiviUnitTestCase.php';
3 require_once 'CiviTest/Contact.php';
6 * Include dataProvider for tests
8 class CRM_Contact_BAO_QueryTest
extends CiviUnitTestCase
{
11 * @return CRM_Contact_BAO_QueryTestDataProvider
13 public function dataProvider() {
14 return new CRM_Contact_BAO_QueryTestDataProvider();
17 public function setUp() {
21 public function tearDown() {
22 $tablesToTruncate = array(
23 'civicrm_group_contact',
25 'civicrm_saved_search',
31 $this->quickCleanup($tablesToTruncate);
35 * Test CRM_Contact_BAO_Query::searchQuery()
36 * @dataProvider dataProvider
42 public function testSearch($fv, $count, $ids, $full) {
43 $op = new PHPUnit_Extensions_Database_Operation_Insert();
44 $op->execute($this->_dbconn
,
45 $this->createFlatXMLDataSet(
46 dirname(__FILE__
) . '/queryDataset.xml'
50 $params = CRM_Contact_BAO_Query
::convertFormValues($fv);
51 $obj = new CRM_Contact_BAO_Query($params);
53 // let's set useGroupBy=true since we are listing contacts here who might belong to
54 // more than one group / tag / notes etc.
55 $obj->_useGroupBy
= TRUE;
57 $dao = $obj->searchQuery();
60 while ($dao->fetch()) {
61 $contacts[] = $dao->contact_id
;
64 sort($contacts, SORT_NUMERIC
);
66 $this->assertEquals($ids, $contacts);
70 * Check that we get a successful result querying for home address.
71 * CRM-14263 search builder failure with search profile & address in criteria
73 public function testSearchProfileHomeCityCRM14263() {
74 $contactID = $this->individualCreate();
75 CRM_Core_Config
::singleton()->defaultSearchProfileID
= 1;
76 $this->callAPISuccess('address', 'create', array(
77 'contact_id' => $contactID,
78 'city' => 'Cool City',
79 'location_type_id' => 1,
90 $returnProperties = array(
92 'contact_sub_type' => 1,
96 $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
98 $resultDAO = $queryObj->searchQuery(0, 0, NULL,
102 $this->assertTrue($resultDAO->fetch());
104 catch (PEAR_Exception
$e) {
105 $err = $e->getCause();
106 $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo
);
112 * Check that we get a successful result querying for home address.
113 * CRM-14263 search builder failure with search profile & address in criteria
115 public function testSearchProfileHomeCityNoResultsCRM14263() {
116 $contactID = $this->individualCreate();
117 CRM_Core_Config
::singleton()->defaultSearchProfileID
= 1;
118 $this->callAPISuccess('address', 'create', array(
119 'contact_id' => $contactID,
120 'city' => 'Cool City',
121 'location_type_id' => 1,
132 $returnProperties = array(
134 'contact_sub_type' => 1,
138 $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
140 $resultDAO = $queryObj->searchQuery(0, 0, NULL,
144 $this->assertFalse($resultDAO->fetch());
146 catch (PEAR_Exception
$e) {
147 $err = $e->getCause();
148 $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo
);
154 * CRM-14263 search builder failure with search profile & address in criteria
155 * We are retrieving primary here - checking the actual sql seems super prescriptive - but since the massive query object has
156 * so few tests detecting any change seems good here :-)
158 public function testSearchProfilePrimaryCityCRM14263() {
159 $contactID = $this->individualCreate();
160 CRM_Core_Config
::singleton()->defaultSearchProfileID
= 1;
161 $this->callAPISuccess('address', 'create', array(
162 'contact_id' => $contactID,
163 'city' => 'Cool City',
164 'location_type_id' => 1,
175 $returnProperties = array(
177 'contact_sub_type' => 1,
180 $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 ";
181 $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
183 $this->assertEquals($expectedSQL, $queryObj->searchQuery(0, 0, NULL,
188 catch (PEAR_Exception
$e) {
189 $err = $e->getCause();
190 $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo
);
196 * Test set up to test calling the query object per GroupContactCache BAO usage.
198 * CRM-17254 ensure that if only the contact_id is required other fields should
201 public function testGroupContactCacheAddSearch() {
202 $returnProperties = array('contact_id');
203 $params = array(array('group', 'IN', array(1), 0, 0));
205 $query = new CRM_Contact_BAO_Query(
206 $params, $returnProperties,
207 NULL, TRUE, FALSE, 1,
212 list($select) = $query->query(FALSE);
213 $this->assertEquals('SELECT contact_a.id as contact_id', $select);
217 * Test smart groups with non-numeric don't fail on range queries.
221 public function testNumericPostal() {
222 $this->individualCreate(array('api.address.create' => array('postal_code' => 5, 'location_type_id' => 'Main')));
223 $this->individualCreate(array('api.address.create' => array('postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main')));
224 $this->individualCreate(array('api.address.create' => array('postal_code' => '4', 'location_type_id' => 'Main')));
225 $this->individualCreate(array('api.address.create' => array('postal_code' => '6', 'location_type_id' => 'Main')));
227 $params = array(array('postal_code_low', '=', 5, 0, 0));
228 CRM_Contact_BAO_Query
::convertFormValues($params);
230 $query = new CRM_Contact_BAO_Query(
231 $params, array('contact_id'),
232 NULL, TRUE, FALSE, 1,
237 $sql = $query->query(FALSE);
238 $result = CRM_Core_DAO
::executeQuery(implode(' ', $sql));
239 $this->assertEquals(2, $result->N
);
241 // We save this as a smart group and then load it. With mysql warnings on & CRM-14720 this
242 // results in mysql warnings & hence fatal errors.
243 /// I was unable to get mysql warnings to activate in the context of the unit tests - but
244 // felt this code still provided a useful bit of coverage as it runs the various queries to load
245 // the group & could generate invalid sql if a bug were introduced.
246 $groupParams = array('title' => 'postal codes', 'formValues' => $params, 'is_active' => 1);
247 $group = CRM_Contact_BAO_Group
::createSmartGroup($groupParams);
248 CRM_Contact_BAO_GroupContactCache
::load($group, TRUE);
252 * Test smart groups with non-numeric don't fail on equal queries.
256 public function testNonNumericEqualsPostal() {
257 $this->individualCreate(array('api.address.create' => array('postal_code' => 5, 'location_type_id' => 'Main')));
258 $this->individualCreate(array('api.address.create' => array('postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main')));
259 $this->individualCreate(array('api.address.create' => array('postal_code' => '4', 'location_type_id' => 'Main')));
260 $this->individualCreate(array('api.address.create' => array('postal_code' => '6', 'location_type_id' => 'Main')));
262 $params = array(array('postal_code', '=', 'EH10 4RB-889', 0, 0));
263 CRM_Contact_BAO_Query
::convertFormValues($params);
265 $query = new CRM_Contact_BAO_Query(
266 $params, array('contact_id'),
267 NULL, TRUE, FALSE, 1,
272 $sql = $query->query(FALSE);
273 $this->assertEquals("WHERE ( civicrm_address.postal_code = 'eh10 4rb-889' ) AND (contact_a.is_deleted = 0)", $sql[2]);
274 $result = CRM_Core_DAO
::executeQuery(implode(' ', $sql));
275 $this->assertEquals(1, $result->N
);