Meaning of "Search Cases with Upcoming Activities" in "My Cases" dashlet

Question

For a case with overdue (not yet completed) scheduled tasks, checking the "Search Cases with Upcoming Activities" in the "My Cases" dashlet correctly displays the next upcoming task which happens to be in the past (overdue). Unchecking this box, however, displays the third-to-last activity, which is as it happens also in the past (overdue) but comes well after the very next (overdue) task shown when this box is checked. My question is what is the function of the dashlet when the "Search Cases with Upcoming Activities" box is unchecked, and why would it show the third-to-last task in the sequence of tasks for my particular ongoing case. Many thanks!

Answer 1

Below is the query it runs, with the relevant parts being

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 
      ...
GROUP BY case_id ORDER BY activity_date_time ASC 

but this query isn't full-group-by compliant and also I think the GROUP BY is in the wrong place. Also case_id is ambiguous since it could apply to the AS case_id in the select or the civicrm_case_activity.case_id field or the civicrm_case_contact.case_id field.

So it seems like there's something off about the query.

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 activity_date_time ASC LIMIT 0, 10