Kysely•4mo ago

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") 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"))...

Jump to solution

1 Reply

Solution

sirhype•4mo ago

Welp, looks like I figured it out! .when("shahz_response", "=", e.ref("rank"))

Gaming

Programming

case when column = another column