Skip to content

GitLab

  • Projects
  • Groups
  • Snippets
  • Help
    • Loading...
  • Help
    • Help
    • Support
    • Community forum
    • Submit feedback
    • Contribute to GitLab
  • Sign in
C
Core
  • Project overview
    • Project overview
    • Details
    • Activity
    • Releases
  • Repository
    • Repository
    • Files
    • Commits
    • Branches
    • Tags
    • Contributors
    • Graph
    • Compare
  • Issues 983
    • Issues 983
    • List
    • Boards
    • Labels
    • Service Desk
    • Milestones
  • Operations
    • Operations
    • Incidents
  • Analytics
    • Analytics
    • Repository
    • Value Stream
  • Wiki
    • Wiki
  • Snippets
    • Snippets
  • Members
    • Members
  • Activity
  • Graph
  • Create a new issue
  • Commits
  • Issue Boards
Collapse sidebar
  • Development
  • Core
  • Issues
  • #502

Closed
Open
Opened Nov 02, 2018 by jensschuppe@jensschuppe

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.

To upload designs, you'll need to enable LFS and have an admin enable hashed storage. More information
Assignee
Assign to
5.13.0
Milestone
5.13.0 (Past due)
Assign milestone
Time tracking
None
Due date
None
Reference: dev/core#502