Search-kit sql error I managed to create
I was able to configure this in the UI (with the custom field in the INNER JOIN) but it died on attempting to search by it
Generated mysql - note requires table wmf_donor with field lifetime_usd_total
SELECT GROUP_CONCAT(DISTINCT `Contribution_Contact_contact_id_01`.`display_name` SEPARATOR "" ) AS `GROUP_CONCAT_DISTINCT_Contribution_Contact_contact_id_01_display_name`
FROM civicrm_contribution a
LEFT JOIN `wmf_donor` `wmf_donor` ON Contribution_Contact_contact_id_01.id = wmf_donor.entity_id
INNER JOIN `civicrm_contact` `Contribution_Contact_contact_id_01` ON `a`.`contact_id` = `Contribution_Contact_contact_id_01`.`id` AND `a`.`receive_date` BETWEEN "2019-07-01 00:00:00" AND "2020-06-30 23:59:59" AND `wmf_donor`.`lifetime_usd_total` >= "1000"
LEFT JOIN `civicrm_contribution` `Contribution_Contact_contact_id_01_Contact_Contribution_contact_id_01` ON `Contribution_Contact_contact_id_01`.`id` = `Contribution_Contact_contact_id_01_Contact_Contribution_contact_id_01`.`contact_id` AND `Contribution_Contact_contact_id_01_Contact_Contribution_contact_id_01`.`receive_date` BETWEEN "2020-07-01 00:00:00" AND "2021-06-30 23:59:59"
WHERE (`a`.`is_test` = "0") AND (`a`.`is_template` = "0") AND (`Contribution_Contact_contact_id_01_Contact_Contribution_contact_id_01`.`id` IS NULL)
GROUP BY `a`.`contact_id`
LIMIT 60
OFFSET 0
Note I tested the alternatives that might have been constructed - they 'worked' in the sense that they completed with error on my demo sites. On the server I had to kill them as they were taking more than a couple of minutes by the time I killed them. I don't think this query can work without the group filter TBH
SELECT GROUP_CONCAT(DISTINCT `Contribution_Contact_contact_id_01`.`display_name` SEPARATOR "" ) AS `GROUP_CONCAT_DISTINCT_Contribution_Contact_contact_id_01_display_name`
FROM civicrm_contribution a
** INNER JOIN `civicrm_contact` `Contribution_Contact_contact_id_01` ON `a`.`contact_id` = `Contribution_Contact_contact_id_01`.`id` AND `a`.`receive_date` BETWEEN "2019-07-01 00:00:00" AND "2020-06-30 23:59:59"
INNER JOIN `wmf_donor` `wmf_donor` ON a.contact_id = wmf_donor.entity_id AND `wmf_donor`.`lifetime_usd_total` >= "1000"**
LEFT JOIN `civicrm_contribution` `Contribution_Contact_contact_id_01_Contact_Contribution_contact_id_01` ON `Contribution_Contact_contact_id_01`.`id` = `Contribution_Contact_contact_id_01_Contact_Contribution_contact_id_01`.`contact_id` AND `Contribution_Contact_contact_id_01_Contact_Contribution_contact_id_01`.`receive_date` BETWEEN "2020-07-01 00:00:00" AND "2021-06-30 23:59:59"
WHERE (`a`.`is_test` = "0") AND (`a`.`is_template` = "0") AND (`Contribution_Contact_contact_id_01_Contact_Contribution_contact_id_01`.`id` IS NULL)
GROUP BY `a`.`contact_id`
LIMIT 60
OFFSET 0
``
and
SELECT GROUP_CONCAT(DISTINCT Contribution_Contact_contact_id_01
.display_name
SEPARATOR "" ) AS GROUP_CONCAT_DISTINCT_Contribution_Contact_contact_id_01_display_name
FROM civicrm_contribution a
** INNER JOIN wmf_donor
wmf_donor
ON a.contact_id = wmf_donor.entity_id AND wmf_donor
.lifetime_usd_total
>= "1000"
INNER JOIN `civicrm_contact` `Contribution_Contact_contact_id_01` ON `a`.`contact_id` = `Contribution_Contact_contact_id_01`.`id` AND `a`.`receive_date` BETWEEN "2019-07-01 00:00:00" AND "2020-06-30 23:59:59"**
LEFT JOIN `civicrm_contribution` `Contribution_Contact_contact_id_01_Contact_Contribution_contact_id_01` ON `Contribution_Contact_contact_id_01`.`id` = `Contribution_Contact_contact_id_01_Contact_Contribution_contact_id_01`.`contact_id` AND `Contribution_Contact_contact_id_01_Contact_Contribution_contact_id_01`.`receive_date` BETWEEN "2020-07-01 00:00:00" AND "2021-06-30 23:59:59"
WHERE (a
.is_test
= "0") AND (a
.is_template
= "0") AND (Contribution_Contact_contact_id_01_Contact_Contribution_contact_id_01
.id
IS NULL)
GROUP BY a
.contact_id
LIMIT 60
OFFSET 0