Kysely•2y ago

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•2y 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

RossOP•2y ago

Great, thanks again. Managed to apply that to my more complex real-world example 😄

Igal•2y 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•2y 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•2y 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()

RossOP•2y 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•2y 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.

RossOP•2y ago

apply?

koskimas•2y ago

That sounds like a mutating method that applies something somewhere

RossOP•2y ago

i guess that might be dangerous/confusing given the built in

koskimas•2y ago

Would've cmpr been better?

RossOP•2y ago

Match?

koskimas•2y ago

Not bad

Unknown User•2y ago

Message Not Public

koskimas•2y 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•2y ago

Message Not Public

Igal•2y ago

but with intellisense, would not matter much

..and co-pilot. 🤖

koskimas•2y 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•2y ago

Yeah, these methods are non-sql, consumers will have to check documentation/examples regardless

koskimas•2y 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•2y ago

We'd be in trouble if our sql methods were ambiguous

koskimas•2y ago

but with intellisense, would not matter much

The point is not that it's long to write. It's long to read

Igal•2y 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•2y 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•2y ago

I think cmpr is a good improvement bin is also open for debate

koskimas•2y ago

Yep it's bad since it means binary

Igal•2y ago

bop - binary operation? 🤷🏻‍♂️

koskimas•2y ago

bex could be better as you suggested somewhere Hey Bob Longer version ropert

Igal•2y ago

bino haha biex, bexp bxp

koskimas•2y ago

All equally nonsense, but all better than bin which does have a widely known (wrong) meaning

Igal•2y ago

Maybe we should get a tik toker to chime in their generation makes up all sorts of abbr

koskimas•2y ago

Maybe we should get a tik toker to chime in

sus

Igal•2y ago

twop - two argument operation "hey don't forget to add that twop!"

Unknown User•2y ago

Message Not Public

koskimas•2y 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•2y ago

bex & bxp are my favorites right now bxp is slightly more comfortable to type on a QWERTY at least

koskimas•2y ago

So bxp and cmpr? 👩‍⚖️ ?

koskimas•2y ago

Igal•2y ago

Yeah let's go for it Any objections? @here

Unknown User•2y ago

Message Not Public

koskimas•2y ago

Arbitrary binary expression. Like age + 1.

Unknown User•2y ago

Message Not Public

RossOP•2y 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•2y ago

@Igal Do you want to do the renaming in your PR?

Igal•2y 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•2y ago

isn't bxp basically arit

I changed it. You can now use comparison operators there too.

Igal•2y ago

So maybe we should introduce arit too?

koskimas•2y ago

I'd be more inclined to getting rid of cmpr than adding more versions that do the same thing

Igal•2y ago

OK We've released v0.26 yesterday, simplifying all of this.

Gaming

Programming

Query with ANDs and ORs - struggling with syntax