Joins on related contacts failing (e.g. address of employer)
I am creating a searchkit that displays data from three entities: two related contacts and an address.
e.g. a contact, their employer and the country of the employer HQ.
Or in this instance, a product (household), the manufacturer/supplier of the product, and the country of the manufacturer/supplier (
API4 json:
{
"version": 4,
"select": [
"display_name",
"Contact_RelationshipCache_Contact_01.display_name",
"Contact_RelationshipCache_Contact_01_Contact_Address_contact_id_01.country_id:label",
"Contact_RelationshipCache_Contact_01.id",
"Contact_RelationshipCache_Contact_01_Contact_Address_contact_id_01.id"
],
"orderBy": [],
"where": [
[
"contact_type:name",
"=",
"Household"
]
],
"groupBy": [],
"join": [
[
"Contact AS Contact_RelationshipCache_Contact_01",
"INNER",
"RelationshipCache",
[
"id",
"=",
"Contact_RelationshipCache_Contact_01.far_contact_id"
],
[
"Contact_RelationshipCache_Contact_01.near_relation:name",
"IN",
[
"Manufactures",
"Supplies"
]
]
],
[
"Address AS Contact_RelationshipCache_Contact_01_Contact_Address_contact_id_01",
"LEFT",
[
"Contact_RelationshipCache_Contact_01.id",
"=",
"Contact_RelationshipCache_Contact_01_Contact_Address_contact_id_01.contact_id"
]
]
],
"having": [],
"debug": true,
"limit": 60,
"offset": 0
}
At first glance he API4 json seems fairly correct (to me)
But the generated SQL looks off
SELECT
`a`.`id` AS `id`,
`a`.`display_name` AS `display_name`,
`a`.`contact_sub_type` AS `contact_sub_type:label`,
`Contact_RelationshipCache_Contact_01`.`display_name` AS `Contact_RelationshipCache_Contact_01.display_name`,
`Contact_RelationshipCache_Contact_01`.`id` AS `Contact_RelationshipCache_Contact_01.id`,
`Contact_RelationshipCache_Contact_01_Contact_Address_contact_id_01`.`id` AS `Contact_RelationshipCache_Contact_01_Contact_Address_contact_id_01.id`
FROM
civicrm_contact a
INNER JOIN `civicrm_relationship_cache` `Contact_RelationshipCache_Contact_01_via_relationshipcache` ON `Contact_RelationshipCache_Contact_01_via_relationshipcache`.`far_contact_id` = `a`.`id`
INNER JOIN `civicrm_contact` `Contact_RelationshipCache_Contact_01` ON `Contact_RelationshipCache_Contact_01_via_relationshipcache`.`near_contact_id` = `Contact_RelationshipCache_Contact_01`.`id`
AND `Contact_RelationshipCache_Contact_01_via_relationshipcache`.`near_relation` IN ("manufactures", "supplies")
LEFT JOIN `civicrm_address` `Contact_RelationshipCache_Contact_01_Contact_Address_contact_id_01` ON `Contact_RelationshipCache_Contact_01_Contact_Address_contact_id_01`.`contact_id` = `a`.`id`
AND `Contact_RelationshipCache_Contact_01`.`id` = `Contact_RelationshipCache_Contact_01_Contact_Address_contact_id_01`.`contact_id`
WHERE
(`a`.`contact_type` = "Household")
AND (`a`.`is_deleted` = "0")
LIMIT
60 OFFSET 0
Removing the first part of the
LEFT JOIN `civicrm_address` clause
that is to say
`Contact_RelationshipCache_Contact_01_Contact_Address_contact_id_01`.`contact_id` = `a`.`id`
AND
seems to fix the query. Not sure why it was inserted in the first place