GitLab

When viewing a client record, then clicking the Cases tab - The cases are not displayed if the case has no activities associated with it. This can happen if you don't have a standard timeline with the case, which typically has the open job activity.

Doing some debugging, the SQL join appears to be an INNER JOIN and not a LEFT JOIN for civicrm_activity. When changed to a LEFT JOIN, then the cases without activities show as the join doesn't filter by the join.

SELECT civicrm_case.id as case_id

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 = 1 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 )

LEFT 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 ( contact_a.id = '1' AND civicrm_case.is_deleted = 0 ) AND (contact_a.is_deleted = 0)

GROUP BY civicrm_case.id

Page: CRM_Case_BAO_Case