CiviCase permissions break My Cases dashlet and CiviCase Dashboard

Question

CiviCRM 5.14.0 Drupal 7.67

Users with CiviCase permission "access all cases and activities" have no issues, but users with "access my cases and activities" get an error ("DataTables warning: table id=DataTables_Table_0 - Ajax error." ) when the My Cases dashlet attempts to load on the main dashboard page.

The backtrace reveals that it's stemming from the getCases() function in /CRM/Case/Page/AJAX.php and the DB is returning an error "1054 ** Unknown column 't_act.activity_type_id' in 'field list'" I checked, and activity_type_id does exist in the referenced table.

The CiviCase Dashboard also does not work for users with the restricted permissions. It does not give and error, but it does not return any results (for either upcoming or recent activities) even though it should.

My feeble attempts to trace the error suggest that it has to do with the 'has access to all cases' permission and how that gets validated in /CRM/Case/BAO/Case.php and then passed to getCases().

Any help is much appreciated!

Here is the full text from the error log if it's useful:

Aug 23 11:00:53  [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 COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALUE(civicrm_case.subject) as case_subject, ANY_VALUE(civicrm_contact.id) as contact_id, ANY_VALUE(civicrm_contact.sort_name) as sort_name, ANY_VALUE(civicrm_phone.phone) as phone, ANY_VALUE(civicrm_contact.contact_type) as contact_type, ANY_VALUE(civicrm_contact.contact_sub_type) as contact_sub_type, ANY_VALUE(t_act.activity_type_id) as activity_type_id, ANY_VALUE(civicrm_case.case_type_id) as case_type_id, ANY_VALUE(civicrm_case.status_id) as case_status_id, ANY_VALUE(t_act.status_id) as status_id, ANY_VALUE(civicrm_case.start_date) as case_start_date, ANY_VALUE(case_relation_type.label_b_a) as case_role, ANY_VALUE(t_act.activity_date_time) as activity_date_time, ANY_VALUE(t_act.id) as activity_id       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_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 = 13622
            AND case_relationship.is_active
            AND case_relationship.case_id = civicrm_case.id
        LEFT JOIN civicrm_relationship_type_en_US 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 = 13622 AND case_relationship.is_active   GROUP BY case_id  ORDER BY activity_date_time ASC) temp  [nativecode=1054 ** Unknown column 't_act.activity_type_id' in 'field list']
    [type] => DB_Error
    [user_info] =>  SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALUE(civicrm_case.subject) as case_subject, ANY_VALUE(civicrm_contact.id) as contact_id, ANY_VALUE(civicrm_contact.sort_name) as sort_name, ANY_VALUE(civicrm_phone.phone) as phone, ANY_VALUE(civicrm_contact.contact_type) as contact_type, ANY_VALUE(civicrm_contact.contact_sub_type) as contact_sub_type, ANY_VALUE(t_act.activity_type_id) as activity_type_id, ANY_VALUE(civicrm_case.case_type_id) as case_type_id, ANY_VALUE(civicrm_case.status_id) as case_status_id, ANY_VALUE(t_act.status_id) as status_id, ANY_VALUE(civicrm_case.start_date) as case_start_date, ANY_VALUE(case_relation_type.label_b_a) as case_role, ANY_VALUE(t_act.activity_date_time) as activity_date_time, ANY_VALUE(t_act.id) as activity_id       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_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 = 13622
            AND case_relationship.is_active
            AND case_relationship.case_id = civicrm_case.id
        LEFT JOIN civicrm_relationship_type_en_US 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 = 13622 AND case_relationship.is_active   GROUP BY case_id  ORDER BY activity_date_time ASC) temp  [nativecode=1054 ** Unknown column 't_act.activity_type_id' in 'field list']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info=" SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALUE(civicrm_case.subject) as case_subject, ANY_VALUE(civicrm_contact.id) as contact_id, ANY_VALUE(civicrm_contact.sort_name) as sort_name, ANY_VALUE(civicrm_phone.phone) as phone, ANY_VALUE(civicrm_contact.contact_type) as contact_type, ANY_VALUE(civicrm_contact.contact_sub_type) as contact_sub_type, ANY_VALUE(t_act.activity_type_id) as activity_type_id, ANY_VALUE(civicrm_case.case_type_id) as case_type_id, ANY_VALUE(civicrm_case.status_id) as case_status_id, ANY_VALUE(t_act.status_id) as status_id, ANY_VALUE(civicrm_case.start_date) as case_start_date, ANY_VALUE(case_relation_type.label_b_a) as case_role, ANY_VALUE(t_act.activity_date_time) as activity_date_time, ANY_VALUE(t_act.id) as activity_id       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_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 = 13622
            AND case_relationship.is_active
            AND case_relationship.case_id = civicrm_case.id
        LEFT JOIN civicrm_relationship_type_en_US 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 = 13622 AND case_relationship.is_active   GROUP BY case_id  ORDER BY activity_date_time ASC) temp  [nativecode=1054 ** Unknown column 't_act.activity_type_id' in 'field list']"]
)


Aug 23 11:00:53  [info] $backTrace = #0 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/CRM/Core/Error.php(236): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(922): CRM_Core_Error::handle(Object(DB_Error))
#2 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: no such field", -19, 16, (Array:2), " SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALU...")
#3 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error->__construct(-19, 16, (Array:2), " SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALU...")
#4 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -19, 16, (Array:2), " SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALU...", "DB_Error", TRUE)
#5 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/packages/DB/common.php(1907): PEAR->__call("raiseError", (Array:7))
#6 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-19, NULL, NULL, " SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALU...", "1054 ** Unknown column 't_act.activity_type_id' in 'field list'")
#7 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#8 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery(" SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALU...")
#9 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/packages/DB/DataObject.php(2415): DB_common->query(" SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALU...")
#10 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/packages/DB/DataObject.php(1607): DB_DataObject->_query(" SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALU...")
#11 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/CRM/Core/DAO.php(439): DB_DataObject->query(" SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALU...")
#12 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/CRM/Core/DAO.php(1491): CRM_Core_DAO->query(" SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALU...", TRUE)
#13 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/CRM/Case/BAO/Case.php(551): CRM_Core_DAO::singleValueQuery(" SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALU...")
#14 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/CRM/Case/Page/AJAX.php(203): CRM_Case_BAO_Case::getCases(FALSE, (Array:5))
#15 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/CRM/Core/Invoke.php(277): CRM_Case_Page_AJAX::getCases()
#16 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/CRM/Core/Invoke.php(85): CRM_Core_Invoke::runItem((Array:12))
#17 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:3))
#18 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/drupal/civicrm.module(444): CRM_Core_Invoke::invoke((Array:3))
#19 /opt/bitnami/apps/civicrm/htdocs/includes/menu.inc(527): civicrm_invoke("ajax", "get-cases")
#20 /opt/bitnami/apps/civicrm/htdocs/index.php(21): menu_execute_active_handler()
#21 {main}

I don't see this happening on case dashboard, but yes I do see it if I enable the dashlet. — Demerit, Aug 23 '19 at 20:32

Demerit · Accepted Answer · 2019-08-24 23:13:28Z

0

This seems to be a bug - what's happening is the type parameter in the url is coming through as my-cases instead of the expected upcoming or whatever, so the view isn't getting included in the query. A quick fix, but not sure yet if it's the right one long-term, is to change this line at https://github.com/civicrm/civicrm-core/blob/5.14.2/CRM/Case/BAO/Case.php#L463 from

case 'any':

to

case 'any': default:

UPDATE: I'm seeing this back to version 5.1 at least but it's intermittent and doesn't depend on permissions. I've opened https://lab.civicrm.org/dev/core/issues/1207

edited Aug 24 '19 at 23:13

answered Aug 23 '19 at 20:38

Demerit

9,6722 gold badges7 silver badges24 bronze badges

Thank you for the reply Demerit! I implemented your suggested fix and it appears to work. Not sure if this matters, but I tried to implement it via my Custom PHP directory, but that did not take (I checked permissions and cleared caches - still didn't take). So I made the change to the core CRM/Case/BAO/Case.php file and it worked! Thank you - and thanks for opening the bug report! – Matt Schroeder Aug 28 '19 at 18:15
Also - the Case Dashboard started working again before I applied this fix - so I can confirm that it must have been unrelated. Sorry about that. – Matt Schroeder Aug 28 '19 at 18:18
Interesting it doesn't work in the custom php directory. I don't have an obvious explanation for that, but maybe it's a clue as to why it's ntermittent and refreshing the dashlet sometimes clears and sometimes makes worse. Perhaps something to do with some config not getting fully loaded sometimes. – Demerit Aug 28 '19 at 18:26

add a comment |

Stack Exchange Network

current community

your communities

more stack exchange communities

CiviCase permissions break My Cases dashlet and CiviCase Dashboard

1 Answer 1

Your Answer

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

Hot Network Questions

CiviCase permissions break My Cases dashlet and CiviCase Dashboard

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

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

Related

Hot Network Questions