objection-filter

What is objection-filter?

objection-filter is a plugin for the objection.js ORM. It's designed to allow powerful filters and aggregations on your API.

Some examples of what you can do include:

1. Filtering on nested relations

For example, if you have the models Customer belongsTo City belongsTo Country, we can query all Customers where the Country starts with A.

2. Eagerly loading data

Eagerly load a bunch of related data in a single query. This is useful for getting a list models e.g. Customers then including all their Orders in the same query.

3. Aggregation and reporting

Creating quick counts and sums on a model can speed up development significantly. An example could be the numberOfOrders for a Customer model.

Shortcuts

• Changelog
• Recipes
• Aggregation

Installation

npm i objection-filter --save

objection-filter >= 1.0.0 is fully backwards compatible with older queries, but now supports nested and/or filtering as well as the new objection.js object notation. The 1.0.0 denotation was used due to these changes and the range of query combinations possible. In later major versions of objection-filter, the top level "where" and "require" filters will be deprecated.

Usage

The filtering library can be applied onto every findAll REST endpoint e.g. GET /api/{Model}?filter={"limit": 1}

A typical express route handler with a filter applied:

const { buildFilter } = require('objection-filter');
const { Customer } = require('./models');

app.get('/Customers', function(req, res, next) {
  buildFilter(Customer)
    .build(JSON.parse(req.query.filter))
    .then(customers => res.send(customers))
    .catch(next);
});

Available filter properties include:

// GET /api/Customers
{
  // Filtering and eager loading
  "eager": {
    // Top level $where filters on the root model
    "$where": {
      "firstName": "John"
      "profile.isActivated": true,
      "city.country": { "$like": "A" }
    },
    // Nested $where filters on each related model
    "orders": {
      "$where": {
        "state.isComplete": true
      },
      "products": {
        "$where": {
          "category.name": { "$like": "A" }
        }
      }
    }
  },
  // An objection.js order by expression
  "order": "firstName desc",
  "limit": 10,
  "offset": 10,
  // An array of dot notation fields to select on the root model and eagerly loaded models
  "fields": ["firstName", "lastName", "orders.code", "products.name"]
}

The where operator from < v1.0.0 is still available and can be combined with the eager string type notation. The same is applicable to the require operator. For filtering going forward, it's recommended to use the objection object-notation for eager loading along with $where definitions at each level.

Filter Operators

There are a number of built-in operations that can be applied to columns (custom ones can also be created). These include:

1. $like - The SQL LIKE operator, can be used with expressions such as ab% to search for strings that start with ab
2. $gt/$lt/$gte/$lte - Greater than and Less than operators for numerical fields
3. =/$equals - Explicitly specify equality
4. $in - Whether the target value is in an array of values
5. $exists - Whether a property is not null
6. $or - A top level OR conditional operator

For any operators not available (eg ILIKE, refer to the custom operators section below).

Example

An example of operator usage

{
  "eager": {
    "$where": {
      "property0": "Exactly Equals",
      "property1": {
        "$equals": 5
      },
      "property2": {
        "$gt": 5
      },
      "property3": {
        "$lt": 10,
        "$gt": 5
      },
      "property4": {
        "$in": [ 1, 2, 3 ]
      },
      "property5": {
        "$exists": false
      },
      "property6": {
        "$or": [
          { "$in": [ 1, 2, 3 ] },
          { "$equals": 100 }
        ]
      }
    }
  }
}

Custom Operators

If the built in filter operators aren't quite enough, custom operators can be added. A common use case for this may be to add a lower case LIKE operator, which may vary in implementation depending on the SQL dialect.

Example:

const options = {
  operators: {
    $ilike: (property, operand, builder) =>
      builder.whereRaw('?? ILIKE ?', [property, operand])
  }
};

buildFilter(Person, null, options)
  .build({
    eager: {
      $where: {
        firstName: { $ilike: 'John' }
      }
    }
  })

The $ilike operator can now be used as a new operator and will use the custom operator callback specified.

Logical Expressions

Logical expressions can be applied to both the eager and require helpers. The where top level operator will eventually be deprecated and replaced by the new eager object notation in objection.js.

Examples using $where

The $where expression is used to "filter models". Given this, related fields between models can be mixed anywhere in the logical expression.

{
  "eager": {
    "$where": {
      "$or": [
        { "city.country.name": "Australia" },
        { "city.code": "09" }
      ]
    }
  }
}

Logical expressions can also be nested

{
  "eager": {
    "$where": {
      "$and": {
        "name": "John",
        "$or": [
          { "city.country.name": "Australia" },
          { "city.code": { "$like": "01" }}
        ]
      }
    }
  }
}

Note that in these examples, all logical expressions come before the property name. However, logical expressions can also come after the property name.

{
  "eager": {
    "$where": {
      "$or": [
        { "city.country.name": "Australia" },
        {
          "city.code": {
            "$or": [
              { "$equals": "12" },
              { "$like": "13" }
            ]
          }
        }
      ]
    }
  }
}

The $where will apply to the relation that immediately precedes it in the tree, in the above case "city". The $where will apply to relations of the eager model using dot notation. For example, you can query Customers, eager load their orders and filter those orders by the product.name. Note that product.name is a related field of the order model, not the customers model.

Aggregations

Aggregations such as count, sum, min, max, avg can be applied to the queried model.

Additionally for any aggregations, you can use them in other expressions above including:

• Filtering using $where
• Ordering using order

For more detailed descriptions of each feature, refer to the aggregations section.

Transform a basic aggregation like this on a GET /Customers endpoint:

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

...into a result set like this:

[
  {
    "firstName": "John",
    "lastName": "Smith",
    "numberOfOrders": 10
  },{
    "firstName": "Jane",
    "lastName": "Bright",
    "numberOfOrders": 5
  },{
    "firstName": "Greg",
    "lastName": "Parker",
    "numberOfOrders": 7
  }
]