Back
using (clj)
(source)function
(using & args)
Accepts similar arguments to `select` as part of
a SQL `USING` clause.
Examples
honeysql
(ns honey.sql-test
(:refer-clojure :exclude [format])
(:require [clojure.string :as str]
[clojure.test :refer [deftest is testing]]
[honey.sql :as sut :refer [format]]
[honey.sql.helpers :as h])
#?(:clj (:import (clojure.lang ExceptionInfo))))
(deftest delete-using
(is (= ["DELETE FROM films USING producers WHERE (producer_id = producers.id) AND (producers.name = ?)" "foo"]
(-> {:delete-from :films
:using [:producers]
:where [:and
[:= :producer_id :producers.id]
[:= :producers.name "foo"]]}
(format)))))
;; Make sure if Locale is Turkish we're not generating queries like İNNER JOIN (dot over the I) because
;; `string/upper-case` is converting things to upper-case using the default Locale. Generated query should be the same
;; regardless of system Locale. See #236
#?(:clj
(deftest statements-generated-correctly-with-turkish-locale
(let [format-with-locale (fn [^String language-tag]
(let [original-locale (java.util.Locale/getDefault)]
(try
(java.util.Locale/setDefault (java.util.Locale/forLanguageTag language-tag))
(format {:select [:t2.name]
:from [[:table1 :t1]]
:join [[:table2 :t2] [:= :t1.fk :t2.id]]
:where [:= :t1.id 1]})
(finally
(java.util.Locale/setDefault original-locale)))))]
(is (= (format-with-locale "en")
(format-with-locale "tr"))))))
(deftest join-without-on-using
;; essentially issue 326
(testing "join does not need on or using"
(is (= ["SELECT foo FROM bar INNER JOIN quux"]
(format {:select :foo
:from :bar
:join [:quux]}))))
(testing "join on select with parameters"
(is (= ["SELECT foo FROM bar INNER JOIN (SELECT a FROM b WHERE id = ?) WHERE id = ?" 123 456]
(format {:select :foo
:from :bar
:join [{:select :a :from :b :where [:= :id 123]}]
:where [:= :id 456]})))
(is (= ["SELECT foo FROM bar INNER JOIN (SELECT a FROM b WHERE id = ?) AS x WHERE id = ?" 123 456]
(format {:select :foo
:from :bar
:join [[{:select :a :from :b :where [:= :id 123]} :x]]
:where [:= :id 456]})))
(is (= ["SELECT foo FROM bar INNER JOIN (SELECT a FROM b WHERE id = ?) AS x ON y WHERE id = ?" 123 456]
(format {:select :foo
:from :bar
:join [[{:select :a :from :b :where [:= :id 123]} :x] :y]
:where [:= :id 456]})))))
(deftest issue-483-join
(testing "single nested join"
(is (= ["SELECT * FROM tbl1 LEFT JOIN (tbl2 INNER JOIN tbl3 USING (common_column)) ON (tbl2.col2 = tbl1.col2) AND (tbl3.col3 = tbl1.col3)"]
(-> {:select :*
:from :tbl1
:left-join [[[:join :tbl2 {:join [:tbl3 [:using [:common_column]]]}]]
[:and
[:= :tbl2.col2 :tbl1.col2]
[:= :tbl3.col3 :tbl1.col3]]]}
(sut/format)))))
(testing "multiple nested join"
(is (= ["SELECT * FROM tbl1 LEFT JOIN (tbl2 INNER JOIN tbl3 USING (common_column) RIGHT JOIN tbl4 USING (id)) ON (tbl2.col2 = tbl1.col2) AND (tbl3.col3 = tbl1.col3)"]
(-> {:select :*
:from :tbl1
:left-join [[[:join :tbl2
{:join [:tbl3 [:using [:common_column]]]}
{:right-join [:tbl4 [:using :id]]}]]
[:and
[:= :tbl2.col2 :tbl1.col2]
[:= :tbl3.col3 :tbl1.col3]]]}
(sut/format)))))
(testing "special syntax example"
(is (= ["INNER JOIN (tbl1 LEFT JOIN tbl2 USING (id))"]
(sut/format {:join [[[:join :tbl1 {:left-join [:tbl2 [:using :id]]}]]]})))))
honeysql
(ns honey.sql.helpers-test
(:refer-clojure :exclude [filter for group-by partition-by set update])
(:require [clojure.test :refer [deftest is testing]]
[honey.sql :as sql]
[honey.sql.helpers :as h
:refer [add-column add-index alter-table columns create-table create-table-as create-view
create-materialized-view drop-view drop-materialized-view
create-index
bulk-collect-into
cross-join do-update-set drop-column drop-index drop-table
filter from full-join
group-by having insert-into
join-by join lateral left-join limit offset on-conflict
on-duplicate-key-update
order-by over partition-by refresh-materialized-view
rename-column rename-table returning right-join
select select-distinct select-top select-distinct-top
values where window with with-columns
with-data within-group]]))
(deftest join-using-test
(testing "nil join"
(is (= ["SELECT * FROM foo INNER JOIN x USING (id) INNER JOIN y USING (foo, bar)"]
(-> (select :*)
(from :foo)
(join :x [:using :id] :y [:using :foo :bar])
sql/format)))))
(deftest issue-293-insert-into-helpers
;; and the same set of tests using the helper functions instead:
(is (= (sql/format (-> (insert-into :transport)
(values [[1 "Car"] [2 "Boat"] [3 "Bike"]])))
["INSERT INTO transport VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
(is (= (sql/format (-> (insert-into :transport)
(columns :id :name)
(values [[1 "Car"] [2 "Boat"] [3 "Bike"]])))
["INSERT INTO transport (id, name) VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
;; with an alias:
(is (= (sql/format (-> (insert-into :transport :t)
(values [[1 "Car"] [2 "Boat"] [3 "Bike"]])))
["INSERT INTO transport AS t VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
(is (= (sql/format (-> (insert-into :transport :t)
(columns :id :name)
(values [[1 "Car"] [2 "Boat"] [3 "Bike"]])))
["INSERT INTO transport AS t (id, name) VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
;; second case -- table specifier and columns:
(is (= (sql/format (-> (insert-into :transport [:id :name])
(values [[1 "Car"] [2 "Boat"] [3 "Bike"]])))
["INSERT INTO transport (id, name) VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
;; with an alias:
(is (= (sql/format (-> (insert-into [:transport :t] [:id :name])
(values [[1 "Car"] [2 "Boat"] [3 "Bike"]])))
["INSERT INTO transport AS t (id, name) VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
;; third case -- table/column specifier and query:
(is (= (sql/format (insert-into :transport '{select (id, name) from (cars)}))
["INSERT INTO transport SELECT id, name FROM cars"]))
;; with columns:
(is (= (sql/format (insert-into [:transport [:id :name]] '{select (*) from (cars)}))
["INSERT INTO transport (id, name) SELECT * FROM cars"]))
;; with an alias:
(is (= (sql/format (insert-into '(transport t) '{select (id, name) from (cars)}))
["INSERT INTO transport AS t SELECT id, name FROM cars"]))
;; with columns:
(is (= (sql/format (insert-into '(transport (id, name)) '{select (*) from (cars)}))
["INSERT INTO transport (id, name) SELECT * FROM cars"]))
;; with an alias and columns:
(is (= (sql/format (insert-into ['(transport t) '(id, name)] '{select (*) from (cars)}))
["INSERT INTO transport AS t (id, name) SELECT * FROM cars"]))
;; three arguments with columns:
(is (= (sql/format (insert-into :transport [:id :name] '{select (*) from (cars)}))
["INSERT INTO transport (id, name) SELECT * FROM cars"]))
;; three arguments with an alias and columns:
(is (= (sql/format (insert-into '(transport t) '(id, name) '{select (*) from (cars)}))
["INSERT INTO transport AS t (id, name) SELECT * FROM cars"])))