sql 2018-07-26 | Slack Archive

Does anyone know how to do insert or ignore into with honeysql?

@martincharlesrichards it's not part of base HoneySQL, but you can add it yourself pretty easily. HoneySQL is designed to be extensible for vendor-specific SQL

bja18:07:18

you might take a look at the source for how distinct is implemented, since it seems IGNORE would be similar

bja18:07:44

err, I just tried using modifiers and I don't think that works

bwstearns18:07:52

in hugsql/yesql is there a way to cast a list of inputs to the function? I thought something like this would work but it’s only casting the last value SELECT * FROM FOO WHERE name IN (:values)::text

curtis.summers15:07:05

I think postgresql doesn't like this because it's not sure what the starting types are that it needs to case. Even so, you can work around this:

curtis.summers15:07:28

-- :name x 
-- :require [clojure.string :as string]
select * from test where id in (
/*~
(clojure.string/join 
  ","
  (map-indexed (fn [i v] (str ":values." i "::int")) (:values params)))
~*/
)


;; Which will end up giving you something like this:
(x-sqlvec {:values ["1" "2"]})
;=> ["select * from test where id in (?::int,?::int)" "1" "2"]

curtis.summers15:07:07

So, the above takes the values vector and uses HugSQL's deep-get notation to pull in each of the values individually and add the type cast to each one. So, you're effectively building a new set of hugsql parameters on the fly that looks like: in (:values.0::int, :values.1::int, :values.2::int)

bwstearns18:07:31

Thanks for the replies. Someone on stack figured it out. Apparently using array in combination with ::type[] syntax worked.

curtis.summers18:07:29

nice, I think I like that better than the workaround, for sure. 🙂

👍 4

bja18:07:09

you'll likely need a custom mysql-insert-into that knows how to handle an optional ignore

bja18:07:17

maybe a insert-ignore handler

jakemcc18:07:52

@martinklepsch Look at https://github.com/nilenso/honeysql-postgres

jakemcc18:07:39

I guess that was assuming you were doing postgres

jakemcc18:07:52

but probably decent example at how to extend honeysql as well

martinklepsch18:07:53

@jakemcc using sqlite 😄

bja18:07:01

you'll need to do basically what honeysql-postgres does, but for mysql syntax

martinklepsch18:07:15

yeah that could be, I’ll take a look or just use hugsql 😛

bja18:07:41

if you do roughly this:

but change the name to :insert-ignore and insert the IGNORE into the string, you'll get something usable

martinklepsch18:07:46

For now execute! is doing the job 😄

bja18:07:24

;;completely untested!?!
(defmethod format-clause :insert-ignore-into [[_ table] _]
  (if (and (sequential? table) (sequential? (first table)))
    (str "INSERT IGNORE INTO "
         (to-sql (ffirst table))
         " (" (comma-join (map to-sql (second (first table)))) ") "
         (binding [*subquery?* false]
           (to-sql (second table))))
    (str "INSERT IGNORE INTO " (to-sql table))))

martinklepsch18:07:35

ah, that’s cool thanks!

bja18:07:36

that'll handle :insert-ignore-into being in the honeysql map

bja18:07:50

you'll still need to do something similar for ON DUPLICATE KEY UPDATE

bja18:07:59

I think honeysql-postgres has a good example of it for postgresql

bja18:07:14

in particular, how to update the priority map for clauses

bja18:07:23

if you're interested (since you seem to be using MySQL), there is some demand for a honeysql-mysql library in the vein of honeysql-postgres to collect these clauses in a prebuilt fashion

bja18:07:42

I just use postgres 😉

martinklepsch19:07:33

not using MySQL but SQLite (currently)

martinklepsch19:07:52

but thanks for all the pointers I’ll give that a go later on

seancorfield19:07:51

I would certainly love to see more honeysql-<vendor> libraries out there!

seancorfield19:07:05

(there's also a #honeysql channel @martinklepsch FYI)

bja19:07:32

my google-fu isn't the strongest. I think I saw mysql at the top and assumed it was a mysql-specific clause

martinklepsch19:07:50

btw I just tried to figure out how to enable PRAGMA foreign_keys for sqlite, led me to this: https://clojurians-log.clojureverse.org/sql/2018-01-31 🙂

2018-07-26

Channels