根据键类型解析地图和过滤的向量

Question

我有一个地图向量，我正在尝试将这些地图解析为一个将在RAW SQL查询中使用的字符串。

(def params [{:fieldName "salary" :comparator "greater" :inputValue "10000" :inputType "number"}
             {:fieldName "name" :comparator "include" :inputValue "Ros" :inputType "text"}
             {:fieldName "start_date" :comparator "between" :inputValue "2022-01-01" :maxInputValue "2022-06-01" :inputType "date"}])

我需要从每张映射中提取的内容是基于的类型：比较器 and ：fieldName我创建一个strinng，一旦我对其进行解析，请放置所有字符串在一个字符串中。每个fieldName将在向量中仅重复一次。我在下面写了一些东西，但我试图做得更好，而且它仅适用于一种类型，这将使​​所有比较器 and field> fieldname

(defn convert
  [params]
  (cond-> nil
    (= "equal" (->> params
                    (filter #(= (:fieldName %) "salary"))
                    (first)
                    (:comparator))) (conj " AND salary= ?")
    (= "not-equal" (->> params
                      (filter #(= (:fieldName %) "salary"))
                      (first)
                      (:comparator))) (conj " AND salary!= ?")
    (= "greater" (->> params
                      (filter #(= (:fieldName %) "salary"))
                      (first)
                      (:comparator))) (conj " AND salary > ?")))

我想这样做

"AND salary > ? 
 AND name LIKE 'Ros%'
 AND start BETWEEN between '2022-01-01' AND '2022-06-01' "

Answer 1

我不确定您想在这里拥有的所有选项，所以这是我的第一次尝试（请注意，我将名称更改为kebab-case）：

(def params [{:field-name "salary" :comparator "greater" :input-value "10000" :input-type "number"}
             {:field-name "name" :comparator "include" :input-value "Ros" :input-type "text"}
             {:field-name "start-date" :comparator "between" :input-value "2022-01-01" :max-input-value "2022-06-01" :input-type "date"}])

(defn convert [params]
  (->> (for [{:keys [field-name comparator input-value max-input-value input-type]} params]
         (->> (condp = input-type
                "number" [field-name ({"greater" ">" "less" "<" "equal" "=" "not-equal" "!="} comparator) input-value]
                "text" [field-name ({"include" "LIKE"} comparator) (str "'%" input-value "%'")]
                "date" [field-name ({"between" "BETWEEN"} comparator) (str "'" input-value "'") "AND" (str "'" max-input-value "'")])
              (str/join " ")))
       (str/join " AND ")))

(convert params)
=> "salary > 10000 AND name LIKE '%Ros%' AND start-date BETWEEN '2022-01-01' AND '2022-06-01'"

Answer 2

这当然是一个不错的用例

对于多途径可能是这样实现的，

(def sql-hierarchy
  (-> (make-hierarchy)
      (derive :bounded :any)
      (derive :text :any)
      (derive :number :bounded)
      (derive :date :bounded)))

(defmulti to-sql
  (fn [{:keys [input-type comparator]}]
    (mapv keyword [input-type comparator]))
  :hierarchy #'sql-hierarchy)

(defmethod to-sql [:text :include]
  [{:keys [field-name input-value]}]
  {:q (format "%s like '%%%s%%'" field-name input-value)  :p []})

(defmethod to-sql [:bounded :less]
  [{:keys [field-name input-value]}]
  {:q (format "%s < ?" field-name) :p [input-value]})

(defmethod to-sql [:bounded :greater]
  [{:keys [field-name input-value]}]
  {:q (format "%s > ?" field-name) :p [input-value]})

(defmethod to-sql [:bounded :between]
  [{:keys [field-name input-value max-input-value]}]
   {:q (format "%s between ? and ?" field-name) :p [input-value max-input-value]})

。像这样：

(def params [{:field-name "salary" :comparator "greater" :input-value "10000" :input-type "number"}
             {:field-name "name" :comparator "include" :input-value "Ros" :input-type "text"}
             {:field-name "start_date" :comparator "between" :input-value "2022-01-01" :max-input-value "2022-06-01" :input-type "date"}])

(let [data (map to-sql params)]
  {:query (clojure.string/join " and " (map :q data))
   :params (into [] (mapcat :p) data)})

;;=>{:query "salary > ? and name like '%Ros%' and start_date between ? and ?",
;;   :params ["10000" "2022-01-01" "2022-06-01"]}

其中最好的部分是，您始终可以添加新的类型 /选择器，而无需触摸现有的（大概是经过良好测试）代码。例如，您想添加in-list操作员：

(defmethod to-sql [:any :in-list]
  [{:keys [field-name input-value]}]
   {:q (format "%s in (%s)"
               field-name
               (clojure.string/join ", " (repeat (count input-value) "?")))
    :p input-value})

(def params [{:field-name "salary" :comparator "greater" :input-value "10000" :input-type "number"}
             {:field-name "name" :comparator "include" :input-value "Ros" :input-type "text"}
             {:field-name "rank" :comparator "in-list" :input-value ["middle" "junor"] :input-type "text"}
             {:field-name "start_date" :comparator "between" :input-value "2022-01-01" :max-input-value "2022-06-01" :input-type "date"}])

(let [data (map to-sql params)]
  {:query (clojure.string/join " and " (map :q data))
   :params (into [] (mapcat :p) data)})

;;=> {:query "salary > ? and name like '%Ros%' and rank in (?, ?) and start_date between ? and ?",
;;    :params ["10000" "middle" "junor" "2022-01-01" "2022-06-01"]}