From 2baa1e00ccc88525eb455aff6aeb5655a0f81ce0 Mon Sep 17 00:00:00 2001 From: eileenmcnaugton Date: Sat, 22 Aug 2015 19:24:17 +1200 Subject: [PATCH] CRM-17023, CRM-10687, CRM-12326 add unit tests to quicksearch Preparatory to fixing very slow query, try to clarify & lock in quicksearch behaviour --- api/v3/Contact.php | 62 ++++++++++++++++++---- tests/phpunit/api/v3/ContactTest.php | 79 +++++++++++++++++++++++++++- 2 files changed, 128 insertions(+), 13 deletions(-) diff --git a/api/v3/Contact.php b/api/v3/Contact.php index 6eea28270e..a49bcbad17 100644 --- a/api/v3/Contact.php +++ b/api/v3/Contact.php @@ -622,6 +622,37 @@ function _civicrm_api3_greeting_format_params($params) { } } +/** + * Adjust Metadata for Get action. + * + * @param array $params + * Array of parameters determined by getfields. + */ +function _civicrm_api3_contact_getquick_spec(&$params) { + $params['name']['api.required'] = TRUE; + $params['name']['title'] = ts('String to search on'); + $params['name']['type'] = CRM_Utils_Type::T_STRING; + $params['field']['type'] = CRM_Utils_Type::T_STRING; + $params['field']['title'] = ts('Field to search on'); + $params['field']['options'] = array( + '', + 'id', + 'contact_id', + 'external_identifier', + 'first_name', + 'last_name', + 'job_title', + 'postal_code', + 'street_address', + 'email', + 'city', + 'phone_numeric', + ); + $params['table_name']['type'] = CRM_Utils_Type::T_STRING; + $params['table_name']['title'] = ts('Table alias to search on'); + $params['table_name']['api.default'] = 'cc'; +} + /** * Old Contact quick search api. * @@ -633,9 +664,8 @@ function _civicrm_api3_greeting_format_params($params) { * @throws \API_Exception */ function civicrm_api3_contact_getquick($params) { - civicrm_api3_verify_mandatory($params, NULL, array('name')); $name = CRM_Utils_Type::escape(CRM_Utils_Array::value('name', $params), 'String'); - + $table_name = CRM_Utils_String::munge($params['table_name']); // get the autocomplete options from settings $acpref = explode(CRM_Core_DAO::VALUE_SEPARATOR, CRM_Core_BAO_Setting::getItem(CRM_Core_BAO_Setting::SYSTEM_PREFERENCES_NAME, @@ -665,6 +695,10 @@ function civicrm_api3_contact_getquick($params) { $list[] = $searchField; } } + else { + // Set field name to first name for exact match checking. + $field_name = 'sort_name'; + } $select = $actualSelectElements = array('sort_name'); $where = ''; @@ -805,7 +839,6 @@ function civicrm_api3_contact_getquick($params) { //CRM-10687 if (!empty($params['field_name']) && !empty($params['table_name'])) { - $table_name = CRM_Utils_String::munge($params['table_name']); $whereClause = " WHERE ( $table_name.$field_name LIKE '$strSearch') {$where}"; $exactWhereClause = " WHERE ( $table_name.$field_name = '$name') {$where}"; // Search by id should be exact @@ -855,24 +888,31 @@ function civicrm_api3_contact_getquick($params) { $query = " SELECT DISTINCT(id), data, sort_name {$selectAliases}, exactFirst FROM ( - ( SELECT 0 as exactFirst, cc.id as id, CONCAT_WS( ' :: ', {$actualSelectElements} ) as data {$select} + ( SELECT IF($table_name.$field_name = '{$name}', 0, 1) as exactFirst, cc.id as id, CONCAT_WS( ' :: ', + {$actualSelectElements} ) + as data + {$select} FROM civicrm_contact cc {$from} {$aclFrom} {$additionalFrom} {$includeEmailFrom} {$exactWhereClause} LIMIT 0, {$limit} ) - UNION - ( SELECT 1 as exactFirst, cc.id as id, CONCAT_WS( ' :: ', {$actualSelectElements} ) as data {$select} + "; + if ($whereClause != $exactWhereClause) { + $query .= "UNION + ( SELECT IF($table_name.$field_name = '{$name}', 0, 1) as exactFirst, cc.id as id, CONCAT_WS( ' :: ', {$actualSelectElements} ) as data {$select} FROM civicrm_contact cc {$from} {$aclFrom} {$additionalFrom} {$includeEmailFrom} {$whereClause} {$orderByInner} - LIMIT 0, {$limit} ) -) t -{$orderByOuter} -LIMIT 0, {$limit} - "; + LIMIT 0, {$limit} )"; + } + $query .=") t + {$orderByOuter} + LIMIT 0, {$limit} + "; + // send query to hook to be modified if needed CRM_Utils_Hook::contactListQuery($query, $name, diff --git a/tests/phpunit/api/v3/ContactTest.php b/tests/phpunit/api/v3/ContactTest.php index 3759909cf8..489b32f818 100644 --- a/tests/phpunit/api/v3/ContactTest.php +++ b/tests/phpunit/api/v3/ContactTest.php @@ -2150,13 +2150,78 @@ class api_v3_ContactTest extends CiviUnitTestCase { /** * Test that getquick returns contacts with an exact first name match first. + * + * The search string 'b' & 'bob' both return ordered by sort_name if includeOrderByClause + * is true (default) but if it is false then matches are returned in ID order. */ public function testGetQuickExactFirst() { $this->getQuickSearchSampleData(); $result = $this->callAPISuccess('contact', 'getquick', array('name' => 'b')); $this->assertEquals('A Bobby, Bobby', $result['values'][0]['sort_name']); + $this->assertEquals('B Bobby, Bobby', $result['values'][1]['sort_name']); $result = $this->callAPISuccess('contact', 'getquick', array('name' => 'bob')); $this->assertEquals('A Bobby, Bobby', $result['values'][0]['sort_name']); + $this->assertEquals('B Bobby, Bobby', $result['values'][1]['sort_name']); + $this->callAPISuccess('Setting', 'create', array('includeOrderByClause' => FALSE)); + $result = $this->callAPISuccess('contact', 'getquick', array('name' => 'bob')); + $this->assertEquals('Bob, Bob', $result['values'][0]['sort_name']); + $this->assertEquals('A Bobby, Bobby', $result['values'][1]['sort_name']); + } + + /** + * Test that getquick returns contacts with an exact first name match first. + */ + public function testGetQuickExternalID() { + $this->getQuickSearchSampleData(); + $result = $this->callAPISuccess('contact', 'getquick', array( + 'name' => 'b', + 'field_name' => 'external_identifier', + 'table_name' => 'cc', + )); + $this->assertEquals(0, $result['count']); + $result = $this->callAPISuccess('contact', 'getquick', array( + 'name' => 'abc', + 'field_name' => 'external_identifier', + 'table_name' => 'cc', + )); + $this->assertEquals(1, $result['count']); + $this->assertEquals('Bob, Bob', $result['values'][0]['sort_name']); + } + + /** + * Test that getquick returns contacts with an exact first name match first. + */ + public function testGetQuickID() { + $this->getQuickSearchSampleData(); + $result = $this->callAPISuccess('contact', 'getquick', array( + 'name' => 2, + 'field_name' => 'id', + 'table_name' => 'cc', + )); + $this->assertEquals(1, $result['count']); + $this->assertEquals('A Bobby, Bobby', $result['values'][0]['sort_name']); + $result = $this->callAPISuccess('contact', 'getquick', array( + 'name' => 2, + 'field_name' => 'contact_id', + 'table_name' => 'cc', + )); + $this->assertEquals(1, $result['count']); + $this->assertEquals('A Bobby, Bobby', $result['values'][0]['sort_name']); + } + + /** + * Test that getquick returns contacts with an exact first name match first. + */ + public function testGetQuickFirstName() { + $this->getQuickSearchSampleData(); + $result = $this->callAPISuccess('contact', 'getquick', array( + 'name' => 'Bob', + 'field_name' => 'first_name', + 'table_name' => 'cc', + )); + $this->assertEquals('Bob, Bob', $result['values'][0]['sort_name']); + $this->assertEquals('K Bobby, Bob', $result['values'][1]['sort_name']); + $this->assertEquals('A Bobby, Bobby', $result['values'][2]['sort_name']); $this->callAPISuccess('Setting', 'create', array('includeOrderByClause' => FALSE)); $result = $this->callAPISuccess('contact', 'getquick', array('name' => 'bob')); $this->assertEquals('Bob, Bob', $result['values'][0]['sort_name']); @@ -2167,8 +2232,18 @@ class api_v3_ContactTest extends CiviUnitTestCase { */ public function getQuickSearchSampleData() { $contacts = array( - array('first_name' => 'Bob', 'last_name' => 'Bob'), - array('first_name' => 'Bobby', 'last_name' => 'A Bobby'), + array('first_name' => 'Bob', 'last_name' => 'Bob', 'external_identifier' => 'abc'), + array('first_name' => 'Bobby', 'last_name' => 'A Bobby', 'external_identifier' => 'abcd'), + array('first_name' => 'Bobby', 'last_name' => 'B Bobby', 'external_identifier' => 'bcd'), + array('first_name' => 'Bobby', 'last_name' => 'C Bobby', 'external_identifier' => 'bcde'), + array('first_name' => 'Bobby', 'last_name' => 'D Bobby', 'external_identifier' => 'efg'), + array('first_name' => 'Bobby', 'last_name' => 'E Bobby', 'external_identifier' => 'hij'), + array('first_name' => 'Bobby', 'last_name' => 'F Bobby', 'external_identifier' => 'klm'), + array('first_name' => 'Bobby', 'last_name' => 'G Bobby', 'external_identifier' => 'nop'), + array('first_name' => 'Bobby', 'last_name' => 'H Bobby', 'external_identifier' => 'qrs'), + array('first_name' => 'Bobby', 'last_name' => 'I Bobby'), + array('first_name' => 'Bobby', 'last_name' => 'J Bobby'), + array('first_name' => 'Bob', 'last_name' => 'K Bobby', 'external_identifier' => 'bcdef'), ); foreach ($contacts as $type => $contact) { $contact['contact_type'] = 'Individual'; -- 2.25.1