2024-12-02 honeysql | Clojure Slack Archive

honeysql

tengstrand 2024-12-02T08:16:56.377109Z

I want to translate this (example) SQL to honeysql:

UPDATE mytable
SET ids = 
  CASE 
    WHEN id = '00000001-0000-0000-0000-000000000000' THEN ARRAY['11111111-1111-1111-1111-111111111111'::uuid]
    ELSE ids 
  END
WHERE id in ('00000001-0000-0000-0000-000000000000')

The ids column is of type _uuid (array of UUID:s).

p-himik 2024-12-02T08:22:51.959519Z

A question about the "why" part - you already have WHERE id IN (x), why do you also need CASE WHEN id = x? With that WHERE, the WHEN branch will always be used anyway.

p-himik 2024-12-02T08:26:34.621919Z

An answer to the "how" part, with no regard for an answer to the above question:

(sql/format
  {:update :mytable
   :set {:ids [:case
               [:= :id "00000001-0000-0000-0000-000000000000"]
               [:array ["11111111-1111-1111-1111-111111111111"] :uuid ]

               :else
               :ids]}
   :where  [:in :id ["00000001-0000-0000-0000-000000000000"]]})
=>
["UPDATE mytable SET ids = CASE WHEN id = ? THEN ARRAY[?]::UUID[] ELSE ids END WHERE id IN (?)"
 "00000001-0000-0000-0000-000000000000"
 "11111111-1111-1111-1111-111111111111"
 "00000001-0000-0000-0000-000000000000"]

I moved the cast to outside of the array, just because it's a bit simpler to express in HoneySQL.

tengstrand 2024-12-02T08:43:48.215159Z

This is just an example. When I run it "for real" there can be hundreds of id:s and several columns to set (this is how we update more than one row at a time). I may have found the problem, because my output doesn't include ::uuid:

["UPDATE mytable SET ids = CASE WHEN id = ? THEN ARRAY[?] ELSE ids END WHERE id IN (?)"
 "00000001-0000-0000-0000-000000000000"
 "11111111-1111-1111-1111-111111111111"
 "00000001-0000-0000-0000-000000000000"]

Maybe I need to use a later version of the library. Right now we use:

[com.github.seancorfield/honeysql "2.3.928"]

p-himik 2024-12-02T08:48:02.828389Z

That's more than two years old, yeah.

tengstrand 2024-12-02T08:52:57.212379Z

I’m new to HoneySQL and wasn’t sure where to begin. Thank you so much for your help!

👍 1

Clojurians Log v2

honeysql