Cases dashlet sometimes gives error when sort by subject
Pulling this out from #1624 (closed) as a separate issue.
Sometimes, sorting by the subject on a cases dashlet will give a datatables error. The actual error if you look at the network response is Column 'subject' in order clause is ambiguous
.
Error Field Error Value
Type DB_Error
Code -1
Message DB Error: unknown error
Mode 16
UserInfo SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civicrm_contact.id as contact_id, civicrm_contact.sort_name as sort_name, civicrm_phone.phone as phone, civicrm_contact.contact_type as contact_type, civicrm_contact.contact_sub_type as contact_sub_type, t_act.activity_type_id as activity_type_id, civicrm_case.case_type_id as case_type_id, civicrm_case.status_id as case_status_id, t_act.status_id as status_id, civicrm_case.start_date as case_start_date, GROUP_CONCAT(DISTINCT IF(case_relationship.contact_id_b = 2, case_relation_type.label_a_b, case_relation_type.label_b_a) SEPARATOR ', ') as case_role, t_act.activity_date_time as activity_date_time, t_act.id as activity_id, case_status.label AS case_status, civicrm_case_type.title AS case_type FROM civicrm_case INNER JOIN civicrm_case_contact ON civicrm_case.id = civicrm_case_contact.case_id INNER JOIN civicrm_contact ON civicrm_case_contact.contact_id = civicrm_contact.id LEFT JOIN civicrm_case_type ON civicrm_case.case_type_id = civicrm_case_type.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_activity ca4 ON civicrm_case.id = ca4.case_id LEFT JOIN civicrm_activity t_act ON t_act.id = ca4.activity_id AND t_act.is_current_revision = 1 LEFT JOIN civicrm_phone ON civicrm_phone.contact_id = civicrm_contact.id AND civicrm_phone.is_primary = 1 LEFT JOIN civicrm_relationship case_relationship ON ((case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = 2) OR (case_relationship.contact_id_b = civicrm_case_contact.contact_id AND case_relationship.contact_id_a = 2)) AND case_relationship.is_active 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 WHERE (1) AND civicrm_case.is_deleted = 0 AND civicrm_contact.is_deleted <> 1 AND (case_relationship.contact_id_b = 2 OR case_relationship.contact_id_a = 2) AND case_relationship.is_active AND civicrm_case.status_id IN (1,3) GROUP BY case_id ORDER BY subject asc LIMIT 0, 10 [nativecode=1052 ** Column 'subject' in order clause is ambiguous]
DebugInfo SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civicrm_contact.id as contact_id, civicrm_contact.sort_name as sort_name, civicrm_phone.phone as phone, civicrm_contact.contact_type as contact_type, civicrm_contact.contact_sub_type as contact_sub_type, t_act.activity_type_id as activity_type_id, civicrm_case.case_type_id as case_type_id, civicrm_case.status_id as case_status_id, t_act.status_id as status_id, civicrm_case.start_date as case_start_date, GROUP_CONCAT(DISTINCT IF(case_relationship.contact_id_b = 2, case_relation_type.label_a_b, case_relation_type.label_b_a) SEPARATOR ', ') as case_role, t_act.activity_date_time as activity_date_time, t_act.id as activity_id, case_status.label AS case_status, civicrm_case_type.title AS case_type FROM civicrm_case INNER JOIN civicrm_case_contact ON civicrm_case.id = civicrm_case_contact.case_id INNER JOIN civicrm_contact ON civicrm_case_contact.contact_id = civicrm_contact.id LEFT JOIN civicrm_case_type ON civicrm_case.case_type_id = civicrm_case_type.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_activity ca4 ON civicrm_case.id = ca4.case_id LEFT JOIN civicrm_activity t_act ON t_act.id = ca4.activity_id AND t_act.is_current_revision = 1 LEFT JOIN civicrm_phone ON civicrm_phone.contact_id = civicrm_contact.id AND civicrm_phone.is_primary = 1 LEFT JOIN civicrm_relationship case_relationship ON ((case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = 2) OR (case_relationship.contact_id_b = civicrm_case_contact.contact_id AND case_relationship.contact_id_a = 2)) AND case_relationship.is_active 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 WHERE (1) AND civicrm_case.is_deleted = 0 AND civicrm_contact.is_deleted <> 1 AND (case_relationship.contact_id_b = 2 OR case_relationship.contact_id_a = 2) AND case_relationship.is_active AND civicrm_case.status_id IN (1,3) GROUP BY case_id ORDER BY subject asc LIMIT 0, 10 [nativecode=1052 ** Column 'subject' in order clause is ambiguous]
PEAR_Exception: DB Error: unknown error in ...\sites\all\modules\civicrm\vendor\pear\pear-core-minimal\src\PEAR.php on line 922
- DB_Error: DB Error: unknown error in unknown on line unknown
Exception trace
# Function Location
0 CRM_Core_Error::exceptionHandler(Object(DB_Error)) ...\sites\all\modules\civicrm\vendor\pear\pear-core-minimal\src\PEAR.php:922
1 PEAR_Error->__construct('DB Error: unknow…', -1, 16, Array, 'SELECT civicrm_c…') ...\sites\all\modules\civicrm\vendor\pear\db\DB.php:997
2 DB_Error->__construct(-1, 16, Array, 'SELECT civicrm_c…') ...\sites\all\modules\civicrm\vendor\pear\pear-core-minimal\src\PEAR.php:575
3 PEAR::_raiseError(Object(DB_mysqli), null, -1, 16, Array, 'SELECT civicrm_c…', 'DB_Error', true) ...\sites\all\modules\civicrm\vendor\pear\pear-core-minimal\src\PEAR.php:223
4 PEAR->__call('raiseError', Array) ...\sites\all\modules\civicrm\vendor\pear\db\DB\common.php:1928
5 DB_common->raiseError(-1, null, null, 'SELECT civicrm_c…', '1052 ** Column '…') ...\sites\all\modules\civicrm\vendor\pear\db\DB\mysqli.php:936
6 DB_mysqli->mysqliRaiseError() ...\sites\all\modules\civicrm\vendor\pear\db\DB\mysqli.php:406
7 DB_mysqli->simpleQuery('SELECT civicrm_c…') ...\sites\all\modules\civicrm\vendor\pear\db\DB\common.php:1234
8 DB_common->query('SELECT civicrm_c…') ...\sites\all\modules\civicrm\packages\DB\DataObject.php:2696
9 DB_DataObject->_query('SELECT civicrm_c…') ...\sites\all\modules\civicrm\packages\DB\DataObject.php:1829
10 DB_DataObject->query('SELECT civicrm_c…') ...\sites\all\modules\civicrm\CRM\Core\DAO.php:472
11 CRM_Core_DAO->query('SELECT civicrm_c…', true) ...\sites\all\modules\civicrm\CRM\Core\DAO.php:1637
12 CRM_Core_DAO::executeQuery('SELECT civicrm_c…') ...\sites\all\modules\civicrm\CRM\Case\BAO\Case.php:583
13 CRM_Case_BAO_Case::getCases(false, Array) ...\sites\all\modules\civicrm\CRM\Case\Page\AJAX.php:186
14 CRM_Case_Page_AJAX::getCases() ...\sites\all\modules\civicrm\CRM\Core\Invoke.php:285
15 CRM_Core_Invoke::runItem(Array) ...\sites\all\modules\civicrm\CRM\Core\Invoke.php:69
16 CRM_Core_Invoke::_invoke(Array) ...\sites\all\modules\civicrm\CRM\Core\Invoke.php:36
17 CRM_Core_Invoke::invoke(Array) ...\sites\all\modules\civicrm\drupal\civicrm.module:471
18 civicrm_invoke('ajax', 'get-cases') ...\includes\menu.inc:527
19 menu_execute_active_handler() ...\index.php:21
20 {main}
Sorry, due to an error, we are unable to fulfill your request at the moment. You may want to contact your administrator or service provider with more details about what action you were performing when this occurred.
DB Error: unknown error