K
Kyselydecho

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
koskimas
koskimas•324d 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"))
decho
decho•324d 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
koskimas•324d ago
Thank you for sharing ❤️
decho
decho•324d ago
no problem at all
Want results from more Discord servers?
Add your server
More Posts
Kysely originI have to ask. Where does the “kysely” name come from…?🤷‍♂️ It means “sour” in my mother tongue �coalesce in where statementI've found examples and documentation for coalesce in select however I still struggle how to use it Which approach is better for coalesce and similar functions?The fn and raw looks to me familiar as in sequelize there is fn and literal. What is the right approSingle source of truth (defaultAlias-schema-tableName)Hi, is there any way how to set default alias to schema.table mapping to have single source of truthWhat's the pattern for writing migrations exactly?I'm not familiar with SQL migrations, so my question is how do we create the migration files, what rwriting a transaction that calls functionsHey everyone, I like what I've got going here but before I did this for my whole code base I just wahow to search inside a json type column, I'm using postgresqlI looked in the documentation, and it talks about using the jsonObjectFrom function that is in the kHow to inject parentheses in the generated arithmetic expression?https://kyse.link/?p=s&i=SD8v0Fo6NqqCrPEXMbx1 That's obviously not what I meant.withSchema() and raw SQLwhen doing `sql <ReturnType> SELECT some_function(param);.execute(db)` the function is not found beJSON in columns (postgresql)Hey <a:YA_Wave:743393941369651201> I am completely new to using SQL databases and have a (hopefull