Fork me on GitHub
#sql
<
2022-10-13
>
stopa15:10:37

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!

nbardiuk15:10:35

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.

stopa15:10:30

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

nbardiuk15:10:53

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.

nbardiuk16:10:21

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 twice

stopa16:10:01

Ahh I see!

stopa16:10:10

Cool, thanks @U076FM90B 🙂

seancorfield16:10:15

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

🤯 2
stopa16:10:19

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?

seancorfield16:10:06

I would convert the strings to UUID objects in Clojure and avoid the strings, personally. But then I use MySQL/Percona, not PostgreSQL 🙂

❤️ 1
stopa16:10:46

Makes sense, thanks @U04V70XH6!