Public Vars

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"])))