Public Vars

Back

full-join (clj)

(source)

function

(full-join & args)
Accepts one or more FULL JOIN expressions. Each join expression is specified as a pair of arguments, where the first one is the table name (or a pair of table and alias) and the second one is the join condition: (full-join :table [:= :foo.id :table.foo_id]) (full-join [:table :t] [:= :foo.id :t.foo_id]) Produces: FULL JOIN table ON foo.id = table.foo_id FULL JOIN table AS t ON foo.id = t.foo_id

Examples

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 test-select
  (testing "large helper expression"
    (let [m1 (-> (with [:cte (-> (select :*)
                                 (from :example)
                                 (where [:= :example-column 0]))])
                 (select-distinct :f.* :b.baz :c.quux [:b.bla "bla-bla"]
                                  :%now [[:raw "@x := 10"]])
                 (from [:foo :f] [:baz :b])
                 (join :draq [:= :f.b :draq.x])
                 (left-join [:clod :c] [:= :f.a :c.d])
                 (right-join :bock [:= :bock.z :c.e])
                 (full-join :beck [:= :beck.x :c.y])
                 (where [:or
                         [:and [:= :f.a "bort"] [:not= :b.baz :?param1]]
                         [:and [:< 1 2] [:< 2 3]]
                         [:in :f.e [1 [:param :param2] 3]]
                         [:between :f.e 10 20]])
                 (group-by :f.a)
                 (having [:< 0 :f.e])
                 (order-by [:b.baz :desc] :c.quux [:f.a :nulls-first])
                 (limit 50)
                 (offset 10))
          m2 {:with [[:cte {:select [:*]
                            :from [:example]
                            :where [:= :example-column 0]}]]
              :select-distinct [:f.* :b.baz :c.quux [:b.bla "bla-bla"]
                                :%now [[:raw "@x := 10"]]]
              :from [[:foo :f] [:baz :b]]
              :join [:draq [:= :f.b :draq.x]]
              :left-join [[:clod :c] [:= :f.a :c.d]]
              :right-join [:bock [:= :bock.z :c.e]]
              :full-join [:beck [:= :beck.x :c.y]]
              :where [:or
                      [:and [:= :f.a "bort"] [:not= :b.baz :?param1]]
                      [:and [:< 1 2] [:< 2 3]]
                      [:in :f.e [1 [:param :param2] 3]]
                      [:between :f.e 10 20]]
              :group-by [:f.a]
              :having [:< 0 :f.e]
              :order-by [[:b.baz :desc] :c.quux [:f.a :nulls-first]]
              :limit 50
              :offset 10}]
      (testing "Various construction methods are consistent"
        (is (= m1 m2)))
      (testing "SQL data formats correctly"
        (is (= ["WITH cte AS (SELECT * FROM example WHERE example_column = ?) SELECT DISTINCT f.*, b.baz, c.quux, b.bla AS \"bla-bla\", NOW(), @x := 10 FROM foo AS f, baz AS b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod AS c ON f.a = c.d RIGHT JOIN bock ON bock.z = c.e FULL JOIN beck ON beck.x = c.y WHERE ((f.a = ?) AND (b.baz <> ?)) OR ((? < ?) AND (? < ?)) OR (f.e IN (?, ?, ?)) OR f.e BETWEEN ? AND ? GROUP BY f.a HAVING ? < f.e ORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRST LIMIT ? OFFSET ?"
                0 "bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10]
               (sql/format m1 {:params {:param1 "gabba" :param2 2}}))))
      #?(:clj (testing "SQL data prints and reads correctly"
                (is (= m1 (read-string (pr-str m1))))))
      #_(testing "SQL data formats correctly with alternate param naming"
          (is (= (sql/format m1 {:params {:param1 "gabba" :param2 2}})
                 ["WITH cte AS (SELECT * FROM example WHERE example_column = $1) SELECT DISTINCT f.*, b.baz, c.quux, b.bla \"bla-bla\", NOW(), @x := 10 FROM foo AS f, baz AS b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod AS c ON f.a = c.d RIGHT JOIN bock ON bock.z = c.e FULL JOIN beck ON beck.x = c.y WHERE ((f.a = $2) AND (b.baz <> $3)) OR (($4 < $5) AND ($6 < $7)) OR (f.e IN ($8, $9, $10)) OR f.e BETWEEN $11 AND $12 GROUP BY f.a HAVING $13 < f.e ORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRST LIMIT $14 OFFSET $15"
                  0 "bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10])))
      (testing "Locking"
        (is (= ["WITH cte AS (SELECT * FROM example WHERE example_column = ?) SELECT DISTINCT f.*, b.baz, c.quux, b.bla AS `bla-bla`, NOW(), @x := 10 FROM foo AS f, baz AS b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod AS c ON f.a = c.d RIGHT JOIN bock ON bock.z = c.e FULL JOIN beck ON beck.x = c.y WHERE ((f.a = ?) AND (b.baz <> ?)) OR ((? < ?) AND (? < ?)) OR (f.e IN (?, ?, ?)) OR f.e BETWEEN ? AND ? GROUP BY f.a HAVING ? < f.e ORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRST LIMIT ? OFFSET ? LOCK IN SHARE MODE"
                0 "bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10]
               (sql/format (assoc m1 :lock [:in-share-mode])
                           {:params {:param1 "gabba" :param2 2}
                            ;; to enable :lock
                            :dialect :mysql :quoted false}))))))
  (testing "large helper expression with simplified where"
    (let [m1 (-> (with [:cte (-> (select :*)
                                 (from :example)
                                 (where := :example-column 0))])
                 (select-distinct :f.* :b.baz :c.quux [:b.bla "bla-bla"]
                                  :%now [[:raw "@x := 10"]])
                 (from [:foo :f] [:baz :b])
                 (join :draq [:= :f.b :draq.x])
                 (left-join [:clod :c] [:= :f.a :c.d])
                 (right-join :bock [:= :bock.z :c.e])
                 (full-join :beck [:= :beck.x :c.y])
                 (where :or
                        [:and [:= :f.a "bort"] [:not= :b.baz :?param1]]
                        [:and [:< 1 2] [:< 2 3]]
                        [:in :f.e [1 [:param :param2] 3]]
                        [:between :f.e 10 20])
                 (group-by :f.a)
                 (having :< 0 :f.e)
                 (order-by [:b.baz :desc] :c.quux [:f.a :nulls-first])
                 (limit 50)
                 (offset 10))
          m2 {:with [[:cte {:select [:*]
                            :from [:example]
                            :where [:= :example-column 0]}]]
              :select-distinct [:f.* :b.baz :c.quux [:b.bla "bla-bla"]
                                :%now [[:raw "@x := 10"]]]
              :from [[:foo :f] [:baz :b]]
              :join [:draq [:= :f.b :draq.x]]
              :left-join [[:clod :c] [:= :f.a :c.d]]
              :right-join [:bock [:= :bock.z :c.e]]
              :full-join [:beck [:= :beck.x :c.y]]
              :where [:or
                      [:and [:= :f.a "bort"] [:not= :b.baz :?param1]]
                      [:and [:< 1 2] [:< 2 3]]
                      [:in :f.e [1 [:param :param2] 3]]
                      [:between :f.e 10 20]]
              :group-by [:f.a]
              :having [:< 0 :f.e]
              :order-by [[:b.baz :desc] :c.quux [:f.a :nulls-first]]
              :limit 50
              :offset 10}]
      (testing "Various construction methods are consistent"
        (is (= m1 m2)))
      (testing "SQL data formats correctly"
        (is (= ["WITH cte AS (SELECT * FROM example WHERE example_column = ?) SELECT DISTINCT f.*, b.baz, c.quux, b.bla AS \"bla-bla\", NOW(), @x := 10 FROM foo AS f, baz AS b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod AS c ON f.a = c.d RIGHT JOIN bock ON bock.z = c.e FULL JOIN beck ON beck.x = c.y WHERE ((f.a = ?) AND (b.baz <> ?)) OR ((? < ?) AND (? < ?)) OR (f.e IN (?, ?, ?)) OR f.e BETWEEN ? AND ? GROUP BY f.a HAVING ? < f.e ORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRST LIMIT ? OFFSET ?"
                0 "bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10]
               (sql/format m1 {:params {:param1 "gabba" :param2 2}}))))
      #?(:clj (testing "SQL data prints and reads correctly"
                (is (= m1 (read-string (pr-str m1))))))
      #_(testing "SQL data formats correctly with alternate param naming"
          (is (= (sql/format m1 {:params {:param1 "gabba" :param2 2}})
                 ["WITH cte AS (SELECT * FROM example WHERE example_column = $1) SELECT DISTINCT f.*, b.baz, c.quux, b.bla \"bla-bla\", NOW(), @x := 10 FROM foo AS f, baz AS b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod AS c ON f.a = c.d RIGHT JOIN bock ON bock.z = c.e FULL JOIN beck ON beck.x = c.y WHERE ((f.a = $2) AND (b.baz <> $3)) OR (($4 < $5) AND ($6 < $7)) OR (f.e IN ($8, $9, $10)) OR f.e BETWEEN $11 AND $12 GROUP BY f.a HAVING $13 < f.e ORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRST LIMIT $14 OFFSET $15"
                  0 "bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10])))
      (testing "Locking"
        (is (= ["WITH cte AS (SELECT * FROM example WHERE example_column = ?) SELECT DISTINCT f.*, b.baz, c.quux, b.bla AS `bla-bla`, NOW(), @x := 10 FROM foo AS f, baz AS b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod AS c ON f.a = c.d RIGHT JOIN bock ON bock.z = c.e FULL JOIN beck ON beck.x = c.y WHERE ((f.a = ?) AND (b.baz <> ?)) OR ((? < ?) AND (? < ?)) OR (f.e IN (?, ?, ?)) OR f.e BETWEEN ? AND ? GROUP BY f.a HAVING ? < f.e ORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRST LIMIT ? OFFSET ? LOCK IN SHARE MODE"
                0 "bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10]
               (sql/format (assoc m1 :lock [:in-share-mode])
                           {:params {:param1 "gabba" :param2 2}
                            ;; to enable :lock
                            :dialect :mysql :quoted false})))))))

(deftest join-by-test
  (testing "Natural JOIN orders"
    (is (= ["SELECT * FROM foo INNER JOIN draq ON f.b = draq.x LEFT JOIN clod AS c ON f.a = c.d RIGHT JOIN bock ON bock.z = c.e FULL JOIN beck ON beck.x = c.y"]
           (sql/format {:select [:*] :from [:foo]
                        :full-join  [:beck [:= :beck.x :c.y]]
                        :right-join [:bock [:= :bock.z :c.e]]
                        :left-join  [[:clod :c] [:= :f.a :c.d]]
                        :join       [:draq [:= :f.b :draq.x]]}))))
  (testing "Specific JOIN orders"
    (is (= ["SELECT * FROM foo FULL JOIN beck ON beck.x = c.y RIGHT JOIN bock ON bock.z = c.e LEFT JOIN clod AS c ON f.a = c.d INNER JOIN draq ON f.b = draq.x"]
           (sql/format {:select [:*] :from [:foo]
                        :join-by [:full  [:beck [:= :beck.x :c.y]]
                                  :right [:bock [:= :bock.z :c.e]]
                                  :left  [[:clod :c] [:= :f.a :c.d]]
                                  :join  [:draq [:= :f.b :draq.x]]]})))
    (is (= ["SELECT * FROM foo FULL JOIN beck ON beck.x = c.y RIGHT JOIN bock ON bock.z = c.e LEFT JOIN clod AS c ON f.a = c.d INNER JOIN draq ON f.b = draq.x"]
           (-> (select :*)
               (from :foo)
               (join-by :full-join  [:beck [:= :beck.x :c.y]]
                        :right-join [:bock [:= :bock.z :c.e]]
                        :left-join  [[:clod :c] [:= :f.a :c.d]]
                        :inner-join [:draq [:= :f.b :draq.x]])
               (sql/format)))))
  (testing "Specific JOIN orders with join clauses"
    (is (= ["SELECT * FROM foo FULL JOIN beck ON beck.x = c.y RIGHT JOIN bock ON bock.z = c.e LEFT JOIN clod AS c ON f.a = c.d INNER JOIN draq ON f.b = draq.x"]
           (sql/format {:select [:*] :from [:foo]
                        :join-by [{:full-join [:beck [:= :beck.x :c.y]]}
                                  {:right-join [:bock [:= :bock.z :c.e]]}
                                  {:left-join [[:clod :c] [:= :f.a :c.d]]}
                                  {:join [:draq [:= :f.b :draq.x]]}]})))
    (is (= ["SELECT * FROM foo FULL JOIN beck ON beck.x = c.y RIGHT JOIN bock ON bock.z = c.e LEFT JOIN clod AS c ON f.a = c.d INNER JOIN draq ON f.b = draq.x"]
           (-> (select :*)
               (from :foo)
               (join-by (full-join :beck [:= :beck.x :c.y])
                        (right-join :bock [:= :bock.z :c.e])
                        (left-join [:clod :c] [:= :f.a :c.d])
                        (join :draq [:= :f.b :draq.x]))
               (sql/format))))))