GitLab

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.