Uploaded image for project: 'CiviCRM'
  1. CiviCRM
  2. CRM-17377

In profile search results, unable to sort by county, get a db error

    Details

    • Versioning Impact:
      Patch (backwards-compatible bug fixes)
    • Documentation Required?:
      None
    • Funding Source:
      Needs Funding

      Description

      Here is the error that takes place if you try to click the County column on the search results so it sorts by County:
      Oct 11 18:22:19 [info] $Fatal 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, contact_a.contact_type as contact_type, contact_a.contact_sub_type as contact_sub_type, contact_a.sort_name as sort_name, IF ( contact_a.contact_type = 'Individual', NULL, contact_a.organization_name ) as organization_name, civicrm_address.id as address_id, civicrm_address.state_province_id as state_province_id, `1-location_type`.id as `1-location_type_id`, `1-location_type`.name as `1-location_type`, `1-address`.id as `1-address_id`, `1-address`.city as `1-city`, `1-address`.state_province_id as `1-state_province_id`, `1-address`.street_address as `1-street_address`, `1-address`.county_id as `1-county_id`, `1-address`.postal_code as `1-postal_code`, `1-phone-1`.id as `1-phone-1_id`, `1-phone-1`.phone as `1-phone-1`, `1-address`.country_id as `1-country_id`, `Main-location_type`.id as `Main-location_type_id`, `Main-location_type`.name as `Main-location_type`, `Main-phone-1`.id as `Main-phone-1_id`, `Main-phone-1`.phone as `Main-phone-1`, civicrm_value_school_info_2.id as civicrm_value_school_info_2_id, civicrm_value_school_info_2.school_class_24 as custom_24, civicrm_value_school_info_2.last_visit_25 as custom_25, civicrm_value_school_info_2.coach_name_26 as custom_26, civicrm_value_school_info_2.coach_phone_27 as custom_27, civicrm_value_school_info_2.coach_email_28 as custom_28, civicrm_value_school_info_2.ets_number_42 as custom_42, civicrm_value_school_info_2.coach_cell_50 as custom_50, civicrm_value_school_info_2.hudl_link_60 as custom_60 FROM civicrm_contact contact_a
      LEFT JOIN civicrm_address `1-address` ON (`1-address`.contact_id = contact_a.id AND `1-address`.is_primary = 1) LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 )
      LEFT JOIN civicrm_phone `1-phone-1` ON contact_a.id = `1-phone-1`.contact_id AND `1-phone-1`.is_primary = 1
      LEFT JOIN civicrm_phone `Main-phone-1` ON contact_a.id = `Main-phone-1`.contact_id AND `Main-phone-1`.location_type_id = 3 AND ( `Main-phone-1`.phone_type_id = '1' OR `Main-phone-1`.phone_type_id IS NULL )
      LEFT JOIN civicrm_location_type `1-location_type` ON ( ( `1-address`.location_type_id = `1-location_type`.id ) OR ( `1-phone-1`.location_type_id = `1-location_type`.id ) )
      LEFT JOIN civicrm_location_type `Main-location_type` ON ( ( `Main-phone-1`.location_type_id = `Main-location_type`.id ) ) LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_3` ON contact_a.id = `civicrm_group_contact_cache_3`.contact_id
      LEFT JOIN civicrm_value_school_info_2 ON civicrm_value_school_info_2.entity_id = `contact_a`.id WHERE ( civicrm_address.state_province_id = 1019 AND `civicrm_group_contact_cache_3`.group_id IN (3) ) AND (contact_a.is_deleted = 0) AND contact_a.is_deleted = 0 GROUP BY contact_a.id ORDER BY `1-county` asc, contact_a.id LIMIT 0, 50 [nativecode=1054 ** Unknown column '1-county' in 'order clause']
      [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, IF ( contact_a.contact_type = 'Individual', NULL, contact_a.organization_name ) as organization_name, civicrm_address.id as address_id, civicrm_address.state_province_id as state_province_id, `1-location_type`.id as `1-location_type_id`, `1-location_type`.name as `1-location_type`, `1-address`.id as `1-address_id`, `1-address`.city as `1-city`, `1-address`.state_province_id as `1-state_province_id`, `1-address`.street_address as `1-street_address`, `1-address`.county_id as `1-county_id`, `1-address`.postal_code as `1-postal_code`, `1-phone-1`.id as `1-phone-1_id`, `1-phone-1`.phone as `1-phone-1`, `1-address`.country_id as `1-country_id`, `Main-location_type`.id as `Main-location_type_id`, `Main-location_type`.name as `Main-location_type`, `Main-phone-1`.id as `Main-phone-1_id`, `Main-phone-1`.phone as `Main-phone-1`, civicrm_value_school_info_2.id as civicrm_value_school_info_2_id, civicrm_value_school_info_2.school_class_24 as custom_24, civicrm_value_school_info_2.last_visit_25 as custom_25, civicrm_value_school_info_2.coach_name_26 as custom_26, civicrm_value_school_info_2.coach_phone_27 as custom_27, civicrm_value_school_info_2.coach_email_28 as custom_28, civicrm_value_school_info_2.ets_number_42 as custom_42, civicrm_value_school_info_2.coach_cell_50 as custom_50, civicrm_value_school_info_2.hudl_link_60 as custom_60 FROM civicrm_contact contact_a
      LEFT JOIN civicrm_address `1-address` ON (`1-address`.contact_id = contact_a.id AND `1-address`.is_primary = 1) LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 )
      LEFT JOIN civicrm_phone `1-phone-1` ON contact_a.id = `1-phone-1`.contact_id AND `1-phone-1`.is_primary = 1
      LEFT JOIN civicrm_phone `Main-phone-1` ON contact_a.id = `Main-phone-1`.contact_id AND `Main-phone-1`.location_type_id = 3 AND ( `Main-phone-1`.phone_type_id = '1' OR `Main-phone-1`.phone_type_id IS NULL )
      LEFT JOIN civicrm_location_type `1-location_type` ON ( ( `1-address`.location_type_id = `1-location_type`.id ) OR ( `1-phone-1`.location_type_id = `1-location_type`.id ) )
      LEFT JOIN civicrm_location_type `Main-location_type` ON ( ( `Main-phone-1`.location_type_id = `Main-location_type`.id ) ) LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_3` ON contact_a.id = `civicrm_group_contact_cache_3`.contact_id
      LEFT JOIN civicrm_value_school_info_2 ON civicrm_value_school_info_2.entity_id = `contact_a`.id WHERE ( civicrm_address.state_province_id = 1019 AND `civicrm_group_contact_cache_3`.group_id IN (3) ) AND (contact_a.is_deleted = 0) AND contact_a.is_deleted = 0 GROUP BY contact_a.id ORDER BY `1-county` asc, contact_a.id LIMIT 0, 50 [nativecode=1054 ** Unknown column '1-county' 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, contact_a.contact_type as contact_type, contact_a.contact_sub_type as contact_sub_type, contact_a.sort_name as sort_name, IF ( contact_a.contact_type = 'Individual', NULL, contact_a.organization_name ) as organization_name, civicrm_address.id as address_id, civicrm_address.state_province_id as state_province_id, `1-location_type`.id as `1-location_type_id`, `1-location_type`.name as `1-location_type`, `1-address`.id as `1-address_id`, `1-address`.city as `1-city`, `1-address`.state_province_id as `1-state_province_id`, `1-address`.street_address as `1-street_address`, `1-address`.county_id as `1-county_id`, `1-address`.postal_code as `1-postal_code`, `1-phone-1`.id as `1-phone-1_id`, `1-phone-1`.phone as `1-phone-1`, `1-address`.country_id as `1-country_id`, `Main-location_type`.id as `Main-location_type_id`, `Main-location_type`.name as `Main-location_type`, `Main-phone-1`.id as `Main-phone-1_id`, `Main-phone-1`.phone as `Main-phone-1`, civicrm_value_school_info_2.id as civicrm_value_school_info_2_id, civicrm_value_school_info_2.school_class_24 as custom_24, civicrm_value_school_info_2.last_visit_25 as custom_25, civicrm_value_school_info_2.coach_name_26 as custom_26, civicrm_value_school_info_2.coach_phone_27 as custom_27, civicrm_value_school_info_2.coach_email_28 as custom_28, civicrm_value_school_info_2.ets_number_42 as custom_42, civicrm_value_school_info_2.coach_cell_50 as custom_50, civicrm_value_school_info_2.hudl_link_60 as custom_60 FROM civicrm_contact contact_a
      LEFT JOIN civicrm_address `1-address` ON (`1-address`.contact_id = contact_a.id AND `1-address`.is_primary = 1) LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 )
      LEFT JOIN civicrm_phone `1-phone-1` ON contact_a.id = `1-phone-1`.contact_id AND `1-phone-1`.is_primary = 1
      LEFT JOIN civicrm_phone `Main-phone-1` ON contact_a.id = `Main-phone-1`.contact_id AND `Main-phone-1`.location_type_id = 3 AND ( `Main-phone-1`.phone_type_id = '1' OR `Main-phone-1`.phone_type_id IS NULL )
      LEFT JOIN civicrm_location_type `1-location_type` ON ( ( `1-address`.location_type_id = `1-location_type`.id ) OR ( `1-phone-1`.location_type_id = `1-location_type`.id ) )
      LEFT JOIN civicrm_location_type `Main-location_type` ON ( ( `Main-phone-1`.location_type_id = `Main-location_type`.id ) ) LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_3` ON contact_a.id = `civicrm_group_contact_cache_3`.contact_id
      LEFT JOIN civicrm_value_school_info_2 ON civicrm_value_school_info_2.entity_id = `contact_a`.id WHERE ( civicrm_address.state_province_id = 1019 AND `civicrm_group_contact_cache_3`.group_id IN (3) ) AND (contact_a.is_deleted = 0) AND contact_a.is_deleted = 0 GROUP BY contact_a.id ORDER BY `1-county` asc, contact_a.id LIMIT 0, 50 [nativecode=1054 ** Unknown column '1-county' in 'order clause']"]
      )

      Oct 11 18:22:19 [info] $backTrace = #0 /home/national/public_html/administrator/components/com_civicrm/civicrm/CRM/Core/Error.php(239): CRM_Core_Error::backtrace("backTrace", TRUE)
      #1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
      #2 /home/national/public_html/administrator/components/com_civicrm/civicrm/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
      #3 /home/national/public_html/administrator/components/com_civicrm/civicrm/packages/DB.php(975): PEAR_Error->PEAR_Error("DB Error: no such field", -19, 16, (Array:2), "SELECT contact_a.id as contact_id, contact_a.contact_type as contact_type, co...")
      #4 /home/national/public_html/administrator/components/com_civicrm/civicrm/packages/PEAR.php(564): DB_Error->DB_Error(-19, 16, (Array:2), "SELECT contact_a.id as contact_id, contact_a.contact_type as contact_type, co...")
      #5 /home/national/public_html/administrator/components/com_civicrm/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -19, NULL, NULL, "SELECT contact_a.id as contact_id, contact_a.contact_type as contact_type, co...", "DB_Error", TRUE)
      #6 /home/national/public_html/administrator/components/com_civicrm/civicrm/packages/DB/mysql.php(899): DB_common->raiseError(-19, NULL, NULL, NULL, "1054 ** Unknown column '1-county' in 'order clause'")
      #7 /home/national/public_html/administrator/components/com_civicrm/civicrm/packages/DB/mysql.php(328): DB_mysql->mysqlRaiseError()
      #8 /home/national/public_html/administrator/components/com_civicrm/civicrm/packages/DB/common.php(1216): DB_mysql->simpleQuery("SELECT contact_a.id as contact_id, contact_a.contact_type as contact_type, co...")
      #9 /home/national/public_html/administrator/components/com_civicrm/civicrm/packages/DB/DataObject.php(2442): DB_common->query("SELECT contact_a.id as contact_id, contact_a.contact_type as contact_type, co...")
      #10 /home/national/public_html/administrator/components/com_civicrm/civicrm/packages/DB/DataObject.php(1634): DB_DataObject->_query("SELECT contact_a.id as contact_id, contact_a.contact_type as contact_type, co...")
      #11 /home/national/public_html/administrator/components/com_civicrm/civicrm/CRM/Core/DAO.php(325): DB_DataObject->query("SELECT contact_a.id as contact_id, contact_a.contact_type as contact_type, co...")
      #12 /home/national/public_html/administrator/components/com_civicrm/civicrm/CRM/Core/DAO.php(1116): CRM_Core_DAO->query("SELECT contact_a.id as contact_id, contact_a.contact_type as contact_type, co...", TRUE)
      #13 /home/national/public_html/administrator/components/com_civicrm/civicrm/CRM/Contact/BAO/Query.php(4634): CRM_Core_DAO::executeQuery("SELECT contact_a.id as contact_id, contact_a.contact_type as contact_type, co...")
      #14 /home/national/public_html/administrator/components/com_civicrm/civicrm/CRM/Profile/Selector/Listings.php(494): CRM_Contact_BAO_Query->searchQuery(0, 50, Object(CRM_Utils_Sort), NULL, NULL, NULL, NULL, NULL, "contact_a.is_deleted = 0")
      #15 /home/national/public_html/administrator/components/com_civicrm/civicrm/CRM/Core/Selector/Controller.php(408): CRM_Profile_Selector_Listings->getRows(4, 0, 50, Object(CRM_Utils_Sort), 2, NULL)
      #16 /home/national/public_html/administrator/components/com_civicrm/civicrm/CRM/Core/Selector/Controller.php(344): CRM_Core_Selector_Controller->getRows(Object(CRM_Core_Selector_Controller))
      #17 /home/national/public_html/administrator/components/com_civicrm/civicrm/CRM/Profile/Page/Listings.php(390): CRM_Core_Selector_Controller->run()
      #18 /home/national/public_html/administrator/components/com_civicrm/civicrm/CRM/Profile/Page/Router.php(127): CRM_Profile_Page_Listings->run()
      #19 /home/national/public_html/administrator/components/com_civicrm/civicrm/CRM/Core/Invoke.php(331): CRM_Profile_Page_Router->run((Array:2), NULL)
      #20 /home/national/public_html/administrator/components/com_civicrm/civicrm/CRM/Core/Invoke.php(75): CRM_Core_Invoke::runItem((Array:13))
      #21 /home/national/public_html/administrator/components/com_civicrm/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:2))
      #22 /home/national/public_html/components/com_civicrm/civicrm.php(86): CRM_Core_Invoke::invoke((Array:2))
      #23 /home/national/public_html/components/com_civicrm/civicrm.php(11): civicrm_invoke()
      #24 /home/national/public_html/libraries/cms/component/helper.php(352): require_once("/home/national/public_html/components/com_civicrm/civicrm.php")
      #25 /home/national/public_html/libraries/cms/component/helper.php(332): JComponentHelper::executeComponent("/home/national/public_html/components/com_civicrm/civicrm.php")
      #26 /home/national/public_html/libraries/cms/application/site.php(191): JComponentHelper::renderComponent("com_civicrm")
      #27 /home/national/public_html/libraries/cms/application/site.php(237): JApplicationSite->dispatch()
      #28 /home/national/public_html/libraries/cms/application/cms.php(251): JApplicationSite->doExecute()
      #29 /home/national/public_html/index.php(40): JApplicationCms->execute()
      #30

      {main}

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              thimbleweed Harry Cooper
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: