User-selected Field Transformations in Search Kit can cause a DB constraint violation error
Overview
Within Search Kit, a user is not always limited to what type of Field Transformation they can use on a particular column, which can result in a DB constraint violation error.
Reproduction steps
- Go to Search > Search Kit and click NEW SEARCH
- Create a Search with Contributions as the primary Entity. Name and Save the search.
- Click Entity+ to add Contribution Contacts as a second Entity
- Click Group By and select Contact ID
- Expand Field Transformations and select Sum from the drop down menu on the Contact ID column
- Click +Add underneath the highlighted Compose Search block, and select Smart Group
- Give the group a title and make sure the Contact Column has Contact ID selected
- Go to Contacts > Manage Groups and click on Contacts in the row of your test Smart Group
- Got an error "Fatal error: DB error".
Current behaviour
This issue was discovered when a client set a transformation of "Sum" on the Contact ID field within a Search Kit search with Contributions as the primary entity, Grouped By Contact Id, and then created a Smart Group from that search. When the user went to Manage Groups and clicked on Contacts next to the Smart Group in question, they received an error screen with a DB constraint violation. Within the ConfigandLog, the error was as follows:
+message: "DB Error: constraint violation"
+userinfo: """
INSERT INTO civicrm_group_contact_cache (contact_id, group_id)\n
SELECT contact_id, group_id FROM civicrm_tmp_e_gccache_bcc919fe8bd625701eed1c8b83638db1\n
GROUP BY contact_id,group_id\n
[nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`client_civi`.`civicrm_group_contact_cache`, CONSTRAINT `FK_civicrm_group_contact_cache_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]
This happened because the the Sum transformation was selected on the Contact ID field, as shown within Search Kit below:
Expected behaviour
Field Transformations on columns that should only return a singe value (i.e. Contact ID) should be restricted so that a user does not inadvertently cause a constraint violation via Search Kit.
Environment information
- CiviCRM: _Master/5.53.0