This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2022-10-13
Channels
- # announcements (1)
- # babashka (30)
- # beginners (43)
- # biff (24)
- # calva (72)
- # cider (12)
- # clj-commons (24)
- # clj-on-windows (10)
- # cljsrn (23)
- # clojure (123)
- # clojure-bay-area (6)
- # clojure-europe (43)
- # clojure-losangeles (2)
- # clojure-nl (2)
- # clojure-uk (9)
- # clojurescript (125)
- # core-async (9)
- # cursive (2)
- # data-science (3)
- # datomic (30)
- # devops (1)
- # emacs (13)
- # events (5)
- # fulcro (15)
- # graalvm (3)
- # gratitude (1)
- # humbleui (11)
- # lsp (5)
- # nbb (24)
- # off-topic (11)
- # pedestal (5)
- # releases (1)
- # remote-jobs (1)
- # sci (15)
- # scittle (16)
- # shadow-cljs (15)
- # sql (11)
- # tools-deps (9)
- # xtdb (5)
Hey team! noob question. What do you suggest as the idiomatic way to handle a nullable filter? For example, consider this query:
(db-execute-one!
conn
["DELETE FROM foos WHERE app_id = ?::uuid AND user_id = ?::uuid"
app-id user-id])
Here, user-id
is optional:
If it’s provided, I want to delete foos where user_id is this value
But if it is nil, I want to delete foos where user_id IS NULL
I am guessing the idiomatic way is to do an if
and provide a different query. Something like this:
(db-execute-one!
tx
(if user-id
["DELETE FROM foos WHERE app_id = ?::uuid AND user_id = ?::uuid"
app-id user-id]
["DELETE FROM foos WHERE app_id = ?::uuid AND user_id IS NULL"
app-id]))
But if there’s another way I’m all ears!if you really want to have one query there is a trick with coalesce
- convert both column value and and query argument nulls to some value that does not exist in db DELETE FROM foos WHERE app_id = ?::uuid AND coalesce(user_id,'0000-0000...'::uuid) = coalesce(?, '0000-0000...')::uuid
but this approach does not work well with indexes I guess.
Love the hackery! This would not return the rows that do have null as the value for user-id
though right?
My goal is, for the case where user-id is null, I want the rows where user-id is null
If both sides of equality have the same transformation of nulls to a dummy value we essentially make nulls comparable. If input user-id is NULL the query will pick records with user_id NULL because both sides of equality will have same dummy values.
More index friendly way would be to have both condition with OR
DELETE FROM foos
WHERE app_id = ?::uuid
AND (user_id IS NULL AND ?::uuid IS NULL
OR user_id = ?::uuid)
note that this requires to pass user-id
argument twiceCool, thanks @U076FM90B 🙂
If you're using HoneySQL to build queries, it figures this out for you (although the cast would probably not work in this specific case). {:where [:= :user_id user-id]}
will either produce WHERE user_id = ?
(and the value of user-id
as a parameter) or WHERE user_id IS NULL
Gotcha! One more noob question @U04V70XH6 Is the casting I am doing idiomatic? I was hacking and adding the casts is what let me provide strings as uuids with all working honky dory. Would you do it a different way?
I would convert the strings to UUID objects in Clojure and avoid the strings, personally. But then I use MySQL/Percona, not PostgreSQL 🙂
Makes sense, thanks @U04V70XH6!