criterion - npm Package Compare versions

Comparing version 0.3.3 to 0.4.0-rc.1

package.json

 {
   "name": "criterion",
-  "version": "0.3.3",
-  "description": "criterion describes sql-where-conditions as objects which can be combined and manipulated",
-  "keywords": ["sql", "condition", "where", "query"],
+  "version": "0.4.0-rc.1",
+  "description": "criterion allows you to work with (build, combine, reuse, ...) SQL-where-conditions ('x = 5 AND y IS NOT NULL'...) as data (goodbye string-concatenation) and compile them to SQL: it has a succinct mongodb-like query-language, a simple and elegant functional API, is reasily extended just by implementing 2 functions and gets out of your way: you can always drop down to raw-sql",
+  "keywords": [
+    "sql",
+    "condition",
+    "where",
+    "clause",
+    "query",
+    "mongodb",
+    "parser",
+    "compiler",
+    "builder",
+    "dsl",
+    "language",
+    "immutable",
+    "postgres",
+    "orm"
+  ],
   "homepage": "http://github.com/snd/criterion",
@@ -25,3 +40,3 @@ "author": {
   "engines": {
-    "node": ">=0.8.5"
+    "node": ">=0.8.0"
   },
@@ -31,13 +46,14 @@ "dependencies": {
   "devDependencies": {
-    "coffee-script": "1.7.1",
-    "nodeunit": "0.8.6"
+    "coffee-script": "1.8.0",
+    "nodeunit": "0.9.0"
   },
-  "main": "src/factory",
   "scripts": {
-    "prepublish": "coffee --bare --compile --output src src/*.coffee",
-    "postpublish": "rm src/*.js",
-    "pretest": "coffee --bare --compile --output src src/*.coffee && coffee --bare --compile --output test test/*.coffee",
-    "posttest": "rm src/*.js && rm test/*.js",
-    "test": "nodeunit test/*.js"
-  }
+    "compile": "node_modules/coffee-script/bin/coffee --bare --compile --output src src/*.coffee",
+    "clean": "rm -f src/*.js",
+    "prepublish": "node_modules/coffee-script/bin/coffee --bare --compile --output src src/*.coffee",
+    "pretest": "rm -f src/*.js",
+    "test": "node_modules/nodeunit/bin/nodeunit test/*.coffee || :",
+    "posttest": "node_modules/coffee-script/bin/coffee --bare --compile --output src src/*.coffee"
+  },
+  "main": "src/criterion"
 }

README.md

 # criterion
 
-[![Build Status](https://travis-ci.org/snd/criterion.png)](https://travis-ci.org/snd/criterion)
+[![NPM version](https://badge.fury.io/js/criterion.svg)](http://badge.fury.io/js/criterion)
+[![Build Status](https://travis-ci.org/snd/criterion.svg?branch=master)](https://travis-ci.org/snd/criterion/branches)
+[![Dependencies](https://david-dm.org/snd/criterion.svg)](https://david-dm.org/snd/criterion)
 
-criterion describes sql-where-conditions as objects which can be combined
-and manipulated.
+#### ABOUT THIS VERSION !
 
-criterion is inspired by the
-[mongodb query language](http://www.mongodb.org/display/DOCS/Advanced+Queries)
+**this is the readme for criterion version `0.4.0` - a work in progress.
+release candidate `0.4.0-rc.1` is published to npm:
+the api is stable, the implementation complete, the tests numerous and passing,
+the documentation (this readme) still needs some love.
+to see the readme for `0.3.3` [click here](https://github.com/snd/criterion/tree/0808d66443fd72aaece2f3e5134f49d3af0bf72e) !
+to see what has changed in `0.4.0` [click here](#changelog).**
 
-- [mesa, mohair and criterion](#mesa-mohair-and-criterion)
-- [install](#install)
-- [basic usage](#basic-usage)
-- [usage examples](#usage-examples)
-  - [equal](#equal-find-where-x--7)
-  - [not equal](#not-equal-find-where-x--3)
-  - [and](#and-find-where-x--7-and-y--a)
-  - [or](#or-find-where-x--7-or-y--6)
-  - [lower than](#lower-than-find-where-x--3-and-y--4)
-  - [greater than](#greater-than-find-where-x--3-and-y--4)
-  - [between](#between-find-where-x-is-between-5-and-10)
-  - [not](#not-find-where-not-x--3-and-y--4)
-  - [sql function](#sql-function-find-where-x--logy-4)
-  - [in](#in-find-where-x-is-in-1-2-3)
-  - [not in](#not-in-find-where-x-is-not-in-1-2-3)
-  - [null](#null-find-where-x-is-null)
-  - [not null](#not-null-find-where-x-is-not-null)
-- [combining criteria](#combining-criteria)
-  - [and](#and)
-  - [or](#or)
-  - [not](#not)
-- [license](#license-mit)
+> criterion allows you to work with (build, combine, reuse, ...) SQL-where-conditions
+> (`x = 5 AND y IS NOT NULL`...) as data
+> (goodbye string-concatenation)
+> and compile them to SQL:
+> it has a *succinct* [mongodb-like query-language](#condition-objects),
+> a *simple* and *elegant* functional API,
+> is [reasily extended](#the-sql-fragment-interface)
+> just by implementing 2 functions
+> and *gets out of your way*: you can always drop down to [raw-sql](#raw-sql).
 
-### [mesa](http://github.com/snd/mesa), [mohair](http://github.com/snd/mohair) and [criterion](http://github.com/snd/criterion)
+- [background](#background)
+- [introduction](#get-started)
+  - [install (`npm install criterion`)](#install)
+  - [require (`var criterion = require('criterion');`)](#require)
+  - [condition-objects (`var c = criterion({x: 7, y: {$lt: 5}});`)](#condition-objects)
+  - [raw-sql (`var c = criterion('LOG(y, ?)', 5);`)](#raw-sql)
+  - [the sql-fragment interface](#the-sql-fragment-interface)
+- [for users of mesa and mohair](#for-users-of-mesa-and-mohair)
+- [reference by example](#reference-by-example)
+  - [how to read this reference](#how-to-read-this-reference)
+  - [comparisons](#comparisons)
+    - [equal (`{x: 1}` -> `x = ?`)](#equal)
+    - [not equal (`{x: {$ne: 1}}` -> `x != ?`)](#not-equal)
+    - [lower than (`{x: {$lt: 1}}` -> `x < ?`, `{x: {$lte: 1}}` -> `x <= ?`)](#lower-than)
+    - [greater than (`$gt` -> `>`, `$gte` -> `>=`](#greater-than)
+    - [null (`{$null: true}` -> `IS NULL`)](#null)
+    - [not null (`{$null: false}` -> `IS NOT NULL`)](#not-null)
+  - [boolean operations](#boolean-operations)
+    - [and (`{x: 1, y: {$lt: 2}}` -> `(x = ?) AND (y < ?)`)](#and)
+    - [or (`{$or: {x: 1, y: {$lt: 2}}}` -> `(x = ?) OR (y < ?)`)](#or)
+    - [not (`{$not: {x: 1}}` -> `NOT (x = ?)`)](#not)
+  - [lists of scalar expressions](#lists-of-scalar-expressions)
+    - [in list (`{x: [1, 2, 3]}` -> `x IN (?, ?, ?)`)](#in-list)
+    - [not in list (`{x: {$nin: [1, 2, 3]}}` -> `x NOT IN (?, ?, ?)`)](#not-in-list)
+  - [subqueries](#subqueries)
+    - [in subquery (`{x: subquery}` -> `x IN subquery`)](#in-subquery)
+    - [not in subquery (`{x: {$nin: subquery}}` -> `x NOT IN subquery`)](#not-in-subquery)
+    - [exists - whether subquery returns any rows](#exists-whether-subquery-returns-any-rows)
+    - [row-wise comparison with subqueries](#row-wise-comparison-with-subqueries)
+- [advanced topics](#advanced-topics)
+  - [combining criteria with `.and()`](#combining-criteria-with-and)
+  - [combining criteria with `.or()`](#combining-criteria-with-or)
+  - [negating criteria with `.not()`](#negating-criteria-with-not)
+  - [escaping column names with `.sql(escape)`](#escaping-column-names)
+  - [param array explosion](#param-array-explosion)
+- [changelog](#changelog)
+- [license: MIT](#license-mit)
 
-**criterion is part of a set of three libraries whose goal is to make sql with nodejs simple and elegant:**
+## background
 
-[criterion](http://github.com/snd/criterion) describes sql-where-conditions as objects which can be combined
-and manipulated.
+criterion is part of three libraries for nodejs that make SQL with nodejs:
 
-[mohair](http://github.com/snd/mohair) is a simple and flexible sql builder with a fluent interface.
-*mohair uses criterion.*
+- [simple](http://www.infoq.com/presentations/Simple-Made-Easy),
+- DRY
+- extendable
+- well documented
 
-[mesa](http://github.com/snd/mesa) is not an orm. it aims to help as much as possible with the construction, composition and execution of sql queries while not restricting full access to the underlying database driver and database in any way.
-*mesa uses mohair.*
+> make SQL with Nodejs
+> succinct,
+> DRY,
+> functional
+> data-driven
+> composable
+> flexible
+- free
+- close to the metal (sql, database, database-driver)
+- and FUN !
 
-the arguments to mohairs and mesas `where()` method are **exactly** the same as the arguments to the function exported by criterion.
+- succinct 
+- FUN !
 
-mesa supports all methods supported by mohair with some additions.
-look into mohairs documentation to get the full picture of what's possible with mesa.
+used in production
 
+short code
+
+high quality
+
+- few lines of high quality code
+
+well tested
+
+philosophy
+
+#### [CRITERION](http://github.com/snd/criterion) <- you are looking at it
+
+parses SQL-where-conditions from a mongodb-like query-language into
+objects which it can compile to SQL
+
+#### [MOHAIR](http://github.com/snd/mohair)
+
+a powerful SQL-query-builder with a fluent, functional, side-effect-free API.
+
+uses [criterion](http://github.com/snd/criterion) to build and combine its SQL-where-clauses.
+
+#### [MESA](http://github.com/snd/mesa)
+
+helps as much as possible with the construction, composition and execution of SQL-queries while not restricting full access to the database in any way.
+
+is not an ORM !
+
+uses [mohair](http://github.com/snd/mohair) to build its SQL-queries.
+
+uses [criterion](http://github.com/snd/criterion) (through [mohair](http://github.com/snd/mohair)) to build and combine its SQL-where-clauses.
+
+## introduction
+
 ### install
@@ -58,261 +128,818 @@
 
-**or**
+### require
 
-put this line in the dependencies section of your `package.json`:
+``` js
+var criterion = require('criterion');
+```
 
+criterion exports a single function `criterion()` which
+can be called either with a [condition-object](#condition-objects)
+or with [raw-sql](#raw-sql):
+
+### condition-objects
+
+a *condition-object* describes an SQL-where-condition
+as data using a *query-language* that is inspired by the
+[mongodb query language](http://docs.mongodb.org/manual/tutorial/query-documents/).
+
+let's make a *condition-object*:
+
+``` js
+var condition = {
+  a: 7,
+  b: {$lt: 5},
+  $not: {
+    $or: {
+      c: [1, 2, 3],
+      d: {$null: false}
+    }
+  }
+};
 ```
-"criterion": "0.3.3"
+
+you see that the *query-language* uses special *modifier-keys* to model comparisons (`$lt`), boolean operations (`$not`, `$or`)
+and [much much more](#reference-by-example) (not unlike the [mongodb query language](http://docs.mongodb.org/manual/tutorial/query-documents/)).
+
+now we can make a *criterion* from the *condition-object*:
+
+``` js
+var c = criterion(condition);
 ```
 
-and run:
+we can then compile the *criterion* to SQL:
 
+``` js
+c.sql();
+// ->
+// '(a = ?)
+//  AND
+//  (b < ?)
+//  AND
+//  NOT (
+//    (c IN (?, ?, ?))
+//    OR
+//    (d IS NOT NULL)
+//  )'
 ```
-npm install
+
+we can also get the bound parameters of the *criterion*:
+
+```js
+c.params();
+// -> [7, 5, 1, 2, 3]
 ```
 
-### basic usage
+[see the reference below for examples on how to model almost every SQL-where-condition using *condition-objects* !](#reference-by-example)
 
-```javascript
-var criterion = require('criterion');
+### raw-sql
+
+*raw-sql* is a string of SQL followed by some optional parameter bindings.
+
+use *raw-sql* for those rare cases where condition-objects and you have to fall back to using strings.
+
+note that [*condition-objects* and *raw-sql* can be mixed](#mixing-condition-objects-and-sql-fragments) to keep *raw-sql* to a minimum.
+
+``` js
+var c = criterion('LOG(y, ?)', 4);
 ```
-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 usage examples](#usage-examples) below for examples of the possible query objects:
 
-```javascript
-var c = criterion({x: 7, y: 8});
+a criterion made from *raw-sql* behaves exactly like one made from
+a *condition-object*:
+
+you can get the sql:
+
+```js
+c.sql();
+// -> 'LOG(y, ?)'
 ```
 
-sql and a list of parameter bindings can be generated
-from the object returned by criterion:
+...and the bound parameters:
 
-```javascript
-c.sql();        // => 'x = ? AND y = ?'
-c.params();     // => [7, 8]
+```js
+c.params();
+// -> [4]
 ```
 
-alternatively criterion can be called with a string of **raw sql** and optional parameter bindings:
+in fact both the criterion made from *raw-sql* and one made from
+a *condition-object* are *sql-fragments*:
 
-```javascript
-var c = criterion('x = ? AND Y = ?', 7, 8);
+### the sql-fragment interface
 
-c.sql();        // => 'x = ? AND y = ?'
-c.params();     // => [7, 8]
-```
+in
+[mesa](http://github.com/snd/mesa),
+[mohair](http://github.com/snd/mohair)
+and
+[criterion](http://github.com/snd/criterion)
+every object that has a `.sql()` method and a `.params()` method
+is an implements the [sql-fragment](#the-sql-fragment-interface) interface
+and is an [sql-fragment](#the-sql-fragment-interface).
 
-if a param is an array the corresponding binding `?` is exploded into a list of `?`:
+more precisely:
 
-```javascript
-var d = criterion('x = ? AND y IN (?)', 7, [8, 9, 10]);
+the `.sql()` method should return a string of valid SQL.
+the `.sql()` method might be called with a single argument:
+a function `escape()` which takes a string and returns a string.
+when the `escape()` function is present then the `.sql()` method should call it
+to transform table- and column-names in the returned SQL.
 
-d.sql();        // => 'x = ? AND y IN (?, ?, ?)'
-d.params();     // => [7, 8, 9, 10]
+if `.sql()` constructs the SQL on-the-fly that should be straightforward.
+in the case of *raw-sql* escaping is complex, ambigous and not worth the effort.
 
-var e = criterion('x = ? AND (y && ARRAY[?])', 7, [8, 9, 10]);
+the `.params()` method takes no arguments and must return an array.
 
-e.sql();        // => 'x = ? AND (y && ARRAY[?, ?, ?])'
-e.params();     // => [7, 8, 9, 10]
+#### things that are sql-fragments (already)
+
+- EVERY *criterion*:
+  - `criterion({x: 7})`
+  - `criterion('LOG(y, ?)', 4)`
+- EVERY [mesa](http://github.com/snd/mesa)-query or [mohair](http://github.com/snd/mohair)-query:
+  - `mesa.table('post')`
+  - `mesa.table('post').where({id: 7})`
+  - `mohair.table('host')`
+  - `mohair.table('host').select('name').where({created_at: {$lt: new Date()}})`
+- EVERY return value of [mesa's](http://github.com/snd/mesa) or [mohair's](http://github.com/snd/mohair) `.raw()` method:
+  - `mesa.raw('LOG(y, ?)', 4)`
+  - `mohair.raw('LOG(y, ?)', 4)`
+- EVERY object you create that implements the [sql-fragment interface](#sql-fragment-interface)
+
+#### mixing condition-objects and sql-fragments
+
+now to the FUN part !
+
+**ANY** *sql-fragment* can be used in place of **ANY** value in a [condition-object](#condition-objects):
+
+``` js
+var c = criterion({
+  x: criterion('crypt(?, gen_salt(?, ?))', 'password', 'bf', 4)
+});
+
+c.sql();
+// -> 'x = (crypt(?, gen_salt(?, ?)))'
+c.params();
+// -> ['password', 'bf', 4]
 ```
 
-**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:
+or
 
-```javascript
+``` js
 var c = criterion({x: {$ne: criterion('LOG(y, ?)', 4)}});
-c.sql();        // => 'x != LOG(y, ?)'
-c.params();     // => [4]
+
+c.sql();
+// -> 'x != LOG(y, ?)'
+c.params();
+// -> [4]
 ```
 
-### usage examples
+you see how this allows mixing *condition-objects* with arbitrary sql: use it to keep [raw-sql](#raw-sql) to a minimum !
 
-#### equal: find where `x = 7`
+*sql-fragments* can be mixed with *condition-objects* inside boolean operations:
 
-```javascript
+``` js
+var c = criterion({
+  $or: [
+    criterion('x BETWEEN ? AND ?', 5, 10),
+    {y: {$ne: 12}}
+    [
+      criterion('x != LOG(y, ?)', 4)}}),
+      {x: {$lt: 10}}
+    ]
+  ]
+});
+
+c.sql();
+// ->
+// '(x BETWEEN ? AND ?)
+//  OR
+//  (y != ?)
+//  OR
+//  (
+//    (x != LOG(y, ?))
+//    AND
+//    (x < ?)
+//  )'
+c.params();
+// -> [5, 10, 12, 4, 10]
+```
+
+last but not least:
+
+the fact that [mohair](http://github.com/snd/mohair)/[mesa](http://github.com/snd/mesa)-queries are *sql-fragments*
+allows you to model subqueries with mohair/mesa
+and then use them directly in *condition-objects*.
+this makes the creation of SQL-where-conditions that contain subqueries quite elegant:
+[see the examples !](#subqueries)
+
+#### making your own fragments
+
+if we wanted to support [see also](http://www.postgresql.org/docs/9.4/static/functions-json.html)
+
+``` js
+
+var pgJsonGet = function(left, right) {
+  var leftF = criterion.coerceToSqlFragment(left);
+  var rightF = criterion.coerceToSqlFragment(right);
+  return {
+    sql: function(escape) {
+      return left.sql(escape) + 
+    },
+    params: function() {
+
+    }
+  };
+};
+```
+
+and use them like this
+
+``` js
+var c = criterion({
+  $or: [
+    criterion('x BETWEEN ? AND ?', 5, 10),
+    {y: {$ne: 12}}
+    [
+      criterion('x != LOG(y, ?)', 4)}}),
+      {x: {$lt: 10}}
+    ]
+  ]
+});
+```
+
+there is a library that does that for you.
+
+## for users of mesa and mohair
+
+[EVERYTHING possible with criterion](http://github.com/snd/criterion#reference-by-example) is possible
+for the where conditions in
+[mesa](http://github.com/snd/mesa)
+and [mohair](http://github.com/snd/mohair) !
+
+the [criterion reference](http://github.com/snd/criterion#reference-by-example) completes mesa's and mohair's documentation !
+
+here's why:
+
+the criterion module exports a single function: `var criterion = require('criterion')`
+
+[mesa's](http://github.com/snd/mesa) and [mohair's](http://github.com/snd/mohair) fluent `.where()` methods
+call `criterion()` under the hood and forward all their arguments **unmodifed** to `criterion()`.
+this means that all arguments supported by `criterion()` are supported by `.where()` !
+
+``` js
+// same condition-object
+var condition = {x: 7};
+
+// criterion
+var criterion = require('criterion');
+var c = criterion(condition);
+c.sql();
+// -> 'x = ?'
+c.params();
+// -> [7]
+
+// mohair
+var mohair = require('mohair');
+var query = mohair
+  .table('post')
+  .where(condition);
+query.sql();
+// -> 'SELECT * FROM post WHERE x = ?'
+query.params();
+// -> [7]
+```
+
+if `.where()` is called more than once the resulting criteria are [ANDed](#combining-criteria-with-and) together:
+
+``` js
+var mohair = require('mohair');
+
+var postTable = mohair.table('post')
+var queryAlpha = postTable.where({x: 7});
+var queryBravo = queryAlpha.where('y IN (?)', [1, 2]);
+
+postTable.sql();
+// -> 'SELECT * FROM post'
+postTable.params();
+// -> []
+
+queryAlpha.sql();
+// -> 'SELECT * FROM post WHERE x = ?'
+queryAlpha.params();
+// -> [7]
+
+queryBravo.sql();
+// -> 'SELECT * FROM post WHERE x = ? AND y IN (?, ?)'
+queryBravo.params();
+// -> [7, 1, 2]
+```
+
+calling methods on does not but
+refines
+
+this is one of the nice properties of mohair and mesa.
+
+## reference by example
+
+### how to read this reference
+
+
+
+*for each section several examples are given and seperated by "or".
+the criteria created in the examples behave identical.
+the first example in each section
+uses condition-objects and is always the preferred way of doing things !*
+
+### comparisons
+
+#### equal
+
+where `x = 7`:
+
+``` js
 var c = criterion({x: 7});
-c.sql();        // => 'x = ?'
-c.params();     // => [7]
+c.sql();
+// -> 'x = ?'
+c.params();
+// -> [7]
 ```
-or
-```javascript
+
+or raw:
+
+``` js
 var c = criterion('x = ?', 7);
 ```
 
-#### not equal: find where `x != 3`
+or functional:
 
-```javascript
+``` js
+var c = criterion.eq(criterion.escape(x), 7);
+```
+
+#### not equal
+
+where `x != 3`:
+
+``` js
 var c = criterion({x: {$ne: 3}});
-c.sql();        // => 'x != ?'
-c.params();     // => [3]
+c.sql();    // -> 'x != ?'
+c.params(); // -> [3]
 ```
-or
-```javascript
+
+or raw:
+
+``` js
 var c = criterion('x != ?', 3);
 ```
 
-#### and: find where `x = 7` and `y = 'a'`
+or functional:
 
-```javascript
+``` js
+var c = criterion.ne(criterion.escape(x), 3);
+```
+
+#### lower than
+
+where `x < 3` and `y <= 4`:
+
+``` js
+var c = criterion({x: {$lt: 3}, y: {$lte: 4}});
+c.sql();
+// -> 'x < ? AND y <= ?'
+c.params();
+// -> [3, 4]
+```
+
+or raw:
+
+``` js
+var c = criterion('x < ? AND y <= ?', 3, 4);
+```
+
+or functional:
+
+``` js
+var c = criterion.and(
+  criterion.lt(criterion.escape('x'), 3),
+  criterion.lte(criterion.escape('y'), 4)
+);
+```
+
+#### greater than
+
+where `x > 3` and `y >= 4`:
+
+``` js
+var c = criterion({x: {$gt: 3}, y: {$gte: 4}});
+c.sql();
+// -> 'x > ? AND y >= ?'
+c.params();
+// -> [3, 4]
+```
+
+or raw:
+
+``` js
+var c = criterion('x > ? AND y >= ?', 3, 4);
+```
+
+or functional:
+
+``` js
+var c = criterion.and(
+  criterion.gt(criterion.escape('x'), 3),
+  criterion.gte(criterion.escape('y'), 4)
+);
+```
+
+#### null
+
+where `x` is `null`
+
+``` js
+var c = criterion({x: {$null: true});
+c.sql();
+// -> 'x IS NULL'
+c.params();
+// -> []
+```
+
+or raw:
+
+``` js
+var c = criterion('x IS NULL');
+```
+
+or functional:
+
+``` js
+var c = criterion.null(criterion.escape('x'), true);
+// true is default
+```
+
+#### not null
+
+where `x` is not `null`:
+
+``` js
+var c = criterion({x: {$null: false}});
+c.sql();
+// -> 'x IS NOT NULL'
+c.params();
+// -> []
+```
+
+or raw:
+
+``` js
+var c = criterion('x IS NOT NULL');
+```
+
+or functional:
+
+``` js
+var c = criterion.null(criterion.escape('x'), false);
+```
+
+### boolean operations
+
+`$or`, `$and` and `$not` can be nested arbitrarily.
+
+#### and
+
+where `x = 7` and `y = 'a'`:
+
+``` js
 var c = criterion({x: 7, y: 'a'});
-c.sql();        // => 'x = ? AND y = ?'
-c.params();     // => [7, 'a']
+c.sql();
+// -> 'x = ? AND y = ?'
+c.params();
+// -> [7, 'a']
 ```
-or
-```javascript
+
+or using an array:
+
+``` js
 var c = criterion([{x: 7}, {y: 'a'}]);
 ```
-or
-```javascript
-var c = criterion('x = ? AND y = ?', 7, 'a');
+
+or more verbose:
+
+``` js
+var c = criterion({$and: {x: 7, y: 'a'}});
 ```
 
-#### or: find where `x = 7` or `y = 6`
+or more verbose using an array:
 
-```javascript
-var c = criterion({$or: [{x: 7}, {y: 6}]});
-c.sql();        // => 'x = ? OR y = ?'
-c.params();     // => [7, 6]
+``` js
+var c = criterion({$and: [{x: 7}, {y: 'a'}]});
 ```
-or
-```javascript
-var c = criterion({$or: {x: 7, y: 6}});
+
+or raw:
+
+``` js
+var c = criterion('x = ? AND y = ?', 7, 'a');
 ```
-or
-```javascript
-var c = criterion('x = ? OR y = ?', 7, 6);
+
+or functional:
+
+``` js
+var c = criterion.and(
+  criterion.eq(criterion.escape('x'), 7),
+  criterion.eq(criterion.escape('y'), 'a')
+);
 ```
 
+#### or
 
-#### lower than: find where `x < 3` and `y <= 4`
+where `x = 7` or `y = 6`:
 
-```javascript
-var c = criterion({x: {$lt: 3}, y: {$lte: 4}});
-c.sql();        // => 'x < ? AND y <= ?'
-c.params();     // => [3, 4]
+``` js
+var c = criterion({$or: {x: 7, y: 6}});
+c.sql();
+// -> 'x = ? OR y = ?'
+c.params();
+// -> [7, 6]
 ```
-or
-```javascript
-var c = criterion('x < ? AND y <= ?', 3, 4);
+
+or using an array:
+
+``` js
+var c = criterion({$or: [{x: 7}, {y: 6}]});
 ```
 
-#### greater than: find where `x > 3` and `y >= 4`
+or raw:
 
-```javascript
-var c = criterion({x: {$gt: 3}, y: {$gte: 4}});
-c.sql();        // => 'x > ? AND y >= ?'
-c.params();     // => [3, 4]
+``` js
+var c = criterion('x = ? OR y = ?', 7, 6);
 ```
-or
-```javascript
-var c = criterion('x > ? AND y >= ?', 3, 4);
-```
 
-#### between: find where `x` is between `5` and `10`
+or functional:
 
-```javascript
-var c = criterion('x BETWEEN ? AND ?', 5, 10);
-c.sql();        // => 'x BETWEEN ? AND ?'
-c.params();     // => [5, 10]
+``` js
+var c = criterion.or(
+  criterion.eq(criterion.escape('x'), 7),
+  criterion.eq(criterion.escape('y'), 6)
+);
 ```
 
-#### not: find where not (`x > 3` and `y >= 4`)
+#### not
 
-```javascript
+where not (`x > 3` and `y >= 4`):
+
+``` js
 var c = criterion({$not: {x: {$gt: 3}, y: {$gte: 4}}});
-c.sql();        // => 'NOT (x > ? AND y >= ?)'
-c.params();     // => [3, 4]
+c.sql();
+// -> 'NOT (x > ? AND y >= ?)'
+c.params();
+// -> [3, 4]
 ```
-or
-```javascript
+
+or raw:
+
+``` js
 var c = criterion('NOT (x > ? AND y >= ?)', 3, 4);
 ```
 
-#### sql function: find where `x != LOG(y, 4)`
+or functional:
 
-```javascript
-var c = criterion({x: {$ne: criterion('LOG(y, ?)', 4)}});
-c.sql();        // => 'x != LOG(y, ?)'
-c.params();     // => [4]
+``` js
+var c = criterion.not(
+  criterion.and(
+    criterion.eq(criterion.escape('x'), 3),
+    criterion.eq(criterion.escape('y'), 4)
+  )
+);
 ```
 
-#### in: find where `x` is in `[1, 2, 3]`
+`$or`, `$and` and `$not` can be nested arbitrarily.
 
-```javascript
+### lists of scalar expressions
+
+[see also the postgres documentation on row and array comparisons](http://www.postgresql.org/docs/9.3/static/functions-comparisons.html)
+
+#### in list
+
+where `x` is in `[1, 2, 3]`
+
+``` js
 var c = criterion({x: [1, 2, 3]});
-c.sql();        // => 'x IN (?, ?, ?)'
-c.params();     // => [1,2,3]
+c.sql();
+// -> 'x IN (?, ?, ?)'
+c.params();
+// -> [1,2,3]
 ```
-or
-```javascript
+
+or more verbose:
+
+``` js
+var c = criterion({x: {$in: [1, 2, 3]}});
+```
+
+or raw:
+
+``` js
 var c = criterion('x IN (?)', [1, 2, 3]);
 ```
 
-#### not in: find where `x` is not in `[1, 2, 3]`
+or functional:
 
-```javascript
+``` js
+var c = criterion.in(criterion.escape('x'), [1, 2, 3]);
+```
+
+#### not in list
+
+where `x` is not in `[1, 2, 3]`
+
+``` js
 var c = criterion({x: {$nin: [1, 2, 3]}});
-c.sql();        // => 'x NOT IN (?, ?, ?)'
-c.params();     // => [1,2,3]
+c.sql();
+// -> 'x NOT IN (?, ?, ?)'
+c.params();
+// -> [1,2,3]
 ```
-or
-```javascript
+
+or raw:
+
+``` js
 var c = criterion('x NOT IN (?)', [1, 2, 3]);
 ```
 
-#### null: find where `x` is `null`
+or functional:
 
-```javascript
-var c = criterion({x: {$null: true});
-c.sql();        // => 'x IS NULL'
-c.params();     // => []
+``` js
+var c = criterion.nin(criterion.escape('x'), [1, 2, 3]);
 ```
-or
-```javascript
-var c = criterion('x IS NULL');
+
+### subqueries
+
+`var subquery` in the examples below can be any [sql-fragment](#the-sql-fragment-interface).
+
+the fact that [mohair](http://github.com/snd/mohair)/[mesa](http://github.com/snd/mesa)-queries are *sql-fragments*
+allows you to model subqueries with mohair/mesa
+and then use them directly in *condition-objects*.
+this makes the creation of SQL-where-conditions that contain subqueries quite elegant.
+
+[see also the postgres documentation on row and array comparisons](http://www.postgresql.org/docs/9.3/static/functions-comparisons.html)
+
+#### in subquery
+
+where `x` is in subquery:
+
+``` js
+var subquery = mohair
+  .table('post')
+  .where({is_published: true})
+  .select('id');
+
+var c = criterion({x: {$in: subquery}});
+
+c.sql();
+// -> 'x IN (SELECT id FROM post WHERE is_published = ?)'
+c.params();
+// -> [true]
 ```
 
-#### not null: find where `x` is not `null`
+or functional:
 
-```javascript
-var c = criterion({x: {$null: false}});
-c.sql();        // => 'x IS NOT NULL'
-c.params();     // => []
+``` js
+var c = criterion.in(criterion.escape('x'), subquery);
 ```
-or
-```javascript
-var c = criterion('x IS NOT NULL');
+
+#### not in subquery
+
+where `x` is not in subquery:
+
+``` js
+var subquery = mohair
+  .table('post')
+  .where({is_published: true})
+  .select('id');
+
+var c = criterion({x: {$nin: subquery}});
+
+c.sql();
+// -> 'x NOT IN (SELECT id FROM post WHERE is_published = ?)'
+c.params();
+// -> [true]
 ```
 
-### combining criteria
+or functional:
 
-`and()`, `or()` and `not()` return new objects.
-no method ever changes the object it is called on.
+``` js
+var c = criterion.nin(criterion.escape('x'), subquery);
+```
 
-#### and
+#### subquery returns any rows
 
-```javascript
-var fst = criterion({x: 7, y: 'a'});
-var snd = criterion('z = ?', true);
+``` js
+# TODO this isnt right
+var subquery = mohair
+  .table('post')
+  .where({is_published: false})
+  .where({user_id: mohair.raw('id')})
 
-fst.and(snd).sql();         // => '(x = ?) AND (y = ?) AND (z = ?)'
-fst.and(snd).params();      // => [7, 'a', true]
+var c = criterion({$exists: subquery})
+
+c.sql();
+// -> 'EXISTS (SELECT * FROM post WHERE is_published = ?)'
+c.params();
+// -> [true]
 ```
 
-#### or
+or functional:
 
-```javascript
-var fst = criterion({x: 7, y: 'a'});
-var snd = criterion('z = ?', true);
+``` js
+var c = criterion.exists(subquery);
+```
 
-snd.or(fst).sql();          // => '(z = ?) OR (x = ? AND y = ?)'
-snd.or(fst).params();       // => [true, 7, 'a']
+#### compare to any/all in subquery
+
+``` js
+var subquery = mohair
+  .table('post')
+  .select('id')
+  .where({is_published: false})
+
+var c = criterion({x: {$any: subquery}})
+
+c.sql();
+// -> 'x = ANY (SELECT * FROM post WHERE is_published = ?)'
+c.params();
+// -> [true]
 ```
 
-#### not
+or functional:
 
-```javascript
+``` js
+var c = criterion.any(criterion.escape('x'), subquery);
+```
+
+criterion supports
+
+#### row-wise comparison with subqueries
+
+find published posts that were created strictly-before the user with `id = 1` was created:
+
+``` js
+var mohair = require('mohair');
+
+var creationDateOfUserWithId1 = mohair
+  .table('user')
+  .where({id: 1})
+  .select('created_at');
+
+var postsCreatedBeforeUser = mohair
+  .table('post')
+  .where({is_published: true})
+  .where({created_at: {$lt: creationDateOfUserWithId1}});
+
+postsCreatedBeforeUser.sql();
+// ->
+// 'SELECT *
+//  FROM post
+//  WHERE is_published = ?
+//  AND created_at < (SELECT created_at FROM user WHERE id = ?)'
+postsCreatedBeforeUser.params();
+// -> [true, 1]
+```
+
+## advanced topics
+
+### combining criteria with `.and()`
+
+``` js
+var alpha = criterion({x: 7, y: 'a'});
+var bravo = criterion('z = ?', true);
+
+alpha.and(bravo).sql();
+// -> '(x = ?) AND (y = ?) AND (z = ?)'
+alpha.and(bravo).params();
+// -> [7, 'a', true]
+```
+
+`and()`, `or()` and `not()` return new objects.
+no method ever changes the object it is called on.
+
+### combining criteria with `.or()`
+
+``` js
+var alpha = criterion({x: 7, y: 'a'});
+var bravo = criterion('z = ?', true);
+
+bravo.or(alpha).sql();
+// -> '(z = ?) OR (x = ? AND y = ?)'
+bravo.or(alpha).params();
+// -> [true, 7, 'a']
+```
+
+`and()`, `or()` and `not()` return new objects.
+no method ever changes the object it is called on.
+
+### negating criteria with `.not()`
+
+``` js
 var c = criterion({x: 7, y: 'a'});
-c.not().sql();              // => 'NOT ((x = ?) AND (y = ?))'
-c.not().params();           // => [7, 'a']
+c.not().sql();
+// -> 'NOT ((x = ?) AND (y = ?))'
+c.not().params();
+// -> [7, 'a']
 ```
@@ -322,8 +949,89 @@
 
-```javascript
+``` js
 var c = criterion({x: 7, y: 'a'});
-c.not().not().sql();        // => '(x = ?) AND (y = ?)'
-c.not().not().params();     // => [7, 'a']
+c.not().not().sql();
+// -> '(x = ?) AND (y = ?)'
+c.not().not().params();
+// -> [7, 'a']
 ```
 
-### [license: MIT](LICENSE)
+### escaping column names
+
+you can pass a function into any `sql()` method to escape column names:
+
+``` js
+var c = criterion({x: 7, y: 8});
+
+var escape = function(x) {
+  return '"' + x + '"';
+};
+c.sql(escape);
+// -> '"x" = ? AND "y" = ?' <- x and y are escaped !
+c.params();
+// -> [7, 8]
+```
+
+### param array explosion
+
+if a parameter binding for raw sql is an array then
+the corresponding binding `?` is exploded into a list of `?`:
+
+``` js
+var c = criterion('x = ? AND y IN (?)', 7, [8, 9, 10]);
+
+c.sql();
+// -> 'x = ? AND y IN (?, ?, ?)'
+c.params();
+// -> [7, 8, 9, 10]
+```
+
+``` js
+var c = criterion('x = ? AND (y && ARRAY[?])', 7, [8, 9, 10]);
+
+c.sql();
+// -> 'x = ? AND (y && ARRAY[?, ?, ?])'
+c.params();
+// -> [7, 8, 9, 10]
+```
+
+## changelog
+
+### 0.4.0
+
+- to escape column names in the resulting SQL an escape function can now be passed as an argument into any `sql()` method
+- sql fragments are now always wrapped in parentheses before pasting them into a query.
+  - doesn't break anything and makes subqueries work without further changes.
+- added `$exists` which can be used with mesa/mohair queries (or any object that responds to an `sql()` method): `criterion({$exists: mohair.table('post').where({id: 7})})`
+- `$in` and `$nin` now support not just lists of values but also subqueries:
+  - `criterion({id: {$in: mohair.table('post').where({is_active: true}).select('id')}})`
+- added modifiers `$any`, `$neAny`, `$ltAny`, `$gtAny`, `$gteAny`, `$all`, `$neAll`, `$ltAll`, `$lteAll`, `$gtAll`, `$gteAll` to be used with subqueries:
+  - `criterion({created_at: {$gteAll: mohair.table('post').where({is_active: true}).select('updated_at')}})`
+- sql-fragments can now be used in more places...
+  - where the value would normally go in a comparison: `{$lt: criterion('5 + 8')}`
+    - this makes row-wise comparisons with subqueries possible
+  - in the arrays passed to `$or` and `$and`: `{$or [{a: 7}, criterion('b < ?', 5)]}`
+  - ...
+- bugfixes
+  - made some (exotic) condition-objects work which didn't work before
+- improved implementation and based everything on a DSL which is also exposed
+- major improvements to
+  - code quality
+  - tests
+  - terminology
+  - documentation
+
+## [license: MIT](LICENSE)
+
+## TODO
+
+- test dsl
+- document dsl
+- read through the code again
+- often the left side of an operation is just a column or a table qualified column
+- atoms are treated as values
+- dontWrap
+  - says how outer fragments should handle this fragment
+  - things are only wrapped when inside of something
+- finish the readme
+- test left operands
+  - reverse operands

New alerts

Major refactor

Supply chain risk

Package has recently undergone a major refactor. It may be unstable or indicate significant internal changes. Use caution when updating to versions that include significant changes.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

81089

increased by147.34%

Lines of code

592

increased by78.31%

Number of lines in readme file

1036

increased by215.85%

Worsened metrics

Number of package files

8

decreased by-33.33%

Number of medium supply chain risk alerts

1

increased byInfinity%

No dependency changes