SearchKit - Unknown column 'Contact_ActivityContact_Activity_01.id' in 'on clause'
With the awesome new feature to allow a join between two fields, we managed to get this error :
SELECT `a`.`id` AS `id`, `a`.`sort_name` AS `sort_name`, `a`.`contact_type` AS `contact_type:label`, `a`.`contact_sub_type` AS `contact_sub_type:label`, `Contact_ActivityContact_Activity_01`.`subject` AS `Contact_ActivityContact_Activity_01.subject`, `Contact_ActivityContact_Activity_01`.`id` AS `Contact_ActivityContact_Activity_01.id`
FROM civicrm_contact a
LEFT JOIN (`civicrm_activity_contact` `Contact_ActivityContact_Activity_01_via_activitycontact` INNER JOIN `civicrm_value_activity_fiel_4` `Activity_field_1` ON (`Contact_ActivityContact_Activity_01`.`id` = `Activity_field_1`.`entity_id`) INNER JOIN `civicrm_activity` `Contact_ActivityContact_Activity_01` ON (`Contact_ActivityContact_Activity_01_via_activitycontact`.`activity_id` = `Contact_ActivityContact_Activity_01`.`id`)) ON `Contact_ActivityContact_Activity_01_via_activitycontact`.`record_type_id` = "3" AND `Activity_field_1`.`whatever_7` = TIMESTAMPDIFF(YEAR, `a`.`birth_date`, CURDATE()) AND `Contact_ActivityContact_Activity_01_via_activitycontact`.`contact_id` = `a`.`id`
WHERE (`a`.`contact_type` = "Individual")
AND (`a`.`is_deleted` = "0")
ORDER BY `a`.`sort_name` ASC
LIMIT 50
OFFSET 0
[nativecode=1054 ** Unknown column 'Contact_ActivityContact_Activity_01.id' in 'on clause']
The simplest query to reproduce the issue is to :
- create a custom field on activity
- create a SK with main entity Contact and left join on ActivityContact with a condition on the custom field = another field
Here is the SK UI:
And the API info:
{
"version": 4,
"select": [
"id",
"sort_name",
"contact_type:label",
"contact_sub_type:label",
"Contact_ActivityContact_Activity_01.subject"
],
"orderBy": {},
"where": [
[
"contact_type:name",
"=",
"Individual"
]
],
"groupBy": [],
"join": [
[
"Activity AS Contact_ActivityContact_Activity_01",
"LEFT",
"ActivityContact",
[
"id",
"=",
"Contact_ActivityContact_Activity_01.contact_id"
],
[
"Contact_ActivityContact_Activity_01.record_type_id:name",
"=",
"\"Activity Targets\""
],
[
"Contact_ActivityContact_Activity_01.Activity_field.whatever",
"=",
"age_years"
]
]
],
"having": []
}
Apparently, it's a problem in the order the query is done.
Instead of :
LEFT JOIN (
`civicrm_activity_contact` `Contact_ActivityContact_Activity_01_via_activitycontact`
INNER JOIN `civicrm_value_activity_fiel_4` `Activity_field_1`
ON (`Contact_ActivityContact_Activity_01`.`id` = `Activity_field_1`.`entity_id`)
INNER JOIN `civicrm_activity` `Contact_ActivityContact_Activity_01`
ON (`Contact_ActivityContact_Activity_01_via_activitycontact`.`activity_id` = `Contact_ActivityContact_Activity_01`.`id`)
)
In sql, moving the civicrm_activity
join before the custom field table join solves it :
LEFT JOIN (
`civicrm_activity_contact` `Contact_ActivityContact_Activity_01_via_activitycontact`
INNER JOIN `civicrm_activity` `Contact_ActivityContact_Activity_01`
ON (`Contact_ActivityContact_Activity_01_via_activitycontact`.`activity_id` =
INNER JOIN `civicrm_value_activity_fiel_4` `Activity_field_1`
ON (`Contact_ActivityContact_Activity_01`.`id` = `Activity_field_1`.`entity_id`)
`Contact_ActivityContact_Activity_01`.`id`)
)