Clojurians Log v2
Clojure programming
Channels
Apps
honeysql
igrishaev 2023-06-08T10:18:40.967609Z
I'm trying to build a query with CTE that carries VALUES:
with vals (a, b) as (
values (1, 2),
(3, 4)
)
select table.*
from table join vals on ...{:with
[
[[:vals {:columns [:foo :bar]}]
{:values [{:foo 1 :bar 2} {:foo 1 :bar 2}]}]
]
:select [:*]
:from [:events]
:join
[:vals [:= :foo.bar :events.test]]
}"WITH vals (foo, bar) AS ((foo, bar) VALUES (?, ?), (?, ?))
SELECT * FROM events...(foo, bar) in front of VALUES. How can I do this?igrishaev 2023-06-08T10:24:06.334109Z
ah, it must have been arrays:
{:values [[1 2] [3 4]]}
seancorfield 2023-06-08T18:43:19.630979Z
There is an example in the docs:
You can use a VALUES clause in the CTE:
user=> (sql/format {:with [[[:stuff {:columns [:id :name]}]
{:values [[1 "Sean"] [2 "Jay"]]}]]
:select [:id :name]
:from [:stuff]})
["WITH stuff (id, name) AS (VALUES (?, ?), (?, ?)) SELECT id, name FROM stuff" 1 "Sean" 2 "Jay"]igrishaev 2023-06-08T18:48:29.915479Z
Yeah I missed it somehow. Thank you!
seancorfield 2023-06-08T19:04:14.714149Z
NP. Your Q made me wonder if I'd omitted that from the docs, so I needed to check. I guess it could also state explicitly that you can't use the vector-of-maps version of :values...
seancorfield 2023-06-08T19:05:40.327939Z