Kysely•2y ago

CTE with exists() and case()

Hey guys, just wrote this one and I thought I'd share.

WITH
  "ongoing" AS (
    SELECT
      EXISTS (
        SELECT
        FROM
          "matches"
        WHERE
          "kickOff" BETWEEN NOW() - INTERVAL '2 hours' AND NOW()
      ) AS "is_ongoing"
    FROM
      "matches"
    LIMIT
      $1
  )
SELECT
  "matches".*
FROM
  "matches",
  "ongoing"
WHERE
  "kickOff" > NOW()
  AND CASE
    WHEN "ongoing"."is_ongoing" = TRUE THEN FALSE
    ELSE TRUE
  END
ORDER BY
  "kickOff" ASC
LIMIT
  $2

WITH
  "ongoing" AS (
    SELECT
      EXISTS (
        SELECT
        FROM
          "matches"
        WHERE
          "kickOff" BETWEEN NOW() - INTERVAL '2 hours' AND NOW()
      ) AS "is_ongoing"
    FROM
      "matches"
    LIMIT
      $1
  )
SELECT
  "matches".*
FROM
  "matches",
  "ongoing"
WHERE
  "kickOff" > NOW()
  AND CASE
    WHEN "ongoing"."is_ongoing" = TRUE THEN FALSE
    ELSE TRUE
  END
ORDER BY
  "kickOff" ASC
LIMIT
  $2

https://kyse.link/?p=s&i=weqpZStlVZ7TONs1D3UR 1. Create a CTE that checks if there is an ongoing match and returns a boolean value. 2. Find the closest match in a future date. 3. Only return it if there is no ongoing match. EDIT: Updated version: https://kyse.link/?p=s&i=r8bpSEIqQVS92bvZxqsn

4 Replies

koskimas•2y ago

Very nice! Perfect combination of type-safe and readable IMO. There are ways to make some parts more type-safe but then the code becomes much less readable. Only thing I'd change is the case statement into

where(eb => eb.not("ongoing"."is_ongoing"))

where(eb => eb.not("ongoing"."is_ongoing"))

dechoOP•2y ago

Ah, thank you for the tip, it did not occur to this can be written in such way, but effectively it does the same thing. This is more of an SQL tip than Kysely tip, nevertheless I am glad it happened because I learned a lot of new stuff 🙂 Cheers!

koskimas•2y ago

Thank you for sharing ❤️

dechoOP•2y ago

no problem at all

Gaming

Programming

CTE with exists() and case()