The most practical and ideal way of creating report with appwrite for complex queries

is there way to write sql queries with appwrite,

Appwrite is an abstraction on top of the underlying database and we recommend retrieving data via the provided APIs.

That said, if you're self hosting, you control the database, so you can connect to it if you'd like

Could you give me some recommendation. let say we are having 10M record of Clients, Meetup, Enrollment tables. I want to count # of clients, # of followup client, # of clients who is male, female, having specific illness for example. what i think, it will be queries alot, the peformance something and speed of responding.

we are showing as a report for dashboard for monitoring.

I would recommend keeping a running count of the data in a collection rather than trying to calculate it every time

like plus, minus every transaction.

@Steven however, this is just few indicators, in case there are hundreds of indicator to count data like this. then, I need to keep count every thing ? lets say, I could not think of . then the manager asks for another 100 indicators which is not kept the count in the collection. then, I will need to count by the query api anyway, any ideas?

Btw, you might want to 👍 this issue: https://github.com/appwrite/appwrite/issues/5667

If you don't want to keep a running count, you'll need to iterate over the data using pagination: https://appwrite.io/docs/pagination.

okay. noted. I would suggest if there is possible in the future of open gateway for sql queries for complex and performance benefits

Make sure to 👍 the issue I linked

okay noted.