Searching by phone in Advanced Search with a non-default Search Profile returns DB Error: no such field
Overview
If a user tries to search by a phone number via Advanced Search and uses a non-default Search Profile that includes a phone number and at least one location field (i.e. city), then the search produces an error with the message DB error: no such field
.
Reproduction steps
- Go to Administer > Custom Data and Screens > Profiles
- Click Add Profile
- Enter a Profile Name and check the box next/to the left of Search Views
- Add fields to your Profile. At a minimum, be sure to add Contacts > Phone > Primary > Phone and one address field, such as Contacts > City > Primary. (I also included first and last name as fields)
- Go to Administer > Custom Data and Screens > Search Preferences. Select the name of your test Profile from the drop down menu for the Default Contact Search Profile (roughly halfway down on the settings page).
- Hover over Search on the navbar and click Advanced Search
- Enter a phone number (or even just a number/partial number) and click Search
- At this point, the user will receive an error that states
DB error: no such field
Current behaviour
Instead of returning the results when a user searches by phone number when a non-default Search Profile includes at least one address field, the user receives an error message that the field does not exist. From the SQL displayed in the error message, this happens because the query does not include a statement to join on the address table, but references it in another join statement in the ON
clause.
| Type | DB_Error |
|-----------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Code | -19 |
| Message | DB Error: no such field |
| Mode | 16 |
| UserInfo | SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name FROM civicrm_contact contact_a LEFT JOIN civicrm_phone `1-phone-1` ON contact_a.id = `1-phone-1`.contact_id AND ( `1-phone-1`.phone_type_id = '1' OR `1-phone-1`.phone_type_id IS NULL ) AND ( `1-phone-1`.is_primary = 1 ) LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1) LEFT JOIN civicrm_location_type `1-location_type` ON ( ( `1-address`.location_type_id = `1-location_type`.id ) ) WHERE ( civicrm_phone.phone_numeric LIKE '%301%' ) AND ( 1 ) AND (contact_a.is_deleted = 0) [nativecode=1054 ** Unknown column '1-address.location_type_id' in 'on clause'] |
| DebugInfo | SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name FROM civicrm_contact contact_a LEFT JOIN civicrm_phone `1-phone-1` ON contact_a.id = `1-phone-1`.contact_id AND ( `1-phone-1`.phone_type_id = '1' OR `1-phone-1`.phone_type_id IS NULL ) AND ( `1-phone-1`.is_primary = 1 ) LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1) LEFT JOIN civicrm_location_type `1-location_type` ON ( ( `1-address`.location_type_id = `1-location_type`.id ) ) WHERE ( civicrm_phone.phone_numeric LIKE '%301%' ) AND ( 1 ) AND (contact_a.is_deleted = 0) [nativecode=1054 ** Unknown column '1-address.location_type_id' in 'on clause'] |
Expected behaviour
When a user searches by phone number via Advanced Search they should not receive an error and instead be directed to the results page of the search.
Environment information
- CiviCRM: _Master/5.54.alpha1
Comments
PR #24450 proposes a fix for this issue