Search code examples
postgresqlclojuresqlkorma

Using non-standard postgres operators with SQL Korma

Some complex features of Postgres utilize operators that are not part of the SQL standard. One simple example is the set of POSIX regular expression operators; I need them to include a where clause expression that utilizes word boundaries.

Let's say that I want to find widgets that come in size 1, where size is a string containing a json encoded list of integers.

Sample data:

ID  Size
1   "[1]"
2   "[1,2,4,12]"
3   "[4,12]"
4   "[2,4]"

This is trivial with raw SQL:

SELECT * FROM widgets WHERE size ~ '\m1\M'

But gets very difficult with korma. Korma does allow the use of predicates in the where map, but the functionality is very restrictive. Some things that don't work:

=> (select "widgets" (where {:size ["~" "\\m1\\M"]}))
ClassCastException java.lang.String cannot be cast to clojure.lang.IFn korma.sql.engine/pred-vec (engine.clj:218)

=> (select "widgets" (where {:size [(raw "~") "\\m1\\M"]}))
Failure to execute query with SQL:
SELECT "widgets".* FROM "widgets" WHERE (?)  ::  [\m1\M]

=> (select "widgets" (where {:size (raw "~ '\\m1\\M'")}))
Failure to execute query with SQL:
SELECT "widgets".* FROM "widgets" WHERE ("widgets"."size" = ~ '\m1\M')  ::  []

=> (sql-only (select "widgets" (where {:size [(raw "~ '\\m1\\M'")]})))
"SELECT \"widgets\".* FROM \"widgets\" WHERE (NULL)"

A complicating factor is that other conditions are dynamically added to the where map after this one. So even though the following example works, it doesn't allow for construction of that map:

=> (sql-only (select "widgets" (where (raw "size ~ '\\m1\\M'"))))
"SELECT \"widgets\".* FROM \"widgets\" WHERE size ~ '\\m1\\M'"

So, is using non standard operators like ~ to perform this match possible in korma in conjunction with a where map? How would you do it? Best alternatives or workarounds?

Solution

  • You can add additional where clauses, from the official documentation (http://sqlkorma.com/docs#select):

    ;; Multiple where's are joined with AND, so this
;; is also the same:
(-> (select* users)
    (where {:first "john"})
    (where {:last "doe"})
    (as-sql))

    So you can do something like this:

    (sql-only (select "widgets"
            (where (raw "size ~ '\\m1\\M'"))
            (where {:.. "?"})))

    EDIT: Another option

    You could create your own custom predicate:

    (require '[korma.sql.engine :refer [infix]])

(defn tilde
  [k v]
  (infix k "~" v))

(sql-only
  (select "widgets"
    (where {:size [tilde "\\m1\\M"]
            :... [like "..."]})))