Back
for-query (clj)
(source)function
(for-query table where-params opts)
Given a table name and either a hash map of column names and values or a
vector of SQL (where clause) and its parameters, return a vector of the
full `SELECT` SQL string and its parameters.
Applies any `:table-fn` / `:column-fn` supplied in the options.
Handles pagination options (`:top`, `:limit` / `:offset`, or `:offset` /
`:fetch`) for SQL Server, MySQL / SQLite, ANSI SQL respectively.
By default, this selects all columns, but if the `:columns` option is
present the select will only be those columns.
If `:suffix` is provided in `opts`, that string is appended to the
`SELECT ...` statement.
Examples
next-jdbc
(ns next.jdbc.sql.builder-test
"Tests for the SQL string building functions in next.jdbc.sql.builder."
(:require [clojure.test :refer [deftest is testing]]
[next.jdbc.quoted :refer [mysql sql-server]]
[next.jdbc.sql.builder :as builder]))
(deftest test-for-query
(testing "by example"
(is (= (builder/for-query
:user
{:id 9}
{:table-fn sql-server :column-fn mysql :order-by [:a [:b :desc]]})
["SELECT * FROM [user] WHERE `id` = ? ORDER BY `a`, `b` DESC" 9]))
(is (= (builder/for-query :user {:id nil} {:table-fn sql-server :column-fn mysql})
["SELECT * FROM [user] WHERE `id` IS NULL"]))
(is (= (builder/for-query :user
{:id nil}
{:table-fn sql-server :column-fn mysql
:suffix "FOR UPDATE"})
["SELECT * FROM [user] WHERE `id` IS NULL FOR UPDATE"])))
(testing "by where clause"
(is (= (builder/for-query
:user
["id = ? and opt is null" 9]
{:table-fn sql-server :column-fn mysql :order-by [:a [:b :desc]]})
[(str "SELECT * FROM [user] WHERE id = ? and opt is null"
" ORDER BY `a`, `b` DESC") 9])))
(testing "by :all"
(is (= (builder/for-query
:user
:all
{:table-fn sql-server :column-fn mysql :order-by [:a [:b :desc]]})
["SELECT * FROM [user] ORDER BY `a`, `b` DESC"])))
(testing "top N"
(is (= (builder/for-query
:user
{:id 9}
{:table-fn sql-server :column-fn mysql :order-by [:a [:b :desc]]
:top 42})
["SELECT TOP ? * FROM [user] WHERE `id` = ? ORDER BY `a`, `b` DESC"
42 9])))
(testing "limit"
(testing "without offset"
(is (= (builder/for-query
:user
{:id 9}
{:table-fn sql-server :column-fn mysql :order-by [:a [:b :desc]]
:limit 42})
[(str "SELECT * FROM [user] WHERE `id` = ?"
" ORDER BY `a`, `b` DESC LIMIT ?")
9 42])))
(testing "with offset"
(is (= (builder/for-query
:user
{:id 9}
{:table-fn sql-server :column-fn mysql :order-by [:a [:b :desc]]
:limit 42 :offset 13})
[(str "SELECT * FROM [user] WHERE `id` = ?"
" ORDER BY `a`, `b` DESC LIMIT ? OFFSET ?")
9 42 13]))))
(testing "offset"
(testing "without fetch"
(is (= (builder/for-query
:user
{:id 9}
{:table-fn sql-server :column-fn mysql :order-by [:a [:b :desc]]
:offset 13})
[(str "SELECT * FROM [user] WHERE `id` = ?"
" ORDER BY `a`, `b` DESC OFFSET ? ROWS")
9 13])))
(testing "with fetch"
(is (= (builder/for-query
:user
{:id 9}
{:table-fn sql-server :column-fn mysql :order-by [:a [:b :desc]]
:offset 13 :fetch 42})
[(str "SELECT * FROM [user] WHERE `id` = ?"
" ORDER BY `a`, `b` DESC OFFSET ? ROWS FETCH NEXT ? ROWS ONLY")
9 13 42])))))
seancorfield/next-jdbc
(ns next.jdbc.sql.builder-test
"Tests for the SQL string building functions in next.jdbc.sql.builder."
(:require [clojure.test :refer [deftest is testing]]
[next.jdbc.quoted :refer [mysql sql-server]]
[next.jdbc.sql.builder :as builder]))
(deftest test-for-query
(testing "by example"
(is (= (builder/for-query
:user
{:id 9}
{:table-fn sql-server :column-fn mysql :order-by [:a [:b :desc]]})
["SELECT * FROM [user] WHERE `id` = ? ORDER BY `a`, `b` DESC" 9]))
(is (= (builder/for-query :user {:id nil} {:table-fn sql-server :column-fn mysql})
["SELECT * FROM [user] WHERE `id` IS NULL"]))
(is (= (builder/for-query :user
{:id nil}
{:table-fn sql-server :column-fn mysql
:suffix "FOR UPDATE"})
["SELECT * FROM [user] WHERE `id` IS NULL FOR UPDATE"])))
(testing "by where clause"
(is (= (builder/for-query
:user
["id = ? and opt is null" 9]
{:table-fn sql-server :column-fn mysql :order-by [:a [:b :desc]]})
[(str "SELECT * FROM [user] WHERE id = ? and opt is null"
" ORDER BY `a`, `b` DESC") 9])))
(testing "by :all"
(is (= (builder/for-query
:user
:all
{:table-fn sql-server :column-fn mysql :order-by [:a [:b :desc]]})
["SELECT * FROM [user] ORDER BY `a`, `b` DESC"])))
(testing "top N"
(is (= (builder/for-query
:user
{:id 9}
{:table-fn sql-server :column-fn mysql :order-by [:a [:b :desc]]
:top 42})
["SELECT TOP ? * FROM [user] WHERE `id` = ? ORDER BY `a`, `b` DESC"
42 9])))
(testing "limit"
(testing "without offset"
(is (= (builder/for-query
:user
{:id 9}
{:table-fn sql-server :column-fn mysql :order-by [:a [:b :desc]]
:limit 42})
[(str "SELECT * FROM [user] WHERE `id` = ?"
" ORDER BY `a`, `b` DESC LIMIT ?")
9 42])))
(testing "with offset"
(is (= (builder/for-query
:user
{:id 9}
{:table-fn sql-server :column-fn mysql :order-by [:a [:b :desc]]
:limit 42 :offset 13})
[(str "SELECT * FROM [user] WHERE `id` = ?"
" ORDER BY `a`, `b` DESC LIMIT ? OFFSET ?")
9 42 13]))))
(testing "offset"
(testing "without fetch"
(is (= (builder/for-query
:user
{:id 9}
{:table-fn sql-server :column-fn mysql :order-by [:a [:b :desc]]
:offset 13})
[(str "SELECT * FROM [user] WHERE `id` = ?"
" ORDER BY `a`, `b` DESC OFFSET ? ROWS")
9 13])))
(testing "with fetch"
(is (= (builder/for-query
:user
{:id 9}
{:table-fn sql-server :column-fn mysql :order-by [:a [:b :desc]]
:offset 13 :fetch 42})
[(str "SELECT * FROM [user] WHERE `id` = ?"
" ORDER BY `a`, `b` DESC OFFSET ? ROWS FETCH NEXT ? ROWS ONLY")
9 13 42])))))