tgriesser/knex — pull request #349 by calvinmetcalf (2014-07-01 15:04:32+00:00)
Title: chained where and join

adds some magic to make where, on, and join clauses easier.

- you can add or. in front of a when or on to do the same thing that whenOr and onOr did, e.g.

qb.on('users.id', '=', 'contacts.id').or.on('users.name', '=', 'contacts.name');
sql().select('*').from('users').where('id', '=', 1).or.where('email', '=', 'foo')

instead of

qb.on('users.id', '=', 'contacts.id').orOn('users.name', '=', 'contacts.name');
sql().select('*').from('users').where('id', '=', 1).orWhere('email', '=', 'foo')

- you can chain exists, in, between, raw, null, and wrapped after where, e.g.

sql().select('*').from('users').where.in('id', [1, 2, 3]).toSQL();
chain = sql().select('*').from('users').where.between('id', [1, 2]).toSQL();

instead of

sql().select('*').from('users').whereIn('id', [1, 2, 3]).toSQL();
chain = sql().select('*').from('users').whereBetween('id', [1, 2]).toSQL();

- you can negate one of those by putting a not after the where

sql().select('*').from('users').where.not.in('id', [1, 2, 3]).toSQL();

instead of

sql().select('*').from('users').whereNotIn('id', [1, 2, 3]).toSQL();

- you can also just put the not infront of where

sql().select('*').from('users').not.whereIn('id', [1, 2, 3]).toSQL();

- similarly to or you can also chain with 'and' but this doesn't do anything special
sql().select('*').from('users').and.where('id', '=', 1)

vs

sql().select('*').from('users').where('id', '=', 1)

- lastly you can do
- query.inner.join()
- query.left.join()
- query.right.join()
- query.cross.join()
- query.outer.join()
- query.left.outer.join()
- query.right.outer.join()
- query.full.outer.join()

the only weirdness I've found is that this line

chain = sql()
.select('*')
.from('orders')
.where
.exists(
sql()
.select('*')
.from('products')
.where
.raw('products.id = orders.id')
).toSQL();

causes a stack overflow but doesn't if I switch from where.exists to whereExists