DB error "no such field" when executing actions on a sorted contact search result with search profile
Steps to reproduce:
- Create a profile with e.g. the postal code field of the primary address
- Perform a contact search using this profile for displaying the results
- Sort the result by the postal code field
- Tick the "all # records" radio button
- Choose any action to perform on the result (e.g. "Print PDF document")
The result is a "DB error: no such field" for the "1-postal_code" field. Error 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 FROM civicrm_contact contact_a WHERE (contact_a.is_deleted = 0) ORDER BY `1-postal_code` asc, `contact_a`.`id` [nativecode=1054 ** Unknown column '1-postal_code' in 'order clause']
[type] => DB_Error
[user_info] => SELECT contact_a.id as contact_id FROM civicrm_contact contact_a WHERE (contact_a.is_deleted = 0) ORDER BY `1-postal_code` asc, `contact_a`.`id` [nativecode=1054 ** Unknown column '1-postal_code' in 'order clause']
[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 FROM civicrm_contact contact_a WHERE (contact_a.is_deleted = 0) ORDER BY `1-postal_code` asc, `contact_a`.`id` [nativecode=1054 ** Unknown column '1-postal_code' in 'order clause']"]
)
The generated query is missing the JOINs for the civicrm_address
table, resulting in the postal_code field not being part of the result set to be ordered by. The JOIN is not being added to the query in CRM_Contact_BAO_Query::prepareOrderBy()
due to the field being named 1-postal_code
and the code expects the field being named postal_code
for adding relationships to the civicrm_address
table.
The exception is being raised in CRM_Contact_BAO_Query::searchQuery
.
Stepping through the code, this seems to also happen when ordering the search result, but the exception is then being caught in CRM_Contact_Selector::fillupPrevNextCache()
, following a $this->rebuildPreNextCache()
call to re-create the query.