SearchKit: 500 error when selecting "All Pages" with HAVING clause
When you create a SK query with a HAVING clause, and there is more than one page of contacts, selecting all contacts will give a 500 error.
The following SK will do this quite nicely. The error is some variant on "Invalid expression in HAVING clause: 'MAX_Contact_Contribution_contact_id_01_total_amount'. Must use a value from SELECT clause."
It seems like selecting "All Pages" causes SK to do an API4 query similar to the original SavedSearch.run
, but only returning the id
column (which makes sense). However, this makes using the HAVING
clause fail.
[
[
"SavedSearch",
"save",
{
"records": [
{
"name": "Test",
"label": "500 error test",
"form_values": null,
"mapping_id": null,
"search_custom_id": null,
"api_entity": "Contact",
"api_params": {
"version": 4,
"select": [
"addressee_display",
"display_name",
"MAX(DISTINCT Contact_Contribution_contact_id_01.total_amount) AS MAX_Contact_Contribution_contact_id_01_total_amount",
"GROUP_CONCAT(DISTINCT Contact_Contribution_contact_id_01.source) AS GROUP_CONCAT_Contact_Contribution_contact_id_01_source",
"Connection_to_MHC.Connection_to_MHC",
"MAX(Contact_Contribution_contact_id_01.receive_date) AS MAX_Contact_Contribution_contact_id_01_receive_date"
],
"orderBy": [],
"where": [],
"groupBy": [
"id",
"addressee_id"
],
"join": [
[
"Contribution AS Contact_Contribution_contact_id_01",
"INNER",
[
"id",
"=",
"Contact_Contribution_contact_id_01.contact_id"
]
]
],
"having": [
[
"MAX_Contact_Contribution_contact_id_01_total_amount",
">",
"1"
]
]
},
"expires_date": null,
"description": null
}
],
"match": [
"name"
]
}
]
]