all the loans and I’d like to get the number of loan preparations for each loan;
or some collection objects and I’d like to get the number of preparations for every CO.
It seems to me that there is no way to achieve this with the Query Builder (Sp-7.11.1 at the time of writing). Am I correct ?
In SQL it would translate to something like that:
SELECT loan.LoanNumber, COUNT(lp.LoanPreparationID) AS 'LoanPreparation Count'
FROM loan
JOIN loanpreparation lp ON lp.LoanID = loan.LoanID
GROUP BY loan.LoanNumber
LoanNumber
LoanPreparation Count
1973-001
1
1974-001
1
1974-002
7
1980-001
2
1981-001
14
etc.
etc.
Or for the preparations of the last five CO:
SELECT co.CatalogNumber, COUNT(p.PreparationID) AS 'Preparation Count'
FROM collectionobject co
JOIN preparation p ON p.CollectionObjectID = co.CollectionObjectID
GROUP BY co.CollectionObjectID
ORDER BY co.CatalogNumber DESC
LIMIT 5
CatalogNumber
Preparation Count
000328293
1
000328292
1
000328291
1
000328290
2
000328289
1
We could expect a (count) entry in the Query Builder, the same way we already have an (aggregated) entry ?
Unfortunately, you cannot directly query anything that uses the GROUP BY function, such as a preparation count, within the current Query Builder. The current workarounds for this are to use SQL queries directly or to edit the form definition to add a virtual field in the form definition.
Here are the GitHub issues to track the progress of adding GROUP BY and querying it. I’ve mentioned you in them and you can follow along the progress as well:
Please let me know if you have any further questions!
