Fork me on GitHub
Slack Archive

This page is not created by, affiliated with, or supported by Slack Technologies, Inc.

2023-06-08

Channels

#honeysql
<
2023-06-08
>
igrishaev10:06:40

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 ...
here is what I've got in my code: 
{:with 
   [
    [[:vals {:columns [:foo :bar]}]
     {:values [{:foo 1 :bar 2} {:foo 1 :bar 2}]}]
    ]
   :select [:*]
   :from [:events]
   :join
   [:vals [:= :foo.bar :events.test]]
   }
which gives 
"WITH vals (foo, bar) AS ((foo, bar) VALUES (?, ?), (?, ?)) 
SELECT * FROM events...
the problem is, I cannot get rid from the second (foo, bar) in front of VALUES. How can I do this?

igrishaev10:06:06

ah, it must have been arrays: 

{:values [[1 2] [3 4]]}

seancorfield18:06:19

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"]
from https://cljdoc.org/d/com.github.seancorfield/honeysql/2.4.1033/doc/getting-started/sql-clause-reference#with-with-recursive

igrishaev18:06:29

Yeah I missed it somehow. Thank you!

seancorfield19:06:14

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...