Dynamically constructing SQL

[Roy Varghese]

Hi

This question seems to have come up in various forms, however I have a slightly different use-case and couldn't find a satisfactory solution:

I have a table with two integer fields,  X & Y.

I need to retrieve a list of records matching a set of integer pairs of variable size, i.e.

select * from table where (x = x1 and y = y1) or (x = x2 and y = x2) or (x = x3 and y = y3) ...

Because of 'where' and 'or' being macros, I find I am unable to compose them easily.

Building a sequence and eval-ing it gives a namespace error  (can't find korma.core), plus I am thinking it comes at a runtime cost.

I also tried using a union by making a series of subselects (which are functions), but trying to get 'queries' macro to accept it is proving difficult.

(union

  (queries (subselect ...) (subselect ...))

While I appreciate the korma DSL, I was thinking the real power would be in being able to manipulate sql code as data, but given all the macro definitions it doesn't seem very straightforward.

Any pointers appreciated.

Thanks in advance!

-Roy

[Immo Heikkinen]

Hi,

Here are some options. 

Consider the following example data:

(def xys (partition 2 (range 6)))  
;; ((0 1) (2 3) (4 5))

1) Building a list and eval-ing it:

(dry-run (select :foo (where (eval (list* or (map #(zipmap [:x :y] %) xys))))))
;; dry run :: SELECT foo.* FROM foo WHERE ((foo.x = ? AND foo.y = ?) OR (foo.x = ? AND foo.y = ?) OR (foo.x = ? AND foo.y = ?)) :: [0 1 2 3 4 5]

Really hacky, I wouldn't put eval in my production code.

2) Use korma.sql.fns/pred-or instead of `or` macro

(dry-run (select :foo (where (apply korma.sql.fns/pred-or (map #(zipmap [:x :y] %) xys)))))
;; dry run :: SELECT foo.* FROM foo WHERE ((foo.x = ? AND foo.y = ?) OR (foo.x = ? AND foo.y = ?) OR (foo.x = ? AND foo.y = ?)) :: [0 1 2 3 4 5]

This is probably the cleanest solution, the only downside being that `pred-or` is kind of internals of Korma.

3) Building where string by yourself:

(dry-run (select :foo (where (clojure.string/join " OR " (map (fn [[x y]] (format "(x = %s AND y = %s)" x y)) xys)))))
;; dry run :: SELECT foo.* FROM foo WHERE (x = 0 AND y = 1) OR (x = 2 AND y = 3) OR (x = 4 AND y = 5) :: []
 

4) Union with subqueries:

(dry-run (exec (reduce queries (union*) (map (fn [[x y]] (subselect :foo (where {:x x :y y}))) xys))))
;;dry run :: (SELECT foo.* FROM foo WHERE (foo.x = ? AND foo.y = ?)) UNION (SELECT foo.* FROM foo WHERE (foo.x = ? AND foo.y = ?)) UNION (SELECT foo.* FROM foo WHERE (foo.x = ? AND foo.y = ?)) :: [0 1 2 3 4 5]
 

Not very readable in my opinion.

5) Generate the entire sql statement yourself and use exec-raw.

None of these options is really optimal and constructing sql dynamically is unnecessarily difficult. As you said, this is largely due to heavy use of macros in the implementation. I couldn't agree more with you, if sql was represented as regular Clojure datastructures, it would more easier to compose and manipulate queries. It is unlikely that Korma will take this direction, but I think there's plenty of room for new SQL libs in the ecosystem.

:: Immo 

--
You received this message because you are subscribed to the Google Groups "Korma" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlkorma+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Roy Varghese]

Sorry for the delay, just got back to this thread. I had already resorted to exec-raw, but will go through your other options and see if they work for me.

Thanks much!

-Roy

--
You received this message because you are subscribed to a topic in the Google Groups "Korma" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlkorma/CerHv79y5Lo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlkorma+u...@googlegroups.com.