Kysely•3mo ago

How to order by desc using expression builder with case

I am ordering by using case but cannot find a way how to add desc to the eb.

const person = await db
  .selectFrom("person")
  .select(["id", "first_name"])
  .where("id", "=", "1")
  .orderBy((eb) =>
    eb
      .case()
      .when(
        eb.fn("length", ["first_name"]),
        "<=",
        eb.fn("length", ["last_name"]),
      )
      .then(eb.fn("length", ["first_name"]))
      .else(eb.fn("length", ["last_name"]))
      .end(),
  )
  .executeTakeFirst()

const person = await db
  .selectFrom("person")
  .select(["id", "first_name"])
  .where("id", "=", "1")
  .orderBy((eb) =>
    eb
      .case()
      .when(
        eb.fn("length", ["first_name"]),
        "<=",
        eb.fn("length", ["last_name"]),
      )
      .then(eb.fn("length", ["first_name"]))
      .else(eb.fn("length", ["last_name"]))
      .end(),
  )
  .executeTakeFirst()

https://old.kyse.link/?p=s&i=E4Hpv78UML4e6YqZoY6u

Solution:

Hey 👋 Try this: ```ts...

Jump to solution

2 Replies

Solution

Igal•3mo ago

Hey 👋 Try this:

await db
  .selectFrom("person")
  .select(["id", "first_name"])
  .where("id", "=", "1")
  .orderBy((eb) =>
    eb
      .case()
      .when(
        eb.fn("length", ["first_name"]),
        "<=",
        eb.fn("length", ["last_name"]),
      )
      .then(sql`${eb.fn("length", ["first_name"])} desc`)
      .else(sql`${eb.fn("length", ["last_name"])} desc`)
      .end(),
  )
  .executeTakeFirst()

await db
  .selectFrom("person")
  .select(["id", "first_name"])
  .where("id", "=", "1")
  .orderBy((eb) =>
    eb
      .case()
      .when(
        eb.fn("length", ["first_name"]),
        "<=",
        eb.fn("length", ["last_name"]),
      )
      .then(sql`${eb.fn("length", ["first_name"])} desc`)
      .else(sql`${eb.fn("length", ["last_name"])} desc`)
      .end(),
  )
  .executeTakeFirst()

https://old.kyse.link/?p=s&i=LyUzuAePf9Go3EK6XLZX Or this:

await db
  .selectFrom("person")
  .select(["id", "first_name"])
  .where("id", "=", "1")
  .orderBy(
    (eb) =>
      eb
        .case()
        .when(
          eb.fn("length", ["first_name"]),
          "<=",
          eb.fn("length", ["last_name"]),
        )
        .then(eb.fn("length", ["first_name"]))
        .else(eb.fn("length", ["last_name"]))
        .end(),
    "desc",
  )
  .executeTakeFirst()

await db
  .selectFrom("person")
  .select(["id", "first_name"])
  .where("id", "=", "1")
  .orderBy(
    (eb) =>
      eb
        .case()
        .when(
          eb.fn("length", ["first_name"]),
          "<=",
          eb.fn("length", ["last_name"]),
        )
        .then(eb.fn("length", ["first_name"]))
        .else(eb.fn("length", ["last_name"]))
        .end(),
    "desc",
  )
  .executeTakeFirst()

https://old.kyse.link/?p=s&i=vJ1oipA1ffM71Hz4K2zh

mike•3mo ago

I was looking for the second one 🥳 the easiest thing is mostly the hardest to do... 😱

Gaming

Programming

How to order by desc using expression builder with case