Count nested elements


I have posted the same question a while ago here but it didn’t lead to any results so this is a new version with everything I learned so far.

I have a database of cities and temples. Each city has multiple temples associated with it which themselves have multiple items associated with them through a relation. The items (called “Goshuin”) are in a third table.

I’m trying to display a list of cities and with each city the amount of temples and the sum of all Goshuin in those temples.
For example:

“Nara” - 8 temples - 14 Goshuin

I’ve gotten as far as this request:

It gives me all the info I need including a count of the temples as well as the Goshuiin. However, the count is incorrect. To use the example from earlier, Nara should have 8 temples but the count function returns 45.

Any pointers are appreciated and feel free to ask me to clarify.
Thank you for your time.


I looked at your case and consulted with colleagues. Unfortunately, it will not work to get the desired result with a single query. You need to make two requests and then combine them.


Hi @stanislaw.grin
thank you for the quick reply. How would I best combine two requests? As a business logic?

If you want to get data by with only one request from the client side and transfer the logic of combining the results of two requests to the server side, then yes, it is better to transfer it to the API Service. Personally, I would do so.

Great, thanks!