npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

objection-filter

v4.4.0

Published

A filter module for objection.js

Downloads

13,401

Readme

CircleCI Coverage Status

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

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
  }
]