Bookshelf Collection Querystring Mutation Plugin

This Bookshelf plugin allows you to filter and sort your bookshelf instances directly from URL query string parameters. Using jsep to parse query string logical expressions into QueryBuilder query.

Inspired by Microsoft API Guideline Collections.

Instalation

$ npm install bookshelf-collection-querystring-mutation

Basic Usage

const express = require("express");
const bookshelf = require("bookshelf");
const knex = require("knex");

const bcqm = reqire("bookshelf-collection-querystring-mutation");

const repository = bookshelf(
  knex({
    /*...*/
  })
);

let app = express();

// Connect Plugin
repository.plugin(bcqm);

// Define Model
const List = repository.Model.extend({
  tableName: "shopping_list",
  allowed: ["name", "qty", "price"],
  hidden: ["barcode"]
});

app.get("/list", function(req, res, next) {
  let items = new List();

  // Mutate from query
  items.filter(req.query.filter).orderBy(req.query.orderBy);

  items.fetchAll().then(r => {
    /* Do stuff. */
  });
});

How to use

GET /list?filter=price ge 10&orderBy=name

Will filter instances that have price >= 10 and order them by name.

Filter Operation

Allows you to filter collection.

Pattern: field1 <operation> value [logical_opration] ...

| Operator | Description | Example | | -------------------- | --------------------- | ----------------------------------------------------- | | Comparison Operators | | | eq | Equal | city eq 'Redmond' | | ne | Not equal | city ne 'London' | | gt | Greater than | price gt 20 | | ge | Greater than or equal | price ge 10 | | lt | Less than | price lt 20 | | le | Less than or equal | price le 100 | | lk | Like | name lk '%gg%' | | il | iLike | name il '%GG%' | | in | in | name in ['GG', 'Salad'] | | is | Is (null) | price is null | | sn | Is Not (null) | price sn null | | Logical Operators | | | and | Logical and | price le 200 and price gt 3.5 | | or | Logical or | price le 3.5 or price gt 200 | | Grouping Operators | | | ( ) | Precedence grouping | (priority eq 1 or city eq 'Redmond') and price gt 100 |

Filter examples:

The following examples illustrate the use and semantics of each of the logical operators.

Example: all products with a name equal to 'Milk'

GET /list?filter=name eq 'Milk'

Example: all products with a name not equal to 'Milk'

GET /list?filter=name ne 'Milk'

Example: all products with the name 'Milk' that also has a price less than 2.55:

GET /list?filter=name eq 'Milk' and price lt 2.55

Example: all products that either have the name 'Milk' or have a price less than 2.55:

GET /list?filter=name eq 'Milk' or price lt 2.55

Example: all products that have the name 'Milk' or 'Eggs' and have a price less than 2.55:

GET /list?filter=(name eq 'Milk' or name eq 'Eggs') and price lt 2.55

Example: all products that have the name matching 'gg':

GET /list?filter=name lk "%gg%"

Example: all products that have ids in array of [1, 2, 3]:

GET /list?filter=id in [1, 2, 3]

Example: all products that have price is set to null:

GET /list?filter=price is null

Example: all products that have price is not set to null:

GET /list?filter=price sn null

OrderBy

Allows you to sort collections.

Pattern: field1 [dirrection(asc|desc)][, field2 [dirrection(asc|desc)], [...]]

OrderBy Examples

Example: will return all products sorted by name in ascending order.

GET /list?orderBy=name

For example: will return all products sorted by name in descending order.

GET /list?orderBy=name desc

Sub-sorts can be specified by a comma-separated list of property names with OPTIONAL direction qualifier.

Example: will return all people sorted by name in descending order and a secondary sort order of price in ascending order.

GET /list?orderBy=name desc,price

Plugin Configuration:

repository.plugin(bcqm, {
  filterName: "filter", //filter function name in BSModel
  orderByName: "orderBy", //orderBy function name in BSModel
  hiddenPropsName: "hidden", //name of Property that provides array of hidden fields
  allowedPropsName: "allowed", //name of Property that provides array of allowed fields
  ignoreErrors: false //throw errors or skip silently
});

Hidden and Allowed arrays provide fields visibility for a plugin. If any filter or orderBy field is an array of private fields, it will throw an Error. If any filter or orderBy field is not an array of allowed fields, it will throw an Error.

You can also not provide this props if you don't care.

Model example:

const List = repository.Model.extend({
  tableName: "shopping_list",
  allowed: ["name", "qty", "price"], //filter and orderBy by this fields
  hidden: ["barcode"] //DON'T by this
});

TODO:

• not Logical operator