criterion

criterion describes sql-where-conditions as objects which can be combined and manipulated

npm install criterion
14 downloads in the last day
292 downloads in the last week
894 downloads in the last month

criterion

Build Status

criterion describes sql-where-conditions as objects which can be combined and manipulated.

criterion is inspired by the mongo query language

mohair uses criterion. the arguments to mohairs where() method are exactly the same as the arguments to the function exported by criterion. mohair is an sql builder which does a lot more than criterion: go check it out.

install

npm install criterion

use

require:

var criterion = require('criterion');

criterion exports a single function. that function can be called with a query object as an argument. a query object describes an sql-where-condition. see the examples below for an idea of possible query objects.

var c = criterion({x: 7, y: 8});

sql and a list of parameter bindings can be generated from the object returned by criterion:

c.sql();        // => 'x = ? AND y = ?'
c.params();     // => [7, 8]

alternatively criterion can be called with a string of raw sql and optional parameter bindings:

var c = criterion('x = ? AND Y = ?', 7, 8);

c.sql();        // => 'x = ? AND y = ?'
c.params();     // => [7, 8]

any criterion and any other object that responds to a sql() and optionally a params() method can be used in place of any value in a query object. this allows you to mix query objects with arbitrary sql:

var c = criterion({x: {$ne: criterion('LOG(y, ?)', 4)}});
c.sql();        // => 'x != LOG(y, ?)'
c.params();     // => [4]

criteria can be combined:

var fst = criterion({x: 7, y: 'a'});
var snd = criterion('z = ?', true);

fst.and(snd).sql();         // => '(x = ?) AND (y = ?) AND (z = ?)'
fst.and(snd).params();      // => [7, 'a', true]

snd.or(fst).sql();          // => '(z = ?) OR (x = ? AND y = ?)'
snd.or(fst).params();       // => [true, 7, 'a']

criteria can be negated:

var c = criterion({x: 7, y: 'a'});
c.not().sql();              // => 'NOT ((x = ?) AND (y = ?))'
c.not().params();           // => [7, 'a']

double negations are removed:

var c = criterion({x: 7, y: 'a'});
c.not().not().sql();        // => '(x = ?) AND (y = ?)'
c.not().not().params();     // => [7, 'a']

and(), or() and not() return new objects. no method ever changes the state of the object it is called on. this enables a functional programming style.

examples

logical
find where x = 7 and y = 'a'
var c = criterion({x: 7, y: 'a'});
c.sql();        // => 'x = ? AND y = ?'
c.params();     // => [7, 'a']

or

var c = criterion([{x: 7}, {y: 'a'}]);

or

var c = criterion('x = ? AND y = ?', 7, 'a');
find where x = 7 or y = 6
var c = criterion({$or: [{x: 7}, {y: 6}]});
c.sql();        // => 'x = ? OR y = ?'
c.params();     // => [7, 6]

or

var c = criterion({$or: {x: 7, y: 6}});

or

var c = criterion('x = ? OR y = ?', 7, 6);
comparison
find where x != 3
var c = criterion({x: {$ne: 3}});
c.sql();        // => 'x != ?'
c.params();     // => [3]

or

var c = criterion('x != ?', 3);
find where x < 3 and y <= 4
var c = criterion({x: {$lt: 3}, y: {$lte: 4}});
c.sql();        // => 'x < ? AND y <= ?'
c.params();     // => [3, 4]

or

var c = criterion('x < ? AND y <= ?', 3, 4);
find where x > 3 and y >= 4
var c = criterion({x: {$gt: 3}, y: {$gte: 4}});
c.sql();        // => 'x > ? AND y >= ?'
c.params();     // => [3, 4]

or

var c = criterion('x > ? AND y >= ?', 3, 4);
find where not (x > 3 and y >= 4)
var c = criterion({$not: {x: {$gt: 3}, y: {$gte: 4}}});
c.sql();        // => 'NOT (x > ? AND y >= ?)'
c.params();     // => [3, 4]

or

var c = criterion('NOT (x > ? AND y >= ?)', 3, 4);
find where x < NOW()
var c = criterion(x: {$lt: criterion('NOW()')});
c.sql();        // => 'x < NOW()'
c.params();     // => []
find where x != LOG(y, 4)
var c = criterion({x: {$ne: criterion('LOG(y, ?)', 4)}});
c.sql();        // => 'x != LOG(y, ?)'
c.params();     // => [4]
find where x is between 5 and 10
var c = criterion('x BETWEEN ? AND ?', 5, 10);
c.sql();        // => 'x BETWEEN ? AND ?'
c.params();     // => [5, 10]
array
find where x is in [1, 2, 3]
var c = criterion({x: [1, 2, 3]});
c.sql();        // => 'x IN (?, ?, ?)'
c.params();     // => [1,2,3]

or

var c = criterion('x IN (?, ?, ?)', 1, 2, 3);
find where x is not in [1, 2, 3]
var c = criterion({x: {$nin: [1, 2, 3]}});
c.sql();        // => 'x NOT IN (?, ?, ?)'
c.params();     // => [1,2,3]

or

var c = criterion('x NOT IN (?, ?, ?)', 1, 2, 3);
null
find where x is null
var c = criterion({x: {$null: true});
c.sql();        // => 'x IS NULL'
c.params();     // => []

or

var c = criterion('x IS NULL');
find where x is not null
var c = criterion({x: {$null: false}});
c.sql();        // => 'x IS NOT NULL'
c.params();     // => []

or

var c = criterion('x IS NOT NULL');

license: MIT

npm loves you