Answers.Body: <p>Yes, its a known issue and is reported on <a href="https://lab.civicrm.org/dev/core/-/issues/2319" rel="nofollow noreferrer">CiviCRM gitlab</a>. Difficult one to support or fix as the data is filled when the rows for results are altered after fetched from database. May be turn off the sorting for the column using JS?</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-06-05T19:52:34.277Z
Answers.Text: Thank you for your quick reply @pradeep. We don't want to turn it off, do you know how much effort will be involved to fix?
Answers.UserDisplayName: FullStack-Dev
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-06-05T19:54:27.440Z
Answers.Text: Would be nice to chat on https://chat.civicrm.org/civicrm/channels/dev for estimates. Other option would be if you are in drupal to use views to create the search or dataprocessor to build your own case search
Answers.UserDisplayName: Pradeep Nayak
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-06-06T04:31:19.837Z
Answers.Text: Thank you, i created drupal views for now
Answers.UserDisplayName: FullStack-Dev
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-06-05T19:48:35.080Z
Answers.LastActivityDate: 2021-06-05T19:48:35.080Z
Answers.UserDisplayName: Pradeep Nayak
Body: <p>On Find case result screen, when sorted using 'Most recent' column get DB error. Is this know error?</p>
  <pre><code>Type    DB_Error
  Code    -19
  Message DB Error: no such field
  Mode    16
  UserInfo    SELECT civicrm_case.id , 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`, contact_a.display_name as `display_name`, civicrm_phone.id as phone_id, civicrm_phone.phone_type_id as phone_type_id, civicrm_phone.phone as `phone`, civicrm_case.id as case_id, civicrm_case_type.title as case_type, case_status.label as case_status, civicrm_case.is_deleted as case_deleted, IF(case_relationship.contact_id_b = contact_a.id, case_relation_type.label_b_a, case_relation_type.label_a_b) as case_role, case_activity.activity_date_time as case_activity_date_time, case_activity.subject as case_activity_subject, civicrm_case.subject as case_subject, rec_activity_type.label as case_activity_type FROM civicrm_contact contact_a LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1) LEFT JOIN civicrm_case_contact ON civicrm_case_contact.contact_id = contact_a.id INNER JOIN civicrm_case ON civicrm_case_contact.case_id = civicrm_case.id LEFT JOIN civicrm_relationship case_relationship ON ( case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = 202 AND case_relationship.case_id = civicrm_case.id OR case_relationship.contact_id_b = civicrm_case_contact.contact_id AND case_relationship.contact_id_a = 202 AND case_relationship.case_id = civicrm_case.id ) LEFT JOIN civicrm_relationship_type case_relation_type ON ( case_relation_type.id = case_relationship.relationship_type_id AND case_relation_type.id = case_relationship.relationship_type_id ) INNER JOIN civicrm_case_activity ON civicrm_case_activity.case_id = civicrm_case.id INNER JOIN civicrm_activity case_activity ON ( civicrm_case_activity.activity_id = case_activity.id AND case_activity.is_current_revision = 1 ) LEFT JOIN civicrm_option_group option_group_activity_type ON (option_group_activity_type.name = 'activity_type') LEFT JOIN civicrm_option_value rec_activity_type ON (case_activity.activity_type_id = rec_activity_type.value AND option_group_activity_type.id = rec_activity_type.option_group_id ) LEFT JOIN civicrm_option_group option_group_case_status ON (option_group_case_status.name = 'case_status') LEFT JOIN civicrm_option_value case_status ON (civicrm_case.status_id = case_status.value AND option_group_case_status.id = case_status.option_group_id ) LEFT JOIN civicrm_case_type ON civicrm_case.case_type_id = civicrm_case_type.id WHERE ( civicrm_case.is_deleted = 0 ) AND ( 1 ) AND (contact_a.is_deleted = 0) GROUP BY civicrm_case.id ORDER BY `case_recent_activity_date` asc, `contact_a`.`id` LIMIT 0, 50 [nativecode=1054 ** Unknown column 'case_recent_activity_date' in 'order clause']
  DebugInfo   SELECT civicrm_case.id , 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`, contact_a.display_name as `display_name`, civicrm_phone.id as phone_id, civicrm_phone.phone_type_id as phone_type_id, civicrm_phone.phone as `phone`, civicrm_case.id as case_id, civicrm_case_type.title as case_type, case_status.label as case_status, civicrm_case.is_deleted as case_deleted, IF(case_relationship.contact_id_b = contact_a.id, case_relation_type.label_b_a, case_relation_type.label_a_b) as case_role, case_activity.activity_date_time as case_activity_date_time, case_activity.subject as case_activity_subject, civicrm_case.subject as case_subject, rec_activity_type.label as case_activity_type FROM civicrm_contact contact_a LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1) LEFT JOIN civicrm_case_contact ON civicrm_case_contact.contact_id = contact_a.id INNER JOIN civicrm_case ON civicrm_case_contact.case_id = civicrm_case.id LEFT JOIN civicrm_relationship case_relationship ON ( case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = 202 AND case_relationship.case_id = civicrm_case.id OR case_relationship.contact_id_b = civicrm_case_contact.contact_id AND case_relationship.contact_id_a = 202 AND case_relationship.case_id = civicrm_case.id ) LEFT JOIN civicrm_relationship_type case_relation_type ON ( case_relation_type.id = case_relationship.relationship_type_id AND case_relation_type.id = case_relationship.relationship_type_id ) INNER JOIN civicrm_case_activity ON civicrm_case_activity.case_id = civicrm_case.id INNER JOIN civicrm_activity case_activity ON ( civicrm_case_activity.activity_id = case_activity.id AND case_activity.is_current_revision = 1 ) LEFT JOIN civicrm_option_group option_group_activity_type ON (option_group_activity_type.name = 'activity_type') LEFT JOIN civicrm_option_value rec_activity_type ON (case_activity.activity_type_id = rec_activity_type.value AND option_group_activity_type.id = rec_activity_type.option_group_id ) LEFT JOIN civicrm_option_group option_group_case_status ON (option_group_case_status.name = 'case_status') LEFT JOIN civicrm_option_value case_status ON (civicrm_case.status_id = case_status.value AND option_group_case_status.id = case_status.option_group_id ) LEFT JOIN civicrm_case_type ON civicrm_case.case_type_id = civicrm_case_type.id WHERE ( civicrm_case.is_deleted = 0 ) AND ( 1 ) AND (contact_a.is_deleted = 0) GROUP BY civicrm_case.id ORDER BY `case_recent_activity_date` asc, `contact_a`.`id` LIMIT 0, 50 [nativecode=1054 ** Unknown column 'case_recent_activity_date' in 'order clause']
  </code></pre>
  
Tags: <civicase>
Title: User cannot sort the cases using 'most recent'
ThreadText: # User cannot sort the cases using 'most recent'
  
  (Asked by: username_0 on 2021-06-05)
  
  On Find case result screen, when sorted using 'Most recent' column get DB error. Is this know error?
  Type    DB_Error
  Code    -19
  Message DB Error: no such field
  Mode    16
  UserInfo    SELECT civicrm_case.id , 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`, contact_a.display_name as `display_name`, civicrm_phone.id as phone_id, civicrm_phone.phone_type_id as phone_type_id, civicrm_phone.phone as `phone`, civicrm_case.id as case_id, civicrm_case_type.title as case_type, case_status.label as case_status, civicrm_case.is_deleted as case_deleted, IF(case_relationship.contact_id_b = contact_a.id, case_relation_type.label_b_a, case_relation_type.label_a_b) as case_role, case_activity.activity_date_time as case_activity_date_time, case_activity.subject as case_activity_subject, civicrm_case.subject as case_subject, rec_activity_type.label as case_activity_type FROM civicrm_contact contact_a LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1) LEFT JOIN civicrm_case_contact ON civicrm_case_contact.contact_id = contact_a.id INNER JOIN civicrm_case ON civicrm_case_contact.case_id = civicrm_case.id LEFT JOIN civicrm_relationship case_relationship ON ( case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = 202 AND case_relationship.case_id = civicrm_case.id OR case_relationship.contact_id_b = civicrm_case_contact.contact_id AND case_relationship.contact_id_a = 202 AND case_relationship.case_id = civicrm_case.id ) LEFT JOIN civicrm_relationship_type case_relation_type ON ( case_relation_type.id = case_relationship.relationship_type_id AND case_relation_type.id = case_relationship.relationship_type_id ) INNER JOIN civicrm_case_activity ON civicrm_case_activity.case_id = civicrm_case.id INNER JOIN civicrm_activity case_activity ON ( civicrm_case_activity.activity_id = case_activity.id AND case_activity.is_current_revision = 1 ) LEFT JOIN civicrm_option_group option_group_activity_type ON (option_group_activity_type.name = 'activity_type') LEFT JOIN civicrm_option_value rec_activity_type ON (case_activity.activity_type_id = rec_activity_type.value AND option_group_activity_type.id = rec_activity_type.option_group_id ) LEFT JOIN civicrm_option_group option_group_case_status ON (option_group_case_status.name = 'case_status') LEFT JOIN civicrm_option_value case_status ON (civicrm_case.status_id = case_status.value AND option_group_case_status.id = case_status.option_group_id ) LEFT JOIN civicrm_case_type ON civicrm_case.case_type_id = civicrm_case_type.id WHERE ( civicrm_case.is_deleted = 0 ) AND ( 1 ) AND (contact_a.is_deleted = 0) GROUP BY civicrm_case.id ORDER BY `case_recent_activity_date` asc, `contact_a`.`id` LIMIT 0, 50 [nativecode=1054 ** Unknown column 'case_recent_activity_date' in 'order clause']
  DebugInfo   SELECT civicrm_case.id , 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`, contact_a.display_name as `display_name`, civicrm_phone.id as phone_id, civicrm_phone.phone_type_id as phone_type_id, civicrm_phone.phone as `phone`, civicrm_case.id as case_id, civicrm_case_type.title as case_type, case_status.label as case_status, civicrm_case.is_deleted as case_deleted, IF(case_relationship.contact_id_b = contact_a.id, case_relation_type.label_b_a, case_relation_type.label_a_b) as case_role, case_activity.activity_date_time as case_activity_date_time, case_activity.subject as case_activity_subject, civicrm_case.subject as case_subject, rec_activity_type.label as case_activity_type FROM civicrm_contact contact_a LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1) LEFT JOIN civicrm_case_contact ON civicrm_case_contact.contact_id = contact_a.id INNER JOIN civicrm_case ON civicrm_case_contact.case_id = civicrm_case.id LEFT JOIN civicrm_relationship case_relationship ON ( case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = 202 AND case_relationship.case_id = civicrm_case.id OR case_relationship.contact_id_b = civicrm_case_contact.contact_id AND case_relationship.contact_id_a = 202 AND case_relationship.case_id = civicrm_case.id ) LEFT JOIN civicrm_relationship_type case_relation_type ON ( case_relation_type.id = case_relationship.relationship_type_id AND case_relation_type.id = case_relationship.relationship_type_id ) INNER JOIN civicrm_case_activity ON civicrm_case_activity.case_id = civicrm_case.id INNER JOIN civicrm_activity case_activity ON ( civicrm_case_activity.activity_id = case_activity.id AND case_activity.is_current_revision = 1 ) LEFT JOIN civicrm_option_group option_group_activity_type ON (option_group_activity_type.name = 'activity_type') LEFT JOIN civicrm_option_value rec_activity_type ON (case_activity.activity_type_id = rec_activity_type.value AND option_group_activity_type.id = rec_activity_type.option_group_id ) LEFT JOIN civicrm_option_group option_group_case_status ON (option_group_case_status.name = 'case_status') LEFT JOIN civicrm_option_value case_status ON (civicrm_case.status_id = case_status.value AND option_group_case_status.id = case_status.option_group_id ) LEFT JOIN civicrm_case_type ON civicrm_case.case_type_id = civicrm_case_type.id WHERE ( civicrm_case.is_deleted = 0 ) AND ( 1 ) AND (contact_a.is_deleted = 0) GROUP BY civicrm_case.id ORDER BY `case_recent_activity_date` asc, `contact_a`.`id` LIMIT 0, 50 [nativecode=1054 ** Unknown column 'case_recent_activity_date' in 'order clause']
  
  **Tags:** civicase
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2021-06-05. Score: 0
  
  Yes, its a known issue and is reported on [CiviCRM gitlab](https://lab.civicrm.org/dev/core/-/issues/2319). Difficult one to support or fix as the data is filled when the rows for results are altered after fetched from database. May be turn off the sorting for the column using JS?
  
  > **Comments:**
  > **username_0**: Thank you for your quick reply @pradeep. We don't want to turn it off, do you know how much effort will be involved to fix?  
  > **username_1**: Would be nice to chat on https://chat.civicrm.org/civicrm/channels/dev for estimates. Other option would be if you are in drupal to use views to create the search or dataprocessor to build your own case search  
  > **username_0**: Thank you, i created drupal views for now  
  
  ---
  
  