I want to generate dynamic sql where conditions with honeysql . Here is my code that has achieved the result I want. Is there a better or easy way to write it?

(defn select-audit-list
  [conn {revitId :revitId
         type :type 
         pro :pro
         username :username}]
  (let [con (data.common/with-logging conn)]
    (let [
          wheresql (cond-> [:and [:= revitId :RevitID]]
                     (not (empty? type)) (conj [:in :Type type])
                     (not (empty? pro)) (conj [:in :Pro pro])
                     (not (empty? username)) (conj [:in :username username]))
          sqlmap {:select :*
                  :from :audit
                  :where wheresql}]
      sqlmap
      (sql/format sqlmap {:inline true}))))
  
(def args {
             :revitId "123"
             :type [1 2 3]
             :pro ["p1" "p2" "p3"]
             :username ["user1"]
  })
  
(select-audit-list nil args)
==> ["SELECT * FROM audit WHERE ('123' = RevitID) AND (Type IN (1, 2, 3)) AND (Pro IN ('p1', 'p2', 'p3')) AND (username IN ('user1'))"]

(select-audit-list2 nil (assoc args :username []))
==> ["SELECT * FROM audit WHERE ('123' = RevitID) AND (Type IN (1, 2, 3)) AND (Pro IN ('p1', 'p2', 'p3'))"]

FYI. We’ve built some fns on top of honey to make stuff a bit more fluent and saves us from runtime errors:

(def none [:= true false])

(def all [:= true true])

(defn in
  [column-name values]
  (if (seq values)
    [:in column-name (set values)]
    none))

(defn and [& clauses]
  (into [:and] (seq clauses)))

(defn or [& clauses]
  (into [:or] (seq clauses)))

(defn lift [x]
  [:lift x])

now that's interesting

the in bit especially

filtering of nil clauses would be useful, so you could go (and (when (seq type) (in :Type type)) (when (seq pro) (in :Pro pro)))

(defmacro in-when
  [condition column-name values]
  `(when ~condition
     [:in ~column-name ~values]))

(defn and [& clauses]
  (let [c (remove nil? clauses)]
    (when (seq c)
      (if (second c)
        (into [:and] (seq c))
        (first c)))))

you could do the same with or

The and and or are nice in so far that you can write (pq/and foo bar baz) but, it’s a bit sucky in this situation (apply pq/and (map lol xs))

The reason for lift is so that you can (update foo :myjsonprop lift)

Hello guys. How can I use :drop-table clause with table names such as user (reserved names) I’m trying these things (sql/format {:drop-table "foo"}) => ["DROP TABLE FOO"] (sql/format {:drop-table :foo}) => ["DROP TABLE foo"] and it doesn’t work

(sql/format {:create-table "foo"}) does work indeed => ["CREATE TABLE \"foo\""]

wanted to run some functions by you to see if you're interested in something like this in honeysql. putting it in this thread