SQL querying models data
Hi,
Is there a way to query data in models. When I use the API Playground and write JS to query, it is not able to process large data and its costs in usage hours 😦
I just want to know database counts per shopify store per shopify model to optimize my data storage.
Thanks in advance
15 Replies
Hello,
Please take a look at these docs: https://docs.gadget.dev/guides/data-access/computed-fields#computed-views-vs-computed-fields
Computed Fields - Developer Docs - Gadget
Gadget Docs
Thanks Antoine. Is there a way now to access the data base via sql or something?
No, you need to use Gelly in order to make count queries. You can't use SQL on the database (yet) for security reasons. The database is not one per application but one joint database split for each app
Got it. Thanks Antoine. Will check out Computed fields on gelly.
Hi Antoine,
I setup below in api/models/shopifyShop/views/summary.gelly
view {
id
domain
orderCount: count(orders)
productsCount: count(products)
productVariants: count(productVariants)
discountsCount: count(discounts)
}
This used to work earlier but now I am getting "CombinedError:"[GraphQL] canceling statement due to statement timeout""
I guess its because of large data set 😕, any suggestions how to do this?
Yeah, I would recommend instead making 4 different queries for things like count (unless you have a time range)
Thanks. I did and it worked for 2 out 4. Basically order and discounts which are perhaps the biggest tables, it didnt work for that 😕
Do you know how many records are in the tables? Also, did you make sure to add a where so that you can minimize the amount of records to be fetched?
That is what I am trying to figure out.. how many records per shop are there in the table.. so we can figure out optimization things.
I didnt put a where clause yet.
Do you have a ton of shops to do that for or are you targetting specific domains?
I wanted to find out per shop # of orders and # of discounts. I already cleaned up the uninstalled customers to reduce the size but again I guess that didnt work
Okay, it seems to have worked now on its own.. I guess it takes time for view to get data after view is created.
I don't think that should be the case. Maybe there was an issue when trying to implement the db indexes that the view would need?
The way the view is written it runs 4 subqueries for each shop record, all part of single overall query that is subject to the timeout restrictions. You can run the individual counts more efficiently if you turn the query around. E.g. for
count(orders) you could instead run view { orders { shop: shop.id orderCount: count(id) [ group by shop ] } } . (as a global view, not a model view)
if you wanted a model view on order then the query would be just view { shop: shop.id orderCount: count(id) [ group by shop ] }Not sure but it worked as per expectation and all good now 🙂
Thanks Martin and do I still put this gelly inside shopifyShop i.e. model level or do I need to put this at a global level?
Currently I have this in api/models/shopifyShop/views/orderCount.gelly
view orderCount {
id
domain
orderCount: count(orders)
}
The first version of the view is a global view, so that would go into api/views/, the second version is a model view for orders so it would go into api/models/order/views/.
Got it. Thanks Martin