API v4 / search kit Or-by-union
We have a need to search for
Contribution has total amount = x OR soft credit has total amount = x
there are some more criteria but that is the gist of it.
Currently there is no good way to do this via search kit. Technically it's possible but it uses an un-indexed query.
Ways to do this query
Least performant.....
- OR clause
WHERE civicrm_contribution.total_amount = x
OR civicrm_contribution_soft.amount = x
This use of OR on 2 fields on different tables is a known performance problem & the reason why we don't do this in advances search where we do
A bit more performant
Temp tables with all contributions & all soft credits combined into one temptable and then used as a join
Performs OK-ish on mid-sized (e.g 300k contacts) sites but building a table with x million rows in it to query doesn't scale
More performant
Build a filtered temp table. We do this in some places in civi report - so instead of creating a temp table of all contributions where amount = x or the credit amount = x
Performs better, we do this in a several places in civireport & extended reports - but it's hard to code and very hard to do generically. Also it's still pretty awful if you are using 'limit' and you just want the first 10 out of a large number
Most performant - at least with limit
Use Union. This is how we got quicksearch to actually be quick and we get pretty good group searching mileage in advanced search from this too.
This would look like
SELECT DISTINCT * FROM (
SELECT * FROM civicrm_contribution LEFT JOIN civicrm_contribution_soft...
WHERE civicrm_contribution.total_amount = x
LIMIT 10
UNION
SELECT * FROM civicrm_contribution LEFT JOIN civicrm_contribution_soft...
WHERE civicrm_contribution_soft.amount = x
LIMIT 10
) as allofem
LIMIT 10
The challenge is how to expose a query like that.....