Skip to content
New issue

Have a question about this project? # for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “#”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? # to your account

Nested comparison groups #3

Open
TehShrike opened this issue Aug 3, 2016 · 8 comments
Open

Nested comparison groups #3

TehShrike opened this issue Aug 3, 2016 · 8 comments

Comments

@TehShrike
Copy link
Owner

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
Copy link
Owner Author

group is dangerously close to groupBy

@daytonlowell
Copy link
Collaborator

daytonlowell commented Aug 11, 2021

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
Copy link
Owner Author

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

@TehShrike
Copy link
Owner Author

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

@wisejohn
Copy link

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
Copy link
Collaborator

daytonlowell commented Aug 12, 2021

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
Copy link
Owner Author

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
Copy link
Owner Author

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.

# for free to join this conversation on GitHub. Already have an account? # to comment
Projects
None yet
Development

No branches or pull requests

3 participants