SearchKit: Participant Role filter not working for exclusions
Overview
Trying to exclude based on role in a Participant Search causes a DB error.
Reproduction steps
-
Create a Participant Search
-
Filter on "Participant Role" "Doesn't Contain" "Value" "Volunteer":
-
SQL error:
SELECT `a`.`id` AS `id`, `contact_id_1`.`sort_name` AS `contact_id.sort_name`, `event_id_2`.`title` AS `event_id.title`, `a`.`role_id` AS `role_id:label`, `contact_id_1`.`id` AS `contact_id.id`, `event_id_2`.`id` AS `event_id.id`, `a`.`contact_id` AS `contact_id`
FROM civicrm_participant a
LEFT JOIN `civicrm_contact` `contact_id_1` ON `a`.`contact_id` = `contact_id_1`.`id`
LEFT JOIN `civicrm_event` `event_id_2` ON `a`.`event_id` = `event_id_2`.`id`
WHERE ((() OR `a`.`role_id` IS NULL))
AND (`a`.`is_test` = "0")
ORDER BY `contact_id_1`.`sort_name` ASC, `event_id_2`.`title` ASC
LIMIT 50
OFFSET 0
[nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') OR `a`.`role_id` IS NULL))
AND (`a`.`is_test` = "0")
ORDER BY `contact_id...' at line 5]
Comments
-
An APIv4 Participant::get request with the
<>
operator does not break. Maybe the solution is to allow that operator on the Searchkit Participant Role field? But then again I don't understand how the operator lists are built. -
And yes, there is a workaround which is to include all the roles we don't want to exclude (but it can be a bit tedious):
Environment information
dmaster running CiviCRM 5.74.alpha1