Building a jdbc WHERE IN clause

[Jonathon McKitrick]

I saw a post some time ago answering this question, and here are three solutions.  I thought the first would be the most elegant.  But for some reason, the first does not work, and returns an empty set.  I'm trying to understand what's wrong with it.

(defn get-by-ids-test

  [ids]

  (let [qs (string/join "," (repeat (count ids) "?"))

        sql (str "select * from survey where survey_id in (" qs ")")]

    (println "SQL " sql)

    (println "ids" ids)

    (sql/with-connection (get-db-spec)

      (sql/with-query-results results

        [sql ids]

        (into [] results)))))

(defn get-by-ids-test-2

  [ids]

  (sql/with-connection (get-db-spec)

    (sql/with-query-results results

      [(str "select * from survey where survey_id in (" (apply str (interpose \, ids)) ")")]

      (into [] results))))

(defn get-by-ids-test-3

  [ids]

  (sql/with-connection (get-db-spec)

    (sql/with-query-results results

      [(str "select * from survey where survey_id in (" (string/join "," ids)  ")")]

      (into [] results))))

[Sean Corfield]

On Fri, Jan 25, 2013 at 2:27 PM, Jonathon McKitrick
<jmcki...@gmail.com> wrote:
> (defn get-by-ids-test
> [ids]
> (let [qs (string/join "," (repeat (count ids) "?"))
> sql (str "select * from survey where survey_id in (" qs ")")]
> (println "SQL " sql)
> (println "ids" ids)
> (sql/with-connection (get-db-spec)
> (sql/with-query-results results
> [sql ids]

Try:
(into [sql] ids)

> (into [] results)))))

The vector should be a SQL string followed by the various parameter
values. You have a SQL string followed by a single item - a sequence
of parameter values.

In the next version of java.jdbc (currently 0.2.4-SNAPSHOT but it will
become 0.3.0), you'll be able to simplify your code to this:

(sql/query (get-db-spec) (into [sql] ids))

and get back a fully realized sequence of maps. If you want a vector instead:

(sql/query (get-db-spec) (into [sql] ids) :resultset-fn vec)

;; the current code has :result-set-fn but that will change since it's
not consistent with the resultset type or resultset-seq function!

Since the question of `where in` comes in fairly often, I will
probably extend the minimal DSL that 0.3.0 will add (in a separate
optional ns) to support that, e.g.,

(sql/query (get-db-spec) (dsl/select * :survey (dsl/where
{:survey_id ids})))

perhaps with dsl/in to make it read better... although I expect you'd
:refer in the symbols you needed to get this:

(query (get-db-spec) (select * :survey (where {:survey_id (in ids)})))

The current thinking is to keep the DSL deliberately small (and
optional) so that other folks can develop richer DSLs that are
compatible with java.jdbc directly (i.e., this is explicitly not
intended to "compete" with Korma - just to make Korma's job easier).
--
Sean A Corfield -- (904) 302-SEAN
An Architect's View -- http://corfield.org/
World Singles, LLC. -- http://worldsingles.com/

"Perfection is the enemy of the good."
-- Gustave Flaubert, French realist novelist (1821-1880)

[Jonathon McKitrick]

On Friday, January 25, 2013 7:34:29 PM UTC-5, Sean Corfield wrote:

Try:
    (into [sql] ids)

That worked perfectly.

 

In the next version of java.jdbc (currently 0.2.4-SNAPSHOT but it will
become 0.3.0), you'll be able to simplify your code to this:

    (sql/query (get-db-spec) (into [sql] ids))

Nice.  I'll be watching for it.