Prepared statement with subselect using Clojure/Korma/Postgres

Question

I've been trying to do the following in Korma to no avail:

sql:

PREPARE q (int) AS SELECT * FROM post a
    WHERE EXISTS 
        (SELECT parent_id
         FROM post_map 
         WHERE parent_id=a.id AND parent_id=$1);
EXECUTE q(1);

my best Korma attempt:

(defn children [parent-id]        ;; clojure
      (if (number? parent-id)
        (exec-raw (str
          "PREPARE q (int) AS SELECT * FROM post a WHERE EXISTS 
               (SELECT parent_id FROM post_map WHERE parent_id=a.id AND parent_id=$1); 
           EXECUTE q(" parent-id ")") 
         :results)))

And this is the error I keep getting: (I don't really understand the :: operator below:)

Failure to execute query with SQL:
PREPARE q (int) AS SELECT * FROM post a WHERE EXISTS 
          (SELECT parent_id FROM post_map WHERE parent_id=a.id AND parent_id=$1); 
       EXECUTE q(1)  ::  nil
PSQLException:
 Message: No results were returned by the query.
 SQLState: 02000
 Error Code: 0
PSQLException No results were returned by the query.  org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery (AbstractJdbc2Statement.java:274)

I don't think this is a terribly outlandish thing to want to do with a query, so I'm wondering if Korma just isn't going to work for my project. Am I just doing it wrong?

UPDATE: this is what I ended up doing (after I bailed on Korma [sorry Korma]).

(defn children [parent-id]
  (if (unsigned? parent-id)
    (sql/with-connection db
      (sql/with-query-results results
         [(str "select " field-list ", b.parent_id from post a, post_map b where a.id=b.child_id and a.id in "
               "(select child_id from post c, post_map d where c.id=d.parent_id and c.id=?)") parent-id]
           (into [] results)))))

Terje Sten Bjerkseth · Accepted Answer · 2013-03-09 17:51:08Z

2

Korma uses do-prepared under the hood, which only works for a single statement and also prepares it for you.

This works:

;; but isn't it more 'parent' than 'children'?
(defn children-raw [parent-id]
  (if (number? parent-id)
    (exec-raw [(str
                "SELECT * FROM post a WHERE EXISTS
               (SELECT parent_id FROM post_map WHERE parent_id=a.id AND parent_id=?)")
               [parent-id]]
              :results)))

I'm slightly confused by your naming/schema, though, even if it is hard to tell from this small example. Your function is called "childREN" but with the EXISTS() I think it will return max a single record. Also, it seems to select the parent, but with the implicit join it will always select itself?

If post_map is a link from id to parent_id and you want to get the children, I would think more along the lines of:

(defentity post
  (entity-fields :id))
(defentity post_map
  (entity-fields :id :parent_id))
(defn children-dsl [parent-id]
  (if (number? parent-id)
    (select post
            (where {:id [in (subselect post_map
                                       (fields :id)
                                       (where {:parent_id parent-id}))]}))))

answered Mar 9, 2013 at 17:51

Terje Sten Bjerkseth

6836 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tjb1982 Over a year ago

Yeah, my query was messed up to begin with (which wasn't really what I was focusing on when I asked this question), but I didn't recognize that until I switched away from korma and back to the jdbc api. I feel like the difference in the level of effort and lines of code between them is negligible and I just wasn't finding Korma intuitive/flexible enough.

Collectives™ on Stack Overflow

Prepared statement with subselect using Clojure/Korma/Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related