Searching by any Address fields with location type other than primary throw DB error
Steps to replicate:
- Go to
Search Builder
- Choose Contact >> Street Address (or any address field) >> Home (as location type) >> NOT EMPTY (or any other operator)
- Submit
Throw DB Error:
Database Error Code: Unknown column 'Home-location_type.id' in 'field list', 1054
Additional Details:
Array
(
[callback] => Array
(
[0] => CRM_Core_Error
[1] => handle
)
[code] => -19
[message] => DB Error: no such field
[mode] => 16
[debug_info] => 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`, `Home-location_type`.id as `Home-location_type_id`, `Home-location_type`.name as `Home-location_type`, `Home-address`.id as `Home-address_id`, `Home-address`.street_address as `Home-street_address` FROM civicrm_contact contact_a LEFT JOIN civicrm_address `Home-address` ON ( contact_a.id = `Home-address`.contact_id ) and `Home-address`.location_type_id = 1 WHERE ( ( (NULLIF(LOWER(`Home-address`.street_address), '') IS NOT NULL) ) ) AND (contact_a.is_deleted = 0) GROUP BY contact_a.id ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc [nativecode=1054 ** Unknown column 'Home-location_type.id' in 'field list']
[type] => DB_Error
[user_info] => 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`, `Home-location_type`.id as `Home-location_type_id`, `Home-location_type`.name as `Home-location_type`, `Home-address`.id as `Home-address_id`, `Home-address`.street_address as `Home-street_address` FROM civicrm_contact contact_a LEFT JOIN civicrm_address `Home-address` ON ( contact_a.id = `Home-address`.contact_id ) and `Home-address`.location_type_id = 1 WHERE ( ( (NULLIF(LOWER(`Home-address`.street_address), '') IS NOT NULL) ) ) AND (contact_a.is_deleted = 0) GROUP BY contact_a.id ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc [nativecode=1054 ** Unknown column 'Home-location_type.id' in 'field list']
[to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="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`, `Home-location_type`.id as `Home-location_type_id`, `Home-location_type`.name as `Home-location_type`, `Home-address`.id as `Home-address_id`, `Home-address`.street_address as `Home-street_address` FROM civicrm_contact contact_a LEFT JOIN civicrm_address `Home-address` ON ( contact_a.id = `Home-address`.contact_id ) and `Home-address`.location_type_id = 1 WHERE ( ( (NULLIF(LOWER(`Home-address`.street_address), '') IS NOT NULL) ) ) AND (contact_a.is_deleted = 0) GROUP BY contact_a.id ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc [nativecode=1054 ** Unknown column 'Home-location_type.id' in 'field list']"]
)