The one place for your designs
To enable design management, you'll need to meet the requirements. If you need help, reach out to our support team for assistance.
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.