Q: What is the most annoying way to get a count or sum of values from your database?
A: Paginating over all records, 250 at a time.
If Sesame Street has taught us anything, it’s that knowing how to count quickly is important. Calculating aggregate values for a field on a data model, whether we’re talking about sums, counts, averages, or finding the max or min, are common operations and it’s important to be able to perform them quickly and efficiently.
Starting today, you’ll be able to do all of that even faster in Gadget with the introduction of computed fields, powered by our data access language, Gelly.
Until now, if you wanted to do data aggregation in Gadget, you had two options: paginate through all records in a data model, or pre-aggregate values using a separate model. Both of these options come with benefits and tradeoffs. Pagination is done at read time, so you guarantee accuracy and easily allow for dynamic filtering, but paginating through a large number of records can take a long time. Pre-aggregation can be done to reduce the time it takes to read values, but limits filtering and presents an opportunity for your pre-aggregated values to fall out of sync with your other data tables.
Wouldn’t it be nice if you could just calculate values dynamically directly from the database?
Gadget is introducing computed fields so developers can perform read-time aggregations quickly and efficiently, avoiding the trade-offs of pagination and pre-computation.
With computed fields, you can use a variety of data aggregation functions to push the work required for your aggregation queries directly to the database. You can get counts, sums, averages (and more!) at read time, guaranteeing accuracy, without the time penalty of pagination. Gadget simplifies your code and optimizes for speed by pre-calculating and precomputing as much data as possible, so all you have to do is write your queries, and let us take care of any performance and indexing.
Computed fields are powered by Gelly, Gadget’s own data access language. Gelly is a superset to GraphQL, and provides a declarative way to write queries that are either computed or re-computed across records at the database level, very efficiently. For more information on Gelly, and the differences between Gelly, GraphQL, and SQL, give our Gelly documentation a read.
A computed field Gelly fragment looks something like this:
This would compute the total count of the orders made on a Shopify store. In this case, shopifyShop is the API identifier of a parent model, and orders is a has-many relationship field to the shopifyOrders model.
Want to add some conditions to your query? Not a problem!
Here’s how you could get a count of orders made in the last 7 days:
But it gets better. Computed fields aren’t restricted to data aggregation. You can use them to perform string transformations, such as concatenation or changing cases, as well as basic mathematical and trigonometric operations like getting a square root or converting radians to degrees.
Computed fields are now available as field types on all your data models within Gadget, so you can start performing aggregations in seconds.
Have questions? Reach out on our developer Discord or check out our computed fields documentation to learn more about Gelly.
