Computed fields
computed fields are read-only field types that can transform and aggregate data from many records. Computed fields shift the burden of query performance from you onto Gadget, so for aggregate queries like counts and sums, Gadget recommends leveraging computed.
For example, if you wanted to display the total spend of a shopper on their profile, you could add a computed field called totalSpend
to a customer
model, and teach that field to store the results of a query that sums the total spend of the customer across all orders. By using computed fields, you no longer have to:
- aggregate data ahead of time when you write it
- remember to re-run the query when any of its inputs change (e.g. if a new order was placed by the customer)
- keep the query performant or manage database indexes
Queries to the Gadget database via computed fields are written in Gelly, Gadget's expression language. Gelly is a simple and easy-to-learn language that closely resembles SQL and GraphQL. Learn more about Gelly in the Gelly guide.
Snippet syntax
Computed field Gelly snippets define a new field with the field
keyword, and then have one expression within them. This expression can use other fields from the model to do rich computation.
For example, we can compute a customer
record's fullName
with the following Gelly snippet that uses the firstName
and lastName
string fields:
customer/fullName.gellygellyfield on customer {concat([firstName, " ", lastName])}
Computed fields can do arithmetic as well, like computing a post's score from its upvotes and downvotes:
post/score.gellygellyfield on post {(upvotes - downvotes) * 100}
Computed fields can use data from related models, like computing the total spend of a customer:
customer/totalSpend.gellygellyfield on customer {sum(orders.totalPrice)}
Using a Computed field
You can create and use a computed field in three steps:
- Add the field to the main model whose data you want to compute with and select the computed field type
- Define the code for the field in the
.gelly
snippet file created for your field - Query the computed field in your API along with any other fields you like.
For example, say your app has a model named business
with number fields revenue
and costs
. We can add a new computed field to the business
model called profit
with this Gelly snippet:
models/profit.gellygellyfield on business {revenue - costs}
Then, we can query this field in our API like so:
JavaScript1await api.business.findMany({2 select: {3 name: true,4 profit: true,5 },6});
Your Gelly snippet can support a wide variety of operators and functions, for more information refer to the Gelly reference.
Accessing computed fields on the frontend through your API
computed fields can be accessed the same way as all your other fields from the frontend: using your API client or your auto-generated GraphQL API.
const record = await api.someModel.findOne(123, {select: { id: true, computedField: true },});console.log(record.computedField); // => value from the backend
const [{ data, fetching, error }] = useFindOne(api.someModel, "123", {select: { id: true, computedField: true },});console.log(data.computedField); // => value from the backend
1query {2 someModel(id: 123) {3 id4 computedField5 }6}
You can choose to exclude computed fields by passing a [select
] param to your API client:
const record = await api.someModel.findOne(123, {select: { id: true, computedField: false },});console.log(record.computedField); // => null
const [{ data, fetching, error }] = useFindOne(api.someModel, "123", {select: { id: true, computedField: false },});console.log(data.computedField); // => null
1query {2 someModel(id: 123) {3 id4 # computedField is not included5 }6}
And you can select computed fields from related records by nesting your select
param:
const record = await api.someModel.findOne(123, {select: { id: true, name: true, relatedRecord: { id: true, computedField: true } },});console.log(record.relatedRecord.computedField); // => value from the backend
const [{ data, fetching, error }] = useFindOne(api.someModel, "123", {select: { id: true, name: true, relatedRecord: { id: true, computedField: true } },});console.log(data.relatedRecord.computedField); // => null
1query {2 someModel(id: 123) {3 id4 name5 relatedRecord {6 id7 computedField8 }9 }10}
On the frontend, computed fields are included by default if you don't pass a select
param.
Accessing computed fields on the backend
On the backend, computed fields are excluded by default on the record
object.
In order to keep your actions fast by default, Gadget does not include computed fields in the record
object passed to your action code. Instead, you must load any computed field values manually if required for your action. See the performance section for more details.
For example, if working on the update
action of a customer
model, you wanted to access the value of the customer object's fullName
computed field, you can load it with an API call:
JavaScript1import { applyParams, save } from "gadget-server";23export async function run({ api, record, params }) {4 const reloadedRecord = await api.customer.findOne(record.id, {5 select: { id: true, fullName: true },6 });78 // do something with reloadedRecord.fullName9 // ...10 applyParams({ record, params });11 await save({ record });12}1314export const options = {15 actionType: "update",16};
Example computed fields
Check spamminess of a post in a blog, where the post
model has a markedAsSpam
boolean column, and then a shadowBanned
boolean column on a related author
model:
post/isSpam.gellygellyfield on post {author.shadowBanned || markedAsSpam}
Count the number of comments on a post:
post/commentCount.gellygellyfield on post {count(comments)}
In an AI app with a chatMessage
model that has a tokenCount
number field describing how many tokens were used to generate the message, count the total number of tokens a user has used in the past 30 days:
user/chatTokenCount.gellygellyfield on user {sum(chatMessages.tokenCount, where: chatMessages.createdAt > now() - interval("30 days"))}
Count the total number of published products for a Shopify Shop when using the Shopify Connection:
shopifyShop/publishedProductCount.gellygellyfield on shopifyShop {count(products, where: !isNull(products.publishedAt))}
Total the revenue of a Shopify Shop:
shopifyShop/totalRevenue.gellygellyfield on shopifyShop {sum(cast(orders.totalPrice, type: "Number"))}
Total the lost revenue of a Shopify Shop for orders that have been canceled:
shopifyShop/totalCancelledRevenue.gellygellyfield on shopifyShop {sum(cast(orders.totalPrice, type: "Number"), where: !isNull(orders.cancelledAt))}
Total the current month's revenue within Shopify Shop:
shopifyShop/totalRevenue.gellygellyfield on shopifyShop {sum(cast(orders.totalPrice, type: "Number"), where: (orders.shopifyCreatedAt > dateTrunc(part: "month", date: now())) && isNull(orders.cancelledAt))}
Compute the length of the third side of a triangle with number fields for sides a
and b
:
triangle/c.gellygellyfield on triangle {sqrt(power(a, exponent: 2) + power(b, exponent: 2))}
Pricing
Including or excluding a computed field from your API calls in Gadget does not change the price of the API call. API calls are charged for the number of records returned, not the number of fields returned or the number of records scanned in the database. API calls will still be charged for database reads based on the number of records returned.
For more information on Gadget's billing, see the Usage and billing guide.
Performance
Gadget executes computed fields under the hood using SQL statements doing read-time aggregation. This means that if your computed field is aggregating over a significant number of records, it can take some time for your hosted database the execute the query. Gadget automatically optimizes the layout and indexes in your database to ensure your computed fields are computed quickly, but they can certainly add time to the duration of your API calls.
If you don't need the value of a computed field for a query, you can omit it from your query with select: { someComputedField: false }
and it will not be computed.