This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2018-07-26
Channels
- # aleph (1)
- # beginners (96)
- # boot (5)
- # cider (44)
- # cljdoc (11)
- # clojure (73)
- # clojure-conj (4)
- # clojure-dev (1)
- # clojure-finland (2)
- # clojure-italy (7)
- # clojure-new-zealand (1)
- # clojure-nl (3)
- # clojure-spec (4)
- # clojure-uk (66)
- # clojurescript (114)
- # code-reviews (16)
- # cursive (15)
- # datomic (37)
- # emacs (6)
- # events (2)
- # figwheel-main (12)
- # fulcro (36)
- # graphql (19)
- # hoplon (2)
- # hyperfiddle (3)
- # jobs (2)
- # leiningen (4)
- # off-topic (36)
- # om (1)
- # om-next (2)
- # other-languages (1)
- # re-frame (12)
- # reagent (12)
- # reitit (5)
- # remote-jobs (4)
- # ring (2)
- # shadow-cljs (218)
- # spacemacs (8)
- # specter (7)
- # sql (34)
- # tools-deps (9)
- # uncomplicate (6)
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
you might take a look at the source for how distinct
is implemented, since it seems IGNORE would be similar
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
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:
-- :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"]
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)
Thanks for the replies. Someone on stack figured it out. Apparently using array in combination with ::type[]
syntax worked.
you'll likely need a custom mysql-insert-into that knows how to handle an optional ignore
@jakemcc using sqlite ๐
yeah that could be, Iโll take a look or just use hugsql ๐
if you do roughly this:
but change the name to :insert-ignore and insert the IGNORE into the string, you'll get something usableFor now execute!
is doing the job ๐
;;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))))
ah, thatโs cool thanks!
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
not using MySQL but SQLite (currently)
but thanks for all the pointers Iโll give that a go later on
I would certainly love to see more honeysql-<vendor>
libraries out there!
(there's also a #honeysql channel @martinklepsch FYI)
my google-fu isn't the strongest. I think I saw mysql at the top and assumed it was a mysql-specific clause
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 ๐