Public Vars

Back

inner-join (clj)

(source)

function

(inner-join & args)
An alternative name to `join`, this accepts one or more INNER 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: (inner-join :table [:= :foo.id :table.foo_id]) (inner-join [:table :t] [:= :foo.id :t.foo_id]) Produces: INNER JOIN table ON foo.id = table.foo_id INNER 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 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))))))