表达(参数化)SQLKorma中Postgres的任何(数组)查询

Question

我当前正在使用SQLKorma进行项目，并且我遇到了一些障碍。

我构建了一个用两个左键的查询；其中一个包含一个我希望在WHERE子句中使用的条目的数组。

这是在SQL中表达的琐碎。请注意，这是一个主要的编辑查询。

SELECT
  cu.name,
  c.description,
  c.created_at AT TIME ZONE 'utc'
FROM calendar_users cu LEFT JOIN calendars c ON cu.id = c.user_id
  LEFT JOIN meetings m ON c.id = m.id
WHERE 'status_report' ILIKE ANY (m.meeting_metadata)
GROUP BY m.meeting_metadata, c.created_at, cu.name, cu.description
ORDER BY c.created_at DESC

ILIKE ANY的部分是我想翻译成Korma的部分。

我从文档中了解到ANY子句不支持WHERE子句，我应该考虑使用raw或exec-raw。

这样，我想将一个参数化的raw字符串传递到WHERE子句中，以完成我想做的事情。

这是我尝试过的，但是在Postgres中的语法错误确实失败了：

(select calendars
    (fields calendar-user-cols)
    (join :calendar_users (= :calendars.user_id :calendar_users.id))
    (join :meetings (= :calendars.id :meetings.id))
    (where (raw ["? ILIKE ANY(meetings.meeting_metadata)" metadata])))

特别：

PSQLException:
 Message: ERROR: syntax error at or near "["
  Position: 1006
 SQLState: 42601
 Error Code: 0

我将如何使用Korma进行此操作？我是否必须求助于成熟的exec-raw查询？

Answer 1

korma具有非常有用的功能korma.core/sql-only，该功能将渲染将执行的SQL字符串。

(defentity calendars)
=> #'korma-test.core/calendars
(sql-only
  (select calendars
          (fields :x :y)
          (join :calendar_users (= :calendars.user_id :calendar_users.id))
          (join :meetings (= :calendars.id :meetings.id))
          (where (raw ["? ILIKE ANY(meetings.meeting_metadata)" "status_report"]))))
=> "SELECT "calendars"."x", "calendars"."y" FROM ("calendars" LEFT JOIN "calendar_users" ON "calendars"."user_id" = "calendar_users"."id") LEFT JOIN "meetings" ON "calendars"."id" = "meetings"."id" WHERE ["? ILIKE ANY(meetings.meeting_metadata)" "status_report"]"

或更可读的：

SELECT "calendars"."x",
       "calendars"."y"
FROM ("calendars"
      LEFT JOIN "calendar_users" ON "calendars"."user_id" = "calendar_users"."id")
LEFT JOIN "meetings" ON "calendars"."id" = "meetings"."id"
WHERE ["? ILIKE ANY(meetings.meeting_metadata)" "status_report"]

您可以看到，Ilike被[]包围。Korma的raw仅采用一个原始字符串，并且不像exec-raw那样支持参数化。Ilike字符串周围的向量刚刚变成带有其内容的字符串。这就是为什么您会在[上获得Postgres错误。

如果要继续使用raw，则需要从Ilike字符串周围删除[]，或者查看exec-raw是否会更好地满足您的需求。如果您使用"原始"，则需要解决SQL注入的危险。

;; require clojure.string :as str in your ns
;; change your clause from
(where (raw ["? ILIKE ANY(meetings.meeting_metadata)" "status_report"])))
;; to this
(where (raw (str/join " " ["'status_report'" "ILIKE ANY(meetings.meeting_metadata)"])))