Using non-standard postgres operators with SQL Korma

Question

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?

I find [YeSQL](https://github.com/krisajenkins/yesql) brilliant for these kind of situations. It allows you to keep your SQL queries written in plain SQL, but provides a really neat way of calling them from Clojure. — Daniel Neal, Feb 10 '14 at 10:45
I'll have to remember YeSQL; there's a lot of good use cases for that strategy. However, it looks like it's not good at dynamically generating queries, which would make it unsuitable for this use case. — Brad Koch, Feb 10 '14 at 15:03

score 1 · Accepted Answer · edited Feb 13 '14 at 15:04

1

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 "..."]})))

edited Feb 13 '14 at 15:04

Brad Koch

19,267
19
110
137

answered Feb 08 '14 at 22:57

ponzao

20,684
3
41
58

That's one solution, but now I have a vector of where conditions to toss around until I finish building the query. Keeping track of static conditions and a map that continues to be dynamically altered will be complicated. – Brad Koch Feb 08 '14 at 23:03
@BradKoch, fair enough. I edited and added another option (I only dry-ran the code so I am not exactly sure if Postgres will accept it). – ponzao Feb 08 '14 at 23:14
1

Nice! Just discovered that one too. Filing an issue on this, korma has some undocumented [functionality](https://github.com/korma/Korma/blob/ce206b3/src/korma/sql/engine.clj#L215) there; not sure what their intent was. Supporting custom predicates in a simpler way shouldn't be too hard. – Brad Koch Feb 08 '14 at 23:22

Using non-standard postgres operators with SQL Korma

1 Answers1