Skip to content

Latest commit

 

History

History
255 lines (215 loc) · 7.36 KB

AGGREGATIONS.md

File metadata and controls

255 lines (215 loc) · 7.36 KB
Raw

Aggregations

Aggregations can currently be added to the base model (not onto eagerly loaded models).

The functions count, sum, min, max, avg are supported, along with a distinct flag.

The aggregated field eg count will appear as if it is a column of the root model.

Basic examples

A base customer model may look something like this:

{
  "firstName": "John",
  "lastName": "Smith"
}

To get the number of orders per Customer, where Customer hasMany Order

{
  "eager": {
    "$aggregations": [
        {
          "type": "count",
          "alias": "numberOfOrders",
          "relation": "orders"
        }
    ]
  }
}

This would yield results which look like:

[
  {
    "firstName": "John",
    "lastName": "Smith",
    "numberOfOrders": 23
  }
]

To get the number of unique products in an order per Customer, where Order hasMany Item belongsTo Product

{
  "eager": {
    "$aggregations": [
        {
          "type": "count",
          "distinct": true,
          "alias": "numberOfUniquelyOrderedProducts",
          "relation": "orders.items.product"
        }
    ]
  }
}

Filtering

Filtering for aggregations is done at the Outer Most model. Given a set of models:

  • Customer hasMany Order hasMany item belongsTo Product

To query the number of times a customer has ordered Apples, the query would look like:

{
  "eager": {
    "$aggregations": [
        {
          "type": "count",
          "alias": "numberOfAppleOrders",
          "relation": "orders.items",
          "$where": {
            "product.name": "Apple"
          }
        }
    ]
  }
}

In the aggregation's $where - the expression product.name is a relation of the outer most model (Item) not the root model (Customer).

To summarize the query above:

  • We are querying the Customer model
  • We are creating an aggregation numberOfAppleOrders based on a relation orders.items
  • orders.items is a relation relative to the Customer model, which corresponds to the Item model
  • We are filtering the aggregation to only count items which are Apples
  • From the Item model, we use $where to filter a relation product.name
  • product.name is a field relative to the Item model

Other aggregation types

To query the total number of Apples ordered by a customer, the query would look like:

{
  "eager": {
    "$aggregations": [
        {
          "type": "sum",
          "alias": "numberOfApplesOrdered",
          "field": "quantity",
          "relation": "orders.items",
          "$where": {
            "product.name": "Apple"
          }
        }
    ]
  }
}

In the above example, the field attribute is specified. The field is also relative to the outer most model (eg quantity is a column of the Item model).

Multiple aggregations

Aggregations can be combined in the same query. Any aggregations should be provided as an array:

{
  "eager": {
    "$aggregations": [
        {
          "type": "count",
          "alias": "numberOfOrders",
          "relation": "orders"
        },
        {
          "type": "sum",
          "alias": "numberOfOrderedItems",
          "relation": "orders.items",
          "field": "quantity"
        }
    ]
  }
}

Filtering results based on aggregations

Sometimes you'll want to get a model, but filter it based on the result of aggregations.

An example is you have a Customer model, with an aggregation on each customer numberOfOrders. You can filter these results to only show customers with more than 10 orders.

{
  "eager": {
    "$aggregations": [
        {
          "type": "count",
          "alias": "numberOfOrders",
          "relation": "orders"
        }
    ],
    "$where": {
      "numberOfOrders": { "$gt": 10 }
    }
  }
}

Ordering results based on aggregations

Sometimes you'll want to aggregate based on a model, but only get the top 10 results ordered by the same aggregated field.

An example is you have a Customer model, with an aggregation on each customer numberOfOrders. You want to show the top 10 customers based on the orders.

{
  "eager": {
    "$aggregations": [
        {
          "type": "count",
          "alias": "numberOfOrders",
          "relation": "orders"
        }
    ]
  },
  "order": "numberOfOrders desc",
  "limit": 10
}

A warning on performance: Whenever you order based on an aggregation, the result will be slower (sometimes significantly). The only way to know the order is to aggregate on every row in the root table. If you have a lot of rows, it's going to be slow and is unavoidable. If you need performant aggregation, look into materialized views or other strategies.

Another method of aggregation filtering

A common problem with querying in general, is limiting views into data. This is often for security reasons, eg slicing data based on tenant.

This is a problem that can occur during eager loading, but also aggregation. Take the following as an example, for a multi-tenant Shop management platform:

Tenant hasMany Shop belongsTo ShopType

In the above example, ShopType is global to the application. It could be a list of items such as [Restaurant, Retail, Service]. When a tenant goes to query their shops, you'll want to filter the list so they only see their own shops. This can be done in objection.js using the onBuild hook:

function sliceByTenant = tenantId => builder => {
  if (builder.modelClass().name === 'Shop')
    builder.where({ tenantId });
};

// Route handler for GET /Shops
async function getShops(req) {
  const { tenantId } = req.decoded; // Using some express middleware
  return await buildFilter(Shop)
    .build(req.query)
    .onBuild(sliceByTenant(tenantId));
};

// Route handler for GET /ShopTypes
async function getShopTypes(req) {
  const { tenantId } = req.decoded; // Using some express middleware
  return await buildFilter(ShopType)
    .build(req.query)
    .onBuild(sliceByTenant(tenantId));
};

Based on the expressjs route handlers above, a user could call GET /Shops to get their list of shops.

They could also call GET /ShopTypes?filter={"eager": "shops"} to get all ShopTypes then eagerly load their shops. The sliceByTenant() build hook ensures that they only see the shops that they own.

Aggregation model hooks

The same thing can be applied for aggregations. We don't want anyone doing GET /ShopTypes and getting a count of all shops, but only their own shops. To do this, the onAggBuild hook can be used. This hook is called every time a model is joined through. For the onBuild hook, it is called once when a query is built.

const createOnAggBuild = tenantId => Model => {
  if (Model.name === 'Shop')
    return Model.query().where({ tenantId });
};

// Route handler for GET /ShopTypes
async function getShopTypes(req) {
  const { tenantId } = req.decoded; // Using some express middleware
  return await buildFilter(ShopType, null, { onAggBuild: createOnAggBuild(tenantId) })
    .build(req.query)
    .onBuild(sliceByTenant(tenantId));
};

Now a query such as:

GET /ShopTypes?filter={
  "eager": {
    "$aggregations": [
        {
          "type": "count",
          "alias": "shopCount",
          "relation": "shops"
        }
    ]
  }
}

will only show counts for the target tenantId.