Activity Type + Activity Status + Case Type not being Combined Correctly in Search Builder
This issue has the same outcome in the Search builder as noted for the Advanced Search in report#53 (moved) which is the main focus of my attention.
A search for contacts with scheduled meetings in one case type incorrectly includes contacts who have completed meetings in the case type of interest but also have scheduled meetings in the other case type.
Steps to create (in 5.31)
- Create CaseType1 containing a Meeting
- Create CaseType2 containing a Meeting
- Create a Case of CaseType1 for Alice and mark the Meeting as Completed
- Create a Case of CaseType1 for Bob and leave the Meeting as Scheduled
- Create a Case of CaseType2 for Alice and leave the Meeting as Scheduled
- Create a Case of CaseType2 for Bob and mark the Meeting as Completed
A table of the Meeting status for Alice and Bob in the different Cases
| Contact | CaseType1 | CaseType2 |
|---|---|---|
| Alice | Completed | Scheduled |
| Bob | Scheduled | Completed |
Use the Search Builder to search for Contacts with Meetings Scheduled in CaseType1.
Activity, Activity Type = Meeting
Activity, Activity Status = Scheduled
Case, Case Type = CaseType1
Result: Alice and Bob are both returned when only Bob satisfies the given search criteria.
The SQL query ($sql) displayed by CiviCRM in civicrm/CRM/Contact/Selector.php
public function fillupPrevNextCache($sort, $cacheKey, $start = 0, $end = self::CACHE_SIZE)
(Line 1019 in 5.31)
is as follows:
SELECT contact_a.id as id FROM civicrm_contact contact_a
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_activity_contact
ON ( civicrm_activity_contact.contact_id = contact_a.id )
LEFT JOIN civicrm_activity
ON ( civicrm_activity.id = civicrm_activity_contact.activity_id
AND civicrm_activity.is_deleted = 0
AND civicrm_activity.is_current_revision = 1 )
INNER JOIN civicrm_contact
ON ( civicrm_activity_contact.contact_id = civicrm_contact.id and civicrm_contact.is_deleted != 1 )
LEFT JOIN civicrm_case_type
ON civicrm_case.case_type_id = civicrm_case_type.id
WHERE ( ( civicrm_activity.activity_type_id = 1
AND civicrm_activity.status_id = 1
AND civicrm_case.case_type_id = 3 ) )
AND (contact_a.is_deleted = 0)
AND (civicrm_activity.activity_type_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 58, 60, 62, 64, 66, 67, 68))
GROUP BY contact_a.id
ORDER BY `contact_a`.`sort_name` asc, `contact_a`.`id`
LIMIT 0, 500