Search Kit: SQL syntax error when creating smart group based on custom field
When creating a SK-based smart group, the SQL generated in the HAVING
clause doesn't correctly calculate the name of custom fields.
Steps to Replicate
- Create a new contact custom field of type Contact Reference.
- Fill in that value on 1-2 contacts.
- Create a SK search that finds those contacts.
- Create a smart group based on the search. In the "Contact Column" selector, choose the custom field and save.
- Go to Manage Groups and attempt to view the contacts in the smart group.
Expected Result
List of contacts.
Actual Result
DB Error: no such field
.
The SQL generated looks like this (my contact reference field is named 'advisor'):
INSERT IGNORE INTO civicrm_tmp_e_gccache_2d0f369488c3e23a1be7f722895ad9ce (group_id, contact_id)
SELECT 5 AS group_id, `constituent_information.advisor` AS contact_id FROM (
SELECT `constituent_information_1`.`advisor_7` AS `constituent_information.advisor`
FROM civicrm_contact a
LEFT JOIN `civicrm_value_constituent_information_1` `constituent_information_1` ON `a`.`id` = `constituent_information_1`.`entity_id`
WHERE (`a`.`id` >= "150")
HAVING (constituent_information.advisor NOT IN ( SELECT contact_id FROM civicrm_group_contact WHERE civicrm_group_contact.status = 'Removed' AND civicrm_group_contact.group_id = 5 )) )
The issue is with HAVING (constituent_information.advisor
. A HAVING
clause must reference the non-aliased name of the field. CRM_Contact_BAO_GroupContactCache::getApiSQL()
has this line:
$idField = SqlExpression::convert($apiParams['select'][0], TRUE)->getAlias();
However, this returns the incorrect value for idField
when the Contact Column is a custom field.
I attempted a fix but couldn't find a way to determine the entity of the $idField
.
Below is a Search Kit export of the search. You need a contact reference field named "advisor" in the "Constituent Information" custom group.
[
[
"SavedSearch",
"save",
{
"records": [
{
"name": "abc",
"label": "abc",
"form_values": null,
"search_custom_id": null,
"api_entity": "Contact",
"api_params": {
"version": 4,
"select": [
"constituent_information.advisor",
"display_name",
"constituent_information.advisor"
],
"orderBy": [],
"where": [
[
"id",
">=",
150
]
],
"groupBy": [],
"join": [],
"having": []
},
"expires_date": null,
"description": null,
"mapping_id": null
}
],
"match": [
"name"
]
}
],
[
"Group",
"save",
{
"records": [
{
"name": "test_group_5",
"title": "test group",
"description": null,
"source": null,
"saved_search_id.name": "abc",
"is_active": true,
"visibility": "User and User Admin Only",
"group_type:name": [],
"parents": null,
"children": null,
"is_hidden": false,
"is_reserved": false,
"frontend_title": null,
"frontend_description": null
}
],
"match": [
"name"
]
}
]
]