Data computations and aggregations 

There are two different strategies you can use to get aggregated values, such as counts or sums, of data stored in a Gadget app: pagination and pre-aggregation.

Pagination 

The first strategy is to paginate through all records at read time. This allows you to apply any filters you want to the GraphQL query and ensures you get an accurate count. The downside to paginating through all records is performance - the maximum page size in Gadget is 250 records; if the total count is 100000 records, walking through them in increments of 250 will take a long time.

When to paginate 

• You are confident that you won't have a large number of total records in your collection
• You have dynamic filtering options that don't allow for counts to be pre-calculated

When to avoid pagination 

• You may need to count a large number of records
• You don't need dynamic options for filtering your counts

Example 

For example, if you want to get the count of all products in a Shopify store:

JavaScript
Copy
1module.exports = async ({ api, record, params, logger }) => {
2  const shopId = "my shop id";
3  const productCount = 0;
4
5  // api is an instance of my Gadget API client
6  let products = await api.shopifyProduct.findMany({
7    // use the maximum page size
8    first: 250,
9    // select the minimum amount of data from the model as needed
10    select: {
11      id: true,
12    },
13    // filter by the shop id
14    // adding more filter conditions is usually a good idea, let the database handle filtering for you!
15    filter: {
16      shop: {
17        equals: shopId,
18      },
19    },
20  });
21
22  productCount += products.length;
23
24  // paginate through all our products, 250 at a time
25  while (products.hasNextPage) {
26    products = await products.nextPage();
27    productCount += products.length;
28  }
29
30  logger.info(
31    { productCount },
32    "contains the total number of products for a given store"
33  );
34};

If you want to serve this count to a frontend, you can use a Global Action or a custom route.

Pre-aggregation and data model changes 

Pre-aggregating data is a great way to speed up calculations, especially on large data sets. Values are calculated at write time, either from scratch or by modifying existing values. By handling most of the calculation at write, read times are greatly improved. The downside to pre-aggregation is that it is more complex to implement. You need to make sure that your aggregated values are always up to date, whether records are created, updated, or deleted. If you need a very dynamic filter or your filter categories are unknown ahead of time, pre-aggregating can be very difficult to implement effectively.

When to pre-aggregate 

• You want or need fast reads for data aggregations
• You have a pre-defined set of filters you can aggregate on

When to avoid pre-aggregation 

• Your filter conditions can be very complex, or filter options are not known ahead of time
• You can guarantee that you will not have a large amount of data to aggregate

Pre-aggregation strategies 

There are various ways to implement a pre-aggregation based on your app requirements. Some potential strategies that might be of use:

Add a field 

Add a field to a parent model to keep track of the number of children and update this field in the child model's create, update, delete, and any custom Actions.

Example 1: Count the total number of products on a Shopify store.

• Add a Products Count number field to the Shopify Shop model
• Increment this field's value using atomic API operations when you create a new product, decrement this field's value when you delete a product

JavaScript
Copy
1// example using atomics to increment productsCount by 1
2
3/**
4 * Effect code for create on Shopify Product
5 * @param { import("gadget-server").CreateShopifyProductActionContext } context - Everything for running this effect, like the api client, current record, params, etc. More on effect context: https://docs.gadget.dev/guides/extending-with-code#effect-context
6 */
7module.exports = async ({ api, record, params }) => {
8  await api.internal.shopifyShop.update(record.shopId, {
9    _atomics: { productsCount: { increment: 1 } },
10  });
11};

Example 2: Count the total number of products and the number of products with a price over $100 in a Shopify store.

You may need the ability to apply some simple filters. You can add additional fields to handle aggregations for filters that are pre-defined.

• Add two new number fields to the Shopify Shop model. Call one Products Count and the other ProductsCount100
• Increment this field's value using atomic API operations when you create a new product, decrement this field's value when you delete a product

JavaScript
Copy
1/**
2 * Effect code for create on Shopify Product
3 * @param { import("gadget-server").CreateShopifyProductActionContext } context - Everything for running this effect, like the api client, current record, params, etc. More on effect context: https://docs.gadget.dev/guides/extending-with-code#effect-context
4 */
5module.exports = async ({ api, record, params }) => {
6  // update the total product count
7  await api.internal.shopifyShop.update(record.shopId, {
8    _atomics: { productsCount: { increment: 1 } },
9  });
10
11  // check to see if there are any variants with a price over $100
12  const variant = await api.shopifyProductVariant.maybeFindFirst({
13    filter: {
14      product: {
15        equals: record.id,
16      },
17      price: {
18        greaterThan: "100",
19      },
20    },
21  });
22
23  if (variant) {
24    // if a $100+ variant is found, increase the other count
25    await api.internal.shopifyShop.update(record.shopId, {
26      _atomics: { productsCount100: { increment: 1 } },
27    });
28  }
29};

Add new models 

Add new models to match the format of the data you need to read.

If you're building an app with a table or visualization, you may want to pre-compute all data required for that element and save it in a separate data model. You can keep track of different filter options using fields and store the filtered chart's data in additional fields or inside a json field. Using JSON enables you to store data in a format that is easily consumable by the chart engine of your choice, and storing this data in a separate model means you can grab it in a single read request.

Example: You have a bar chart showing purchases over time and can select individual customers as a filter. You can also select different time ranges to be used for your bars. For example, you want to show purchases for a customer per year and per month where each bar in the chart is the sum of orders over a year or month, depending on what option is selected.

To handle this in Gadget, you could create a new model to store chart data specifically. This model belongs to a Customer, has an enum to store the selected time range for each bar, and then a json to store the actual chart data. You could add a Code Effect to the Shopify Orders create Action that checks for any current data entries for this customer, modifies the currently saved data, and then updates the records stored in the model for this customer.

Combining pre-aggregation and pagination 

Depending on your use case, it may be enough to refresh pre-aggregated data values on a schedule instead of keeping things fully up to date. You can paginate through all data at a set interval to build up your pre-aggregated data instead of managing pre-aggregations during write requests or paginating through all data at read time.

Example: Generating a daily report or dashboard that is available to view the next day. This dashboard or report may include a monthly or yearly running total that you want to aggregate. You could use a model to save pre-aggregated data and then paginate through all data in a given timeframe at the end of the day. A schedule trigger on a Gadget Global Action can be used to run a job that handles all your pre-aggregation on a regular interval.

Add relationships between existing models 

Adding additional relationships between models can also be a good way to speed up aggregation operations. This may include adding has many relationships, which also creates an intermediary data model.

Example: Counting the total number of Product Variants in a Collection. Collections have many Products through the Collects model, and the Product Variant model belongs to Products. To speed up this count, add a has-many-though relationship between Product Variant and Collection, and then use the Collection ID to filter any read requests on the new intermediary model. You would still need to paginate across returned records or pre-aggregate the count of variants per collection, but adding a relationship makes both of these options easier to compute.

Have questions? 

Having trouble sorting out how to aggregate data for your application? Reach out in our Discord.