Address location type search errors when using a search profile
Steps to replicate the issue:
- Create a profile for use as a search view. The fields I have used - address name, phone, city, state
- Go to Advanced Search
- Trigger a search using that profile, and select an address location type.
Environment details
CiviCRM ver - 5.37.alpha1 / master
CMS - Drupal 7.78
Error:
DB Error: no such field
$Fatal Error Details = Array
(
[callback] => Array
(
[0] => CRM_Core_Error
[1] => exceptionHandler
)
[code] => -19
[message] => DB Error: no such field
[mode] => 16
[debug_info] => SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name
FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id )
LEFT JOIN civicrm_location_type `1-location_type` ON ( ( `1-address`.location_type_id = `1-location_type`.id ) )
WHERE ( civicrm_address.location_type_id IN (5) ) AND ( 1 ) AND (contact_a.is_deleted = 0) [nativecode=1054 ** Unknown column '1-address.location_type_id' in 'on clause']
[type] => DB_Error
[user_info] => SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name
FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id )
LEFT JOIN civicrm_location_type `1-location_type` ON ( ( `1-address`.location_type_id = `1-location_type`.id ) )
WHERE ( civicrm_address.location_type_id IN (5) ) AND ( 1 ) AND (contact_a.is_deleted = 0) [nativecode=1054 ** Unknown column '1-address.location_type_id' in 'on clause']
[to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name
FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id )
LEFT JOIN civicrm_location_type `1-location_type` ON ( ( `1-address`.location_type_id = `1-location_type`.id ) )
WHERE ( civicrm_address.location_type_id IN (5) ) AND ( 1 ) AND (contact_a.is_deleted = 0) [nativecode=1054 ** Unknown column '1-address.location_type_id' in 'on clause']"]
)
Mar 10 08:47:38 [debug] $backTrace = #0 /Users/monish/src/civicrm-core/CRM/Core/Error.php(942): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 /Users/monish/src/civicrm-core/vendor/pear/pear-core-minimal/src/PEAR.php(922): CRM_Core_Error::exceptionHandler(Object(DB_Error))
#2 /Users/monish/src/civicrm-core/vendor/pear/db/DB.php(998): PEAR_Error->__construct("DB Error: no such field", -19, 16, (Array:2), "SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name\n FROM civicr...")
#3 /Users/monish/src/civicrm-core/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error->__construct(-19, 16, (Array:2), "SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name\n FROM civicr...")
#4 /Users/monish/src/civicrm-core/vendor/pear/pear-core-minimal/src/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -19, 16, (Array:2), "SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name\n FROM civicr...", "DB_Error", TRUE)
#5 /Users/monish/src/civicrm-core/vendor/pear/db/DB/common.php(1928): PEAR->__call("raiseError", (Array:7))
#6 /Users/monish/src/civicrm-core/vendor/pear/db/DB/mysqli.php(936): DB_common->raiseError(-19, NULL, NULL, "SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name\n FROM civicr...", "1054 ** Unknown column '1-address.location_type_id' in 'on clause'")
#7 /Users/monish/src/civicrm-core/vendor/pear/db/DB/mysqli.php(406): DB_mysqli->mysqliRaiseError()
#8 /Users/monish/src/civicrm-core/vendor/pear/db/DB/common.php(1234): DB_mysqli->simpleQuery("SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name\n FROM civicr...")
#9 /Users/monish/src/civicrm-core/packages/DB/DataObject.php(2696): DB_common->query("SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name\n FROM civicr...")
#10 /Users/monish/src/civicrm-core/packages/DB/DataObject.php(1829): DB_DataObject->_query("SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name\n FROM civicr...")
#11 /Users/monish/src/civicrm-core/CRM/Core/DAO.php(454): DB_DataObject->query("SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name\n FROM civicr...")
#12 /Users/monish/src/civicrm-core/CRM/Core/DAO.php(1565): CRM_Core_DAO->query("SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name\n FROM civicr...", TRUE)
#13 /Users/monish/src/civicrm-core/CRM/Contact/BAO/Query.php(4988): CRM_Core_DAO::executeQuery("SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name\n FROM civicr...")
#14 /Users/monish/src/civicrm-core/CRM/Contact/Selector.php(1194): CRM_Contact_BAO_Query->alphabetQuery()
#15 /Users/monish/src/civicrm-core/CRM/Utils/PagerAToZ.php(92): CRM_Contact_Selector->alphabetQuery()
#16 /Users/monish/src/civicrm-core/CRM/Utils/PagerAToZ.php(121): CRM_Utils_PagerAToZ::getDynamicCharacters(Object(CRM_Contact_Selector), FALSE)
#17 /Users/monish/src/civicrm-core/CRM/Utils/PagerAToZ.php(36): CRM_Utils_PagerAToZ::createLinks(Object(CRM_Contact_Selector), NULL, FALSE)
#18 /Users/monish/src/civicrm-core/CRM/Contact/Form/Search.php(853): CRM_Utils_PagerAToZ::getAToZBar(Object(CRM_Contact_Selector), NULL)
#19 /Users/monish/src/civicrm-core/CRM/Contact/Form/Search/Advanced.php(305): CRM_Contact_Form_Search->postProcess()
#20 /Users/monish/src/civicrm-core/CRM/Core/Form.php(526): CRM_Contact_Form_Search_Advanced->postProcess()