# Computed fields 

computed fields are read-only field types that allow you to transform and aggregate data from multiple records. Gadget optimizes the underlying query performance for these fields, making them ideal for aggregate queries such as counts and sums.

For example, if I wanted to sum the total price of all orders for a customer, I could create the following computed field on a `customer` model:

```gelly
// in api/models/customer/totalSpend.gelly
field on customer {
  sum(orders.totalPrice)
}
```

Then read the value in the `totalSpend` field on a `customer` record:

```typescript
const customer = await api.customer.findOne("123", {
  select: { id: true, totalSpend: true },
});

// Will log the computed value
console.log(customer.totalSpend);
```

Computed field values are dynamically calculated so you don't need to worry about keeping them in sync with the underlying data. They remove the need to pre-aggregate data, and unchanged values are cached for speedy reads.

Computed fields are written in [Gelly](https://docs.gadget-canary.xyz/guides/data-access/gelly), Gadget's data access language.

## Defining computed fields 

To create and use a computed field:

1.  Create the field: Add a computed field to your model in the Gadget editor.
2.  Write the query: A `.gelly` file will automatically be created for your computed field. Write your Gelly expression in this file.
3.  Query the field: The computed field is automatically added to your API. Read the computed field's value for any record in your model.

## Computed field syntax 

Computed fields have a standard format:

```gelly
// in api/models/<model>/<fieldName>.gelly
field on <model> {
  <expression>
}
```

The expression must evaluate to a single value. This value can be calculated by transforming data on a single record or by aggregating values across multiple related records.

### Examples: Transform data on a single record 

#### Simple arithmetic 

If you have a `business` model with `revenue` and `costs` fields, you can calculate the `profit` with a computed field:

```gelly
// api/models/business/profit.gelly
field on business {
  revenue - costs
}
```

Each `business` record will have a `profit` field that computes the difference between `revenue` and `costs`.

#### String transformations 

We can compute a customer record's `fullName` by concatenating the existing `firstName` and `lastName` fields:

```gelly
// in api/models/customer/fullName.gelly
field on customer {
  concat([firstName, " ", lastName])
}
```

### Examples: Aggregate values across multiple related records 

If you want to find the average size of all `orders` for a `store`, where the relationship is defined as `store` has many `orders`:

```gelly
// in api/models/store/averageOrderSize.gelly
field on store {
  avg(orders.totalPrice)
}
```

This calculates a value for each `store` by aggregating across related `order` records.

## Reading computed field values 

You can read computed fields using your app's API client or GraphQL API, or using the `@gadgetinc/react` hooks in your frontend.

Computed fields are **included** by default in responses to queries made using the public API:

```typescript
const record = await api.someModel.findOne(123);
// the computed field is included by default
console.log(record.computedField);
```

```tsx
const [{data, fetching, error}] = useFindOne(api.someModel, "123");
// the computed field is included by default
console.log(data.computedField);
```

```graphql
query {
  someModel(id: 123) {
    id
    computedField
  }
}
```

You can exclude computed fields from public API responses by passing a \[`select`\] param to your API client:

```typescript
const record = await api.someModel.findOne(123, {
  select: { id: true, computedField: false },
});
// the field is not included, will return null
console.log(record.computedField);
```

```tsx
const [{data, fetching, error}] = useFindOne(api.someModel, "123", { select: { id: true, computedField: false } });
// the field is not included, will return null
console.log(data.computedField);
```

```graphql
query {
  someModel(id: 123) {
    id # computedField is not included
  }
}
```

You can also select computed fields from related records by nesting your `select` param:

```typescript
const record = await api.someModel.findOne(123, {
  select: { id: true, relatedRecord: { id: true, computedField: true } },
});
// prints the computed field value
console.log(record.relatedRecord.computedField);
```

```tsx
const [{data, fetching, error}] = useFindOne(api.someModel, "123", { select: { id: true, relatedRecord: { id: true, computedField: true } } });
// prints the computed field value
console.log(data.relatedRecord.computedField);
```

```graphql
query {
  someModel(id: 123) {
    id
    name
    relatedRecord {
      id
      computedField
    }
  }
}
```

### From the internal API 

Computed fields are **excluded** by default when reading data using the internal API. A `select` parameter must be used to include them:

```typescript
const record = await api.internal.someModel.findOne(123, {
  select: { id: true, computedField: false },
});
// the field is not included, will return null
console.log(record.computedField);
```

```graphql
query {
  internal {
    someModel(id: 123, select: ["id", "computedField"])
  }
}
```

### From an action's `record` 

Computed fields are **excluded** from the `record` object in your [action context parameter](https://docs.gadget-canary.xyz/guides/actions/code#record).

There is no way to add computed fields to this record. To access a computed field on this `record` in your action, you must load it manually using the API:

```typescript
import { applyParams, save, ActionOptions } from "gadget-server";

export const run: ActionRun = async ({
  params,
  record,
  logger,
  api,
  connections,
}) => {
  applyParams(params, record);

  // manually fetch the computed field
  const recordWithComputedField = await api.someModel.findOne(record.id, {
    select: {
      computedField: true,
    },
  });
  logger.info({ computedFieldValue: recordWithComputedField.computedField });

  await save(record);
};

export const options: ActionOptions = {
  actionType: "create",
};
```

## Sample computed fields 

Here are more practical examples demonstrating various use cases:

**Marking a post as spam:**

```gelly
// in api/models/post/isSpam.gelly
field on post {
  author.shadowBanned || markedAsSpam
}
```

**Counting comments on a post:**

```gelly
// in api/models/post/commentCount.gelly
field on post {
  count(comments)
}
```

**Total tokens used by a user in the last 30 days (AI app):**

```gelly
// in api/models/user/chatTokenCount.gelly
field on user {
  sum(chatMessages.tokenCount, where: chatMessages.createdAt > now() - interval("30 days"))
}
```

**Total published products for a Shopify Shop:**

```gelly
// in api/models/shopifyShop/publishedProductCount.gelly
field on shopifyShop {
  count(products, where: !isNull(products.publishedAt))
}
```

**Total revenue for a Shopify Shop:**

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

**Total lost revenue for canceled Shopify orders:**

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

**Current month's revenue within a Shopify Shop:**

```gelly
// in api/models/shopifyShop/totalRevenue.gelly
field on shopifyShop {
  sum(cast(orders.totalPrice, type: "Number"), where: (orders.shopifyCreatedAt > dateTrunc(part: "month", date: now())) && isNull(orders.cancelledAt))
}
```

**Compute the third side of a right triangle (Pythagorean theorem):**

```gelly
// in api/models/triangle/c.gelly
field on triangle {
  sqrt(power(a, exponent: 2) + power(b, exponent: 2))
}
```

## 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 to 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 do not 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. If performance becomes critical, you can also choose to pre-compute values and store them in normal fields at write time instead of computing them on read.