2

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']

1 Answer 1

2

Yes, its a known issue and is reported on CiviCRM gitlab. 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?

3
  • 1
    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? Jun 5, 2021 at 19:52
  • 1
    Would be nice to chat on 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 Jun 5, 2021 at 19:54
  • Thank you, i created drupal views for now Jun 6, 2021 at 4:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.