K
Kyselyā€¢17mo ago
Ross

Query with ANDs and ORs - struggling with syntax

I want to be able to create a query that looks like this - i.e. has a first WHERE section that must be true then an either/or but struggling to get the syntax right SELECT * FROM user WHERE (date = '2022-01-01' AND area = 'south') AND ( ( amount = 20 AND status = 'pending' ) OR ( assignee = 'bob' AND amount = 10 ) ) I've put together an example on the playground - any pointers, greatly appreciated! https://wirekang.github.io/kysely-playground/?p=f&i=-NQeDYTzwAp8pwmb5cWT
49 Replies
Igal
Igalā€¢17mo ago
Hey šŸ‘‹ Appreciate the playground link!
db
.selectFrom('user')
.where('date', '=', '2022-01-01')
.where('area', '=', 'south')
.where((wb) =>
wb
.where((wb) =>
wb.where('amount', '=', 20).where('status', '=', 'pending'),
)
.orWhere((wb) =>
wb.where('assignee', '=', 'bob').where('amount', '=', 10),
),
)
.selectAll()
db
.selectFrom('user')
.where('date', '=', '2022-01-01')
.where('area', '=', 'south')
.where((wb) =>
wb
.where((wb) =>
wb.where('amount', '=', 20).where('status', '=', 'pending'),
)
.orWhere((wb) =>
wb.where('assignee', '=', 'bob').where('amount', '=', 10),
),
)
.selectAll()
https://wirekang.github.io/kysely-playground/?p=f&i=-NQefNT9sPQKfrEtbKRq
Ross
Rossā€¢17mo ago
Great, thanks again. Managed to apply that to my more complex real-world example šŸ˜„
Igal
Igalā€¢17mo ago
We're releasing a brand new way of doing things as far as .where and .having go. We'd love your feedback once it drops.
koskimas
koskimasā€¢17mo ago
With the new expression builder, the above query would look like this
db
.selectFrom('user')
.where('date', '=', '2022-01-01')
.where('area', '=', 'south')
.where((({ or, and, cmp }) => or([
and([
cmp('amount', '=', 20),
cmp('status', '=', 'pending')
]),
and([
cmp('assignee', '=', 'bob'),
cmp('amount', '=', 10)
])
]))
.selectAll()
db
.selectFrom('user')
.where('date', '=', '2022-01-01')
.where('area', '=', 'south')
.where((({ or, and, cmp }) => or([
and([
cmp('amount', '=', 20),
cmp('status', '=', 'pending')
]),
and([
cmp('assignee', '=', 'bob'),
cmp('amount', '=', 10)
])
]))
.selectAll()
Igal
Igalā€¢17mo ago
or full-blown explicit mode:
db
.selectFrom('user')
.where(({ or, and, cmp }) => and([
cmp('date', '=', '2022-01-01'),
cmp('area', '=', 'south'),
or([
and([
cmp('amount', '=', 20),
cmp('status', '=', 'pending')
]),
and([
cmp('assignee', '=', 'bob'),
cmp('amount', '=', 10)
])
])
]))
.selectAll()
db
.selectFrom('user')
.where(({ or, and, cmp }) => and([
cmp('date', '=', '2022-01-01'),
cmp('area', '=', 'south'),
or([
and([
cmp('amount', '=', 20),
cmp('status', '=', 'pending')
]),
and([
cmp('assignee', '=', 'bob'),
cmp('amount', '=', 10)
])
])
]))
.selectAll()
Ross
Rossā€¢17mo ago
This is much clearer to my eye, just one question and excuse my likely ignorance, what does cmp stand for/mean? I mean, I think it's obvious what it's doing but I'm struggling for what its name means
koskimas
koskimasā€¢17mo ago
"compare" I don't love the name either. Any suggestions? It can't be where since the expression builder can be used in any context compare is a bit too long.
Ross
Rossā€¢17mo ago
apply?
koskimas
koskimasā€¢17mo ago
That sounds like a mutating method that applies something somewhere
Ross
Rossā€¢17mo ago
i guess that might be dangerous/confusing given the built in
koskimas
koskimasā€¢17mo ago
Would've cmpr been better?
Ross
Rossā€¢17mo ago
Match?
koskimas
koskimasā€¢17mo ago
Not bad
Unknown User
Unknown Userā€¢17mo ago
Message Not Public
Sign In & Join Server To View
koskimas
koskimasā€¢17mo ago
It's so long. All you see in the code is compare compare compare compare
db
.selectFrom('user')
.where(({ or, and, compare }) => and([
compare('date', '=', '2022-01-01'),
compare('area', '=', 'south'),
or([
and([
compare('amount', '=', 20),
compare('status', '=', 'pending')
]),
and([
compare('assignee', '=', 'bob'),
compare('amount', '=', 10)
])
])
]))
.selectAll()
db
.selectFrom('user')
.where(({ or, and, compare }) => and([
compare('date', '=', '2022-01-01'),
compare('area', '=', 'south'),
or([
and([
compare('amount', '=', 20),
compare('status', '=', 'pending')
]),
and([
compare('assignee', '=', 'bob'),
compare('amount', '=', 10)
])
])
]))
.selectAll()
Actually not that bad in this example If we use a long name for comare we should use a long name for everything else in the expression builder too. I'm not sure that's a good idea. It would be weird to have bin, fn, val, lit and compare If we use long names for all of those, it's goodbye to oneliners The idea with short names was to keep them out of the way. The user would see the "SQL" around the typescript and know what the methods mean. No matter what names we use, it won't help the discoverability. People won't know to look for compare any more than cmp. People learn by looking at examples.
Unknown User
Unknown Userā€¢17mo ago
Message Not Public
Sign In & Join Server To View
Igal
Igalā€¢17mo ago
but with intellisense, would not matter much
..and co-pilot. šŸ¤–
koskimas
koskimasā€¢17mo ago
If kysely is first ORM for a beginner,
Kysely is neither an ORM or for beginners IMO. It's a dumb query builder and you really need to know your SQL to do anything useful.
Igal
Igalā€¢17mo ago
Yeah, these methods are non-sql, consumers will have to check documentation/examples regardless
koskimas
koskimasā€¢17mo ago
dumb in the sense that there is no extra logic on top of converting function calls to SQL. Not dumb as in "this is dumb" šŸ˜„
Igal
Igalā€¢17mo ago
We'd be in trouble if our sql methods were ambiguous
koskimas
koskimasā€¢17mo ago
but with intellisense, would not matter much
The point is not that it's long to write. It's long to read
Igal
Igalā€¢17mo ago
We also need to make sure we don't step on reserved words, from the 3 built-in dialects, and from SQL spec in general. These methods need to scream "oh this is kysely stuff"
koskimas
koskimasā€¢17mo ago
I wish there was some widely known abbreviation for this like there are eq, lt, lte, gt etc. Those are also nonsense, but everyone knows what they mean
Igal
Igalā€¢17mo ago
I think cmpr is a good improvement bin is also open for debate
koskimas
koskimasā€¢17mo ago
Yep it's bad since it means binary
Igal
Igalā€¢17mo ago
bop - binary operation? šŸ¤·šŸ»ā€ā™‚ļø
koskimas
koskimasā€¢17mo ago
bex could be better as you suggested somewhere Hey Bob Longer version ropert
Igal
Igalā€¢17mo ago
bino haha biex, bexp bxp
koskimas
koskimasā€¢17mo ago
All equally nonsense, but all better than bin which does have a widely known (wrong) meaning
Igal
Igalā€¢17mo ago
Maybe we should get a tik toker to chime in their generation makes up all sorts of abbr
koskimas
koskimasā€¢17mo ago
Maybe we should get a tik toker to chime in
sus
Igal
Igalā€¢17mo ago
twop - two argument operation "hey don't forget to add that twop!"
Unknown User
Unknown Userā€¢17mo ago
Message Not Public
Sign In & Join Server To View
koskimas
koskimasā€¢17mo ago
I think I like bex the most. Maybe just because I've had more time to digest that one Do you have a favorite?
Igal
Igalā€¢17mo ago
bex & bxp are my favorites right now bxp is slightly more comfortable to type on a QWERTY at least
koskimas
koskimasā€¢17mo ago
So bxp and cmpr? šŸ‘©ā€āš–ļø ?
koskimas
koskimasā€¢17mo ago
Igal
Igalā€¢17mo ago
Yeah let's go for it Any objections? @here
Unknown User
Unknown Userā€¢17mo ago
Message Not Public
Sign In & Join Server To View
koskimas
koskimasā€¢17mo ago
Arbitrary binary expression. Like age + 1.
Unknown User
Unknown Userā€¢17mo ago
Message Not Public
Sign In & Join Server To View
Ross
Rossā€¢17mo ago
I think cmpr is an improvement. I think if there's a good description in the intellisense hover-over, users will quickly internalise the meanings.
koskimas
koskimasā€¢17mo ago
@Igal Do you want to do the renaming in your PR?
Igal
Igalā€¢17mo ago
OK done
Arbitrary binary expression. Like age + 1.
isn't bxp basically arit ? the way I understood it, it was a non-specific binary expression (that can also be a comparison expression and arithmetic expression) ... now its done
koskimas
koskimasā€¢17mo ago
isn't bxp basically arit
I changed it. You can now use comparison operators there too.
Igal
Igalā€¢17mo ago
So maybe we should introduce arit too?
koskimas
koskimasā€¢17mo ago
I'd be more inclined to getting rid of cmpr than adding more versions that do the same thing
Igal
Igalā€¢13mo ago
OK We've released v0.26 yesterday, simplifying all of this.