Expressing (parameterized) ANY(array) query for Postgres in SQLKorma

I'm currently using SQLKorma for a project, and I'm running into a bit of a snag with it.

I have constructed a query with two left-joins; one of them contains an array with entries that I wish to use in my WHERE clause.

This is trivial to express in SQL. Note that this is a primarily redacted query.

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

The portion in regards to ILIKE ANY is what I'd like to be able to translate to Korma.

I understand from the docs that the ANY clause isn't supported from the WHERE clause, and I should look into using raw or exec-raw instead.

With that, I want to pass in a parameterized raw string into the WHERE clause to accomplish what I'm trying to go for.

This I've attempted, but it does fails with a syntax error in 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])))

Specifically:

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

How would I go about this using Korma? Do I have to resort to a full-blown exec-raw query?

Solution

Korma has a very helpful function korma.core/sql-only which will render the SQL string that would be executed.

(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\"]"

or the more readable:

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"]

as you can see, the ILIKE is surrounded by []. Korma's raw just takes a raw string and doesn't support parameterisation like exec-raw does. The vector around the ILIKE string was just turned into a string with its contents. This is why you got a Postgres error about [.

You need to remove the [] from around the ILIKE string if you want to continue using raw, or see if exec-raw is going to fit your needs better. There is the very present danger of SQL injection if you are using 'raw' though which you will need to address.

;; 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)"])))