case when column = another column

I have a semi-complex query, and I'm trying to use a case() that includes a when() where the two values are columns (aka the two columns have the same value).

It doesn't seem to be supported, however in theory this should work. Any idea how I can make it work with Kysely?

The

.when("shahz_response", "=", "rank")

.when("shahz_response", "=", "rank")

is where I'm attempting this, but this isn't working

  const result = await db
    .selectFrom("zamdle_clips")
    .select((eb) => [
      eb.fn
        .sum((e) =>
          e
            .case()
            .when("shahz_response", "=", "rank")
            .then(e.case().when("id", ">=", startZamdle).then(1).else(0).end())
            .else(0)
            .end()
        )
        .as("correctSeason"),
      eb.fn
        .sum((e) =>
          e.case().when("shahz_response", "=", "rank").then(1).else(0).end()
        )
        .as("correctAllTime"),
      eb.fn
        .sum((e) =>
          e
            .case()
            .when("shahz_response", "is not", null)
            .then(e.case().when("id", ">=", startZamdle).then(1).else(0).end())
            .else(0)
            .end()
        )
        .as("totalSeason"),
      eb.fn
        .sum((e) =>
          e.case().when("shahz_response", "is not", null).then(1).else(0).end()
        )
        .as("totalAllTime"),
    ])
    .executeTakeFirstOrThrow();

  const result = await db
    .selectFrom("zamdle_clips")
    .select((eb) => [
      eb.fn
        .sum((e) =>
          e
            .case()
            .when("shahz_response", "=", "rank")
            .then(e.case().when("id", ">=", startZamdle).then(1).else(0).end())
            .else(0)
            .end()
        )
        .as("correctSeason"),
      eb.fn
        .sum((e) =>
          e.case().when("shahz_response", "=", "rank").then(1).else(0).end()
        )
        .as("correctAllTime"),
      eb.fn
        .sum((e) =>
          e
            .case()
            .when("shahz_response", "is not", null)
            .then(e.case().when("id", ">=", startZamdle).then(1).else(0).end())
            .else(0)
            .end()
        )
        .as("totalSeason"),
      eb.fn
        .sum((e) =>
          e.case().when("shahz_response", "is not", null).then(1).else(0).end()
        )
        .as("totalAllTime"),
    ])
    .executeTakeFirstOrThrow();

Solution

Welp, looks like I figured it out!

.when("shahz_response", "=", e.ref("rank"))

.when("shahz_response", "=", e.ref("rank"))

Jump to solution

Kysely•3y ago•

3 replies

sirhype

case when column = another column

.when("shahz_response", "=", "rank")

.when("shahz_response", "=", "rank")

is where I'm attempting this, but this isn't working

  const result = await db
    .selectFrom("zamdle_clips")
    .select((eb) => [
      eb.fn
        .sum((e) =>
          e
            .case()
            .when("shahz_response", "=", "rank")
            .then(e.case().when("id", ">=", startZamdle).then(1).else(0).end())
            .else(0)
            .end()
        )
        .as("correctSeason"),
      eb.fn
        .sum((e) =>
          e.case().when("shahz_response", "=", "rank").then(1).else(0).end()
        )
        .as("correctAllTime"),
      eb.fn
        .sum((e) =>
          e
            .case()
            .when("shahz_response", "is not", null)
            .then(e.case().when("id", ">=", startZamdle).then(1).else(0).end())
            .else(0)
            .end()
        )
        .as("totalSeason"),
      eb.fn
        .sum((e) =>
          e.case().when("shahz_response", "is not", null).then(1).else(0).end()
        )
        .as("totalAllTime"),
    ])
    .executeTakeFirstOrThrow();

  const result = await db
    .selectFrom("zamdle_clips")
    .select((eb) => [
      eb.fn
        .sum((e) =>
          e
            .case()
            .when("shahz_response", "=", "rank")
            .then(e.case().when("id", ">=", startZamdle).then(1).else(0).end())
            .else(0)
            .end()
        )
        .as("correctSeason"),
      eb.fn
        .sum((e) =>
          e.case().when("shahz_response", "=", "rank").then(1).else(0).end()
        )
        .as("correctAllTime"),
      eb.fn
        .sum((e) =>
          e
            .case()
            .when("shahz_response", "is not", null)
            .then(e.case().when("id", ">=", startZamdle).then(1).else(0).end())
            .else(0)
            .end()
        )
        .as("totalSeason"),
      eb.fn
        .sum((e) =>
          e.case().when("shahz_response", "is not", null).then(1).else(0).end()
        )
        .as("totalAllTime"),
    ])
    .executeTakeFirstOrThrow();

Solution

Welp, looks like I figured it out!

.when("shahz_response", "=", e.ref("rank"))

.when("shahz_response", "=", e.ref("rank"))

Jump to solution

case when column = another column

case when column = another column

Similar Threads

Similar Threads

Similar Threads