Back
find-by-keys (clj)
(source)function
(find-by-keys connectable table key-map)
(find-by-keys connectable table key-map opts)
Syntactic sugar over `execute!` to make certain common queries easier.
Given a connectable object, a table name, and either a hash map of
columns and values to search on or a vector of a SQL where clause and
parameters, returns a vector of hash maps of rows that match.
If `:all` is passed instead of a hash map or vector -- the query will
select all rows in the table, subject to any pagination options below.
If `:columns` is passed, only that specified subset of columns will be
returned in each row (otherwise all columns are selected).
If the `:order-by` option is present, add an `ORDER BY` clause. `:order-by`
should be a vector of column names or pairs of column name / direction,
which can be `:asc` or `:desc`.
If the `:top` option is present, the SQL Server `SELECT TOP ?` syntax
is used and the value of the option is inserted as an additional parameter.
If the `:limit` option is present, the MySQL `LIMIT ? OFFSET ?` syntax
is used (using the `:offset` option if present, else `OFFSET ?` is omitted).
PostgreSQL also supports this syntax.
If the `:offset` option is present (without `:limit`), the standard
`OFFSET ? ROWS FETCH NEXT ? ROWS ONLY` syntax is used (using the `:fetch`
option if present, else `FETCH...` is omitted).
Examples
next-jdbc
(ns next.jdbc.sql-test
"Tests for the syntactic sugar SQL functions."
(:require [clojure.test :refer [deftest is testing use-fixtures]]
[next.jdbc :as jdbc]
[next.jdbc.specs :as specs]
[next.jdbc.sql :as sql]
[next.jdbc.test-fixtures
:refer [with-test-db ds column default-options
derby? jtds? maria? mssql? mysql? postgres? sqlite?]]
[next.jdbc.types :refer [as-other as-real as-varchar]]))
(deftest test-find-all-offset
(let [ds-opts (jdbc/with-options (ds) (default-options))
rs (sql/find-by-keys
ds-opts :fruit :all
(assoc
(if (or (mysql?) (sqlite?))
{:limit 2 :offset 1}
{:offset 1 :fetch 2})
:columns [:ID
["CASE WHEN grade > 91 THEN 'ok ' ELSE 'bad' END"
:QUALITY]]
:order-by [:id]))]
(is (= 2 (count rs)))
(is (every? map? rs))
(is (every? meta rs))
(is (every? #(= 2 (count %)) rs))
(is (= 2 ((column :FRUIT/ID) (first rs))))
(is (= "ok " ((column :QUALITY) (first rs))))
(is (= 3 ((column :FRUIT/ID) (last rs))))
(is (= "bad" ((column :QUALITY) (last rs))))))
(deftest test-find-by-keys
(let [ds-opts (jdbc/with-options (ds) (default-options))]
(let [rs (sql/find-by-keys ds-opts :fruit {:appearance "neon-green"})]
(is (vector? rs))
(is (= [] rs)))
(let [rs (sql/find-by-keys ds-opts :fruit {:appearance "yellow"})]
(is (= 1 (count rs)))
(is (every? map? rs))
(is (every? meta rs))
(is (= 2 ((column :FRUIT/ID) (first rs)))))))
(deftest no-empty-example-maps
(is (thrown? clojure.lang.ExceptionInfo
(sql/find-by-keys (ds) :fruit {})))
(is (thrown? clojure.lang.ExceptionInfo
(sql/update! (ds) :fruit {} {})))
(is (thrown? clojure.lang.ExceptionInfo
(sql/delete! (ds) :fruit {}))))
(deftest no-empty-order-by
(is (thrown? clojure.lang.ExceptionInfo
(sql/find-by-keys (ds) :fruit
{:name "Apple"}
{:order-by []}))))
(deftest array-in
(when (postgres?)
(let [data (sql/find-by-keys (ds) :fruit ["id = any(?)" (int-array [1 2 3 4])])]
(is (= 4 (count data))))))
seancorfield/next-jdbc
(ns next.jdbc.sql-test
"Tests for the syntactic sugar SQL functions."
(:require [clojure.test :refer [deftest is testing use-fixtures]]
[next.jdbc :as jdbc]
[next.jdbc.specs :as specs]
[next.jdbc.sql :as sql]
[next.jdbc.test-fixtures
:refer [with-test-db ds column default-options
derby? jtds? maria? mssql? mysql? postgres? sqlite?]]
[next.jdbc.types :refer [as-other as-real as-varchar]]))
(deftest test-find-all-offset
(let [ds-opts (jdbc/with-options (ds) (default-options))
rs (sql/find-by-keys
ds-opts :fruit :all
(assoc
(if (or (mysql?) (sqlite?))
{:limit 2 :offset 1}
{:offset 1 :fetch 2})
:columns [:ID
["CASE WHEN grade > 91 THEN 'ok ' ELSE 'bad' END"
:QUALITY]]
:order-by [:id]))]
(is (= 2 (count rs)))
(is (every? map? rs))
(is (every? meta rs))
(is (every? #(= 2 (count %)) rs))
(is (= 2 ((column :FRUIT/ID) (first rs))))
(is (= "ok " ((column :QUALITY) (first rs))))
(is (= 3 ((column :FRUIT/ID) (last rs))))
(is (= "bad" ((column :QUALITY) (last rs))))))
(deftest test-find-by-keys
(let [ds-opts (jdbc/with-options (ds) (default-options))]
(let [rs (sql/find-by-keys ds-opts :fruit {:appearance "neon-green"})]
(is (vector? rs))
(is (= [] rs)))
(let [rs (sql/find-by-keys ds-opts :fruit {:appearance "yellow"})]
(is (= 1 (count rs)))
(is (every? map? rs))
(is (every? meta rs))
(is (= 2 ((column :FRUIT/ID) (first rs)))))))
(deftest no-empty-example-maps
(is (thrown? clojure.lang.ExceptionInfo
(sql/find-by-keys (ds) :fruit {})))
(is (thrown? clojure.lang.ExceptionInfo
(sql/update! (ds) :fruit {} {})))
(is (thrown? clojure.lang.ExceptionInfo
(sql/delete! (ds) :fruit {}))))
(deftest no-empty-order-by
(is (thrown? clojure.lang.ExceptionInfo
(sql/find-by-keys (ds) :fruit
{:name "Apple"}
{:order-by []}))))
(deftest array-in
(when (postgres?)
(let [data (sql/find-by-keys (ds) :fruit ["id = any(?)" (int-array [1 2 3 4])])]
(is (= 4 (count data))))))
jacekschae/learn-reitit-course-files
(ns cheffy.conversation.db
(:require [next.jdbc.sql :as sql]
[next.jdbc :as jdbc])
(:import (java.util UUID)))
(defmethod dispatch :find-conversation-by-uid
[[_ db {:keys [uid]}]]
(with-open [conn (jdbc/get-connection db)]
(let [conn-opts (jdbc/with-options conn (:options db))
conversations (sql/find-by-keys conn-opts :conversation {:uid uid})]
(doall
(into []
(for [{:conversation/keys [conversation-id] :as conversation} conversations
:let [{:message/keys [created-at]}
(jdbc/execute-one! conn-opts ["SELECT created_at FROM message
WHERE conversation_id = ?
ORDER BY created_at DESC
LIMIT 1" conversation-id])
with
(jdbc/execute-one! conn-opts ["SELECT uid FROM conversation
WHERE uid != ? AND conversation_id = ?" uid conversation-id])
[{:account/keys [name picture]}] (sql/find-by-keys conn-opts :account with)]]
(assoc conversation
:conversation/updated-at created-at
:conversation/with-name name
:conversation/with-picture picture)))))))
(defmethod dispatch :find-messages-by-conversation
[[_ db conversation]]
(sql/find-by-keys db :message conversation))
jacekschae/learn-reitit-course-files
(ns cheffy.conversation.db
(:require [next.jdbc.sql :as sql]
[next.jdbc :as jdbc])
(:import (java.util UUID)))
(defmethod dispatch :find-conversation-by-uid
[[_ db {:keys [uid]}]]
(with-open [conn (jdbc/get-connection db)]
(let [conn-opts (jdbc/with-options conn (:options db))
conversations (sql/find-by-keys conn-opts :conversation {:uid uid})]
(doall
(for [{:conversation/keys [conversation-id] :as conversation} conversations
:let [{:message/keys [created-at]}
(jdbc/execute-one! conn-opts ["SELECT created_at FROM message
WHERE conversation_id = ?
ORDER BY created_at DESC
LIMIT 1" conversation-id])
with
(jdbc/execute-one! conn-opts ["SELECT uid FROM conversation
WHERE uid != ? AND conversation_id = ?" uid conversation-id])
[{:account/keys [name picture]}] (sql/find-by-keys conn-opts :account with)]]
(assoc conversation
:conversation/updated-at created-at
:conversation/with-name name
:conversation/with-picture picture))))))
(defmethod dispatch :find-messages-by-conversation
[[_ db conversation]]
(sql/find-by-keys db :message conversation))
jacekschae/learn-reitit-course-files
(ns user
(:require [integrant.repl :as ig-repl]
[integrant.core :as ig]
[integrant.repl.state :as state]
[cheffy.server]
[next.jdbc :as jdbc]
[next.jdbc.sql :as sql]))
(comment
(app {:request-method :get
:uri "/swagger.json"})
(jdbc/execute! db ["SELECT * FROM recipe WHERE public = true"])
(sql/find-by-keys db :recipe {:public true})
(go)
(halt)
(reset))
jacekschae/learn-reitit-course-files
(ns cheffy.conversation.db
(:require [next.jdbc.sql :as sql]
[next.jdbc :as jdbc]))
(defmethod dispatch :find-conversation-by-uid
[[_ db {:keys [uid]}]]
(with-open [conn (jdbc/get-connection db)]
(let [conn-opts (jdbc/with-options conn (:options db))
conversations (sql/find-by-keys conn-opts :conversation {:uid uid})]
(doall
(for [{:conversation/keys [conversation-id] :as conversation} conversations
:let [{:message/keys [created-at]}
(jdbc/execute-one! conn-opts ["SELECT created_at FROM message
WHERE conversation_id = ?
ORDER BY created_at DESC
LIMIT 1" conversation-id])
with
(jdbc/execute-one! conn-opts ["SELECT uid FROM conversation
WHERE uid != ? AND conversation_id = ?" uid conversation-id])
[{:account/keys [name picture]}] (sql/find-by-keys conn-opts :account with)]]
(assoc conversation
:conversation/updated-at created-at
:conversation/with-name name
:conversation/with-picture picture))))))