Query multiple sub collection

Is it possible to display aggregated data from multiple subcollections?

I’m trying to create a dashboard that displays our order data. I would like to show in a line graph the total orders made every month. My order data is stored as followed:

Locations/{location_document}/orders/

I have multiple locations, each have their own order document. Each order document has a subcollection with all orders stored.

When I select a datasource for the line graph, I can select the subcollection, however I have to define the parent path. In this parent path I can only select one document as parent. Is it possible to select all documents as parent path? I tried to work around this with a multiple select dropdown component displaying all the parent (location) documents. However when I select more than one document, the query doesn’t work.

Is there any way to display the sum of all orders from all subcollections?

Hello @tomgoedhart

Are you using Sync mode?

If you use the Sync mode of Firebase (Firestore) integration with Jet Admin, then all your data is synchronized bilaterally in Jet Tables (actually PostgreSQL under the hood) and you can use SQL queries to your data as if you had a SQL database. And thus build the query that you need. There is no other way to achieve this because otherwise you have to pass parent (this is the necessary value) for the Firebase API to return values.

@JetTeam I’m using sync mode, but I can’t get my query to work.

Here is the data Stucture:
projects/[Doc ID]/tasks/[Doc ID]

Can you give an example Query on how to get all Docs in all the tasks subcollections? Thank YOU!

@Lukas_s We currently have a problem with the Sync mode for Firebase, specifically with getting related collections. We are already aware of this problem and are working on fixing it. Since Sync mode in Jet Tables is still in public beta testing we are actively fixing any problems.