Fork me on GitHub
#honeysql
<
2022-06-09
>
Miguel15:06:43

How can I select from a function with parameters in honeysql 1.x? Based on the docs this is what I have so far but I get the following error Assert failed: Alias should have two parts["foo(?,?,?)" #sql/param :a #sql/param :b #sql/param :c]

(honeysql/format
    {:select [:res]
     :from   #sql/raw ["foo(?,?,?)" #sql/param :a #sql/param :b #sql/param :c]}
    {:params
     {:a 1
      :b "string"
      :c [1 2 3]}})

seancorfield15:06:36

I don't think it can be done in 1.x. My recommendation would be to add 2.x to your project (you can have both versions in the same project) and switch that one query over to use the 2.x namespaces -- then you won't need raw, you can do an actual function call there.

seancorfield15:06:57

user=> (require '[honey.sql :as hsql])
nil
user=> (hsql/format {:select [:res] :from [[[:foo :?a :?b :?c]]]} {:params {:a 1 :b "string" :c [1 2 3]}})
["SELECT res FROM FOO(?, ?, ?)" 1 "string" [1 2 3]]
user=>

Miguel16:06:51

Actually it can :thumbsup: and its quite simple

(let [a 1
       b "string"
       c [1 2 3]] 
    (honeysql/format
     {:select [:res]
      :from   [(honeysql/call :foo a b (into-array c))]}))

Miguel16:06:02

But yeah definitely need to migrate to 2x

seancorfield17:06:12

Ah, I wasn't sure if the call mechanism would work there. It doesn't work in all contexts.

Noah Bogart15:06:28

question about differences between v1 and v2: right now with v1 we have

(-> (insert-into :temp-table)
    (query-values (-> (select :col1 :col2)
                      (from :other_table)
                      (where [:= :other_col param1])))
    (sql/format :parameterizer :none))
which produces the sql string INSERT INTO temp_table (SELECT col_1, col_2 FROM other_table WHERE (other_col = 'param1')) . As you can see, it neatly inserts the subquery. my attempts at reproducing this with v2 are failing me.
(-> (insert-into :temp-table)
    (columns :col1 :col2)
    (values (-> (select :col1 :col2)
                (from :other_table)
                (where [:= :other_col param1])))
    (v2/format :parameterizer true))
produces the sql string INSERT INTO temp_table (col1, col2) VALUES (select, COL1(col2)), (from, OTHER_TABLE()), (where, (other_col = 'param1')) , which is obviously wrong lol. and switching values to from produces INSERT INTO temp_table (col1, col2) FROM (SELECT col1, col2 FROM other_table WHERE other_col = "param1") , which throws an error on the first FROM. any ideas on how to solve this?

seancorfield15:06:47

First off, the :parameterizer true stuff doesn't exist in v2 and format takes a hash map of options, not named args.

Noah Bogart15:06:31

oops, i do have it as a map in my actual code but missed that :parameterizer doesn't exist

seancorfield15:06:02

You're looking for something like this?

(is (= (format {:insert-into [[:foo [:a :b :c]] {:select [:d :e :f] :from [:baz]}]})
         ["INSERT INTO foo (a, b, c) SELECT d, e, f FROM baz"]))
(that's taken from the tests for HoneySQL)

Noah Bogart15:06:30

oh, yes. i guess i never tried (-> (insert-into :temp-table) (select ...)). that looks like it works

seancorfield15:06:23

Is this is?

user=> (-> (insert-into :temp-table [:col1 :col2]                                                                                                              #_=>     (-> (select :col1 :col2)
  #_=>         (from :other_table)
  #_=>         (where [:= :other_col 42])))
  #_=>     (hsql/format))
["INSERT INTO temp_table (col1, col2) SELECT col1, col2 FROM other_table WHERE other_col = ?" 42]
user=>

Noah Bogart15:06:18

yep, that works. got hung up on the query-values call from the v1