Nested comparison groups

[TehShrike]

q.where('some_column', 14)
    .where(q.group('OR')
        .compare('column_a', someDate)
        .compare('column_b', '>' someDate)
        .like('column_c', someValue)
        .compare('column_d', null)
    )
// => WHERE some_column = ? 
//  AND (column_a = ? OR column_b > ? OR column_c LIKE someValue OR column_d IS NULL

group should be exposed on the original imported object, but not the query object returned by every individual call.

Are like and compare good function names? Is group a good function name?

[TehShrike]

group is dangerously close to groupBy

[daytonlowell]

What if the .where() method could optionally accept another query-builder object that had only "where" methods called on it(where, orWhere, whereLike, and orWhereLike) and then it would produce the nest parenthetical.

So for example: If you wanted a WHERE clause like

WHERE important = ? AND (your_column = ? OR your_column = ? OR something_else LIKE ?)

you could do

const query = q.select('everything')
	.from('table')
	.where('important', true)
	.where(
		q.where('your_column', true)
		.orWhere('your_column', randomVariable)
		.orWhereLike('something_else', anotherVariable)
	)

[TehShrike]

ooooooh that's an interesting idea. My kneejerk reaction is that I like it

[TehShrike]

we could make where throw an error if you ever pass in a query object that has any clause other than where clauses

[wisejohn]

Need to make it work for orWhere as well

for situations like

WHERE notimportant = ? OR (your_column = ? AND your_column = ? AND something_else LIKE ?)

[daytonlowell]

Sure, it'd be

const query = q.select('everything')
	.from('table')
	.where('notimportant', true)
	.orWhere(
		q.where('your_column', true)
		.where('your_column', randomVariable)
		.whereLike('something_else', anotherVariable)
	)

[TehShrike]

So, it wouldn't be difficult to make this work equivalently for the HAVING clause, but it would be kind of disappointing if there wasn't a solution that would work in ON clauses too.

Maybe we just live with that limitation for now?

[TehShrike]

If we take this path forward then I think the way to do it would be to pass in another property to the options object (second argument to columnParam) to take a string that represents the clauseKey that the parenthetical should be taken from (i.e. having or where).

I would be fine with merging this solution now. Even if we find another better way that works for everything including ON clauses in JOINs down the road, it wouldn't hurt to leave this implementation laying around working this way.

Right now this code

q.select('everything')
	.from('table')
	.where('notimportant', true)
	.orWhere(
		q.where('your_column', true)
		.where('your_column', 'randomVariable')
		.whereLike('something_else', 'anotherVariable')
	)

produces this query

SELECT everything
FROM table
WHERE notimportant = true OR WHERE your_column = true AND your_column = 'randomVariable' AND something_else LIKE 'anotherVariable'

and changing it to return something more reasonable doesn't hurt us I think.