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.gelly
gelly
field 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.gelly
gelly
field on post {
  (upvotes - downvotes) * 100
}

Computed fields can use data from related models, like computing the total spend of a customer:

customer/totalSpend.gelly
gelly
field 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.gelly
gelly
field on business {
  revenue - costs
}

Then, we can query this field in our API like so:


JavaScript
1await 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    id
4    computedField
5  }
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    id
4    # computedField is not included
5  }
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    id
4    name
5    relatedRecord {
6      id
7      computedField
8    }
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:


JavaScript
1import { applyParams, save } from "gadget-server";
2
3export async function run({ api, record, params }) {
4  const reloadedRecord = await api.customer.findOne(record.id, {
5    select: { id: true, fullName: true },
6  });
7
8  // do something with reloadedRecord.fullName
9  // ...
10  applyParams({ record, params });
11  await save({ record });
12}
13
14export 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.gelly
gelly
field on post {
  author.shadowBanned || markedAsSpam
}

Count the number of comments on a post:

post/commentCount.gelly
gelly
field 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.gelly
gelly
field 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.gelly
gelly
field on shopifyShop {
  count(products, where: !isNull(products.publishedAt))
}

Total the revenue of a Shopify Shop:

shopifyShop/totalRevenue.gelly
gelly
field on shopifyShop {
  sum(cast(orders.totalPrice, type: "Number"))
}

Total the lost revenue of a Shopify Shop for orders that have been canceled:

shopifyShop/totalCancelledRevenue.gelly
gelly
field on shopifyShop {
  sum(cast(orders.totalPrice, type: "Number"), where: !isNull(orders.cancelledAt))
}

Total the current month's revenue within Shopify Shop:

shopifyShop/totalRevenue.gelly
gelly
field 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.gelly
gelly
field 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.