Searchkit: Fatal error on Related Contacts search
Hey there,
I built out a Related Contacts search, but noticed recently that there are some Fatal error messages in the Civi logs.
How to reproduce
You can use my export here it's nothing complicated just a Display of Related Contacts (way to go on this feature btw)
[
[
"SavedSearch",
"save",
{
"records": [
{
"name": "Related_Contacts",
"label": "Related Contacts",
"form_values": null,
"mapping_id": null,
"search_custom_id": null,
"api_entity": "RelationshipCache",
"api_params": {
"version": 4,
"select": [
"near_relation:label",
"far_contact_id.display_name"
],
"orderBy": [],
"where": [
[
"is_active",
"=",
true
]
],
"groupBy": [],
"join": [],
"having": []
},
"expires_date": null,
"description": null
}
],
"match": [
"name"
]
}
],
[
"SearchDisplay",
"save",
{
"records": [
{
"name": "Relationships",
"label": "Relationships Table",
"saved_search_id.name": "Related_Contacts",
"type": "table",
"settings": {
"actions": true,
"limit": 25,
"classes": [
"table",
"table-striped",
"table-bordered"
],
"pager": {
"show_count": true,
"expose_limit": true
},
"columns": [
{
"type": "field",
"key": "near_relation:label",
"dataType": "String",
"label": "Relationship",
"sortable": true,
"link": {
"path": "",
"entity": "Relationship",
"action": "view",
"join": "",
"target": "crm-popup"
},
"title": "View Related Contact",
"tally": {
"fn": null
}
},
{
"type": "field",
"key": "far_contact_id.display_name",
"dataType": "String",
"label": "Contact Name",
"sortable": true,
"link": {
"path": "",
"entity": "Contact",
"action": "view",
"join": "far_contact_id",
"target": "_blank"
},
"title": "View Contact",
"tally": {
"fn": null
}
}
],
"sort": [
[
"relationship_type_id",
"DESC"
],
[
"far_contact_id.display_name",
"ASC"
]
],
"placeholder": 5,
"tally": {
"label": "Total"
}
},
"acl_bypass": true
}
],
"match": [
"name",
"saved_search_id"
]
}
],
[
"Afform",
"save",
{
"records": [
{
"name": "afsearchRelationships",
"requires": [],
"title": "Relationships",
"description": "",
"is_dashlet": false,
"is_public": false,
"is_token": false,
"permission": "access CiviCRM",
"type": "search",
"entity_type": null,
"join_entity": null,
"contact_summary": "block",
"icon": "fa-list-alt",
"server_route": "",
"redirect": null,
"create_submission": false,
"navigation": null,
"layout": "<div af-fieldset=\"\">\n <af-field name=\"far_contact_id.display_name\" defn=\"{label: 'Contact Name', input_attrs: {}}\" />\n <crm-search-display-table search-name=\"Related_Contacts\" display-name=\"Relationships\" filters=\"{near_contact_id: options.contact_id}\"></crm-search-display-table>\n</div>\n"
}
]
}
]
]
What is happening
The display renders perfectly, but in the logs it seems that the SELECT statement
is incorrect. This fires just on the actual search as well.
The Error
Feb. 03 09:30:22 [error] $Fatal Error Details = Array
(
[callback] => Array
(
[0] => CRM_Core_Error
[1] => exceptionHandler
)
[code] => -2
[message] => DB Error: syntax error
[mode] => 16
[debug_info] => SELECT FROM (SELECT `a`.`near_relation` AS `near_relation:label`, `far_contact_id_1`.`display_name` AS `far_contact_id.display_name`, `RelationshipCache_Contact_far_contact_id_01`.`display_name` AS `RelationshipCache_Contact_far_contact_id_01.display_name`
FROM civicrm_relationship_cache a
LEFT JOIN `civicrm_relationship_type_en_CA` `RelationshipCache_RelationshipType_relationship_type_id_01` ON `a`.`relationship_type_id` = `RelationshipCache_RelationshipType_relationship_type_id_01`.`id`
LEFT JOIN `civicrm_contact` `RelationshipCache_Contact_far_contact_id_01` ON `a`.`far_contact_id` = `RelationshipCache_Contact_far_contact_id_01`.`id`
LEFT JOIN `civicrm_contact` `far_contact_id_1` ON `a`.`far_contact_id` = `far_contact_id_1`.`id`
WHERE (`a`.`is_active` = "1")
) `api_query` [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 'FROM (SELECT `a`.`near_relation` AS `near_relation:label`, `far_contact_id_1`...' at line 1]
[type] => DB_Error
[user_info] => SELECT FROM (SELECT `a`.`near_relation` AS `near_relation:label`, `far_contact_id_1`.`display_name` AS `far_contact_id.display_name`, `RelationshipCache_Contact_far_contact_id_01`.`display_name` AS `RelationshipCache_Contact_far_contact_id_01.display_name`
FROM civicrm_relationship_cache a
LEFT JOIN `civicrm_relationship_type_en_CA` `RelationshipCache_RelationshipType_relationship_type_id_01` ON `a`.`relationship_type_id` = `RelationshipCache_RelationshipType_relationship_type_id_01`.`id`
LEFT JOIN `civicrm_contact` `RelationshipCache_Contact_far_contact_id_01` ON `a`.`far_contact_id` = `RelationshipCache_Contact_far_contact_id_01`.`id`
LEFT JOIN `civicrm_contact` `far_contact_id_1` ON `a`.`far_contact_id` = `far_contact_id_1`.`id`
WHERE (`a`.`is_active` = "1")
) `api_query` [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 'FROM (SELECT `a`.`near_relation` AS `near_relation:label`, `far_contact_id_1`...' at line 1]
[to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="SELECT FROM (SELECT `a`.`near_relation` AS `near_relation:label`, `far_contact_id_1`.`display_name` AS `far_contact_id.display_name`, `RelationshipCache_Contact_far_contact_id_01`.`display_name` AS `RelationshipCache_Contact_far_contact_id_01.display_name`
FROM civicrm_relationship_cache a
LEFT JOIN `civicrm_relationship_type_en_CA` `RelationshipCache_RelationshipType_relationship_type_id_01` ON `a`.`relationship_type_id` = `RelationshipCache_RelationshipType_relationship_type_id_01`.`id`
LEFT JOIN `civicrm_contact` `RelationshipCache_Contact_far_contact_id_01` ON `a`.`far_contact_id` = `RelationshipCache_Contact_far_contact_id_01`.`id`
LEFT JOIN `civicrm_contact` `far_contact_id_1` ON `a`.`far_contact_id` = `far_contact_id_1`.`id`
WHERE (`a`.`is_active` = "1")
) `api_query` [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 'FROM (SELECT `a`.`near_relation` AS `near_relation:label`, `far_contact_id_1`...' at line 1]"]
)
The thing I want to point out is the SELECT FROM (SELECT `a`.`near_relation`
at the start. The Select is invalid because it's not selecting any fields. Perhaps it should be *
, but really I did choose Relationship to contact
and Contact (far side)
to be displayed in the actual table below