Back
join-by (clj)
(source)function
(join-by & args)
Accepts a sequence of join clauses to be generated
in a specific order.
(-> (select :*)
(from :foo)
(join-by :left [:bar [:= :foo.id :bar.id]]
:join [:quux [:= :bar.qid :quux.id]]))
This produces a LEFT JOIN followed by an INNER JOIN
even though the 'natural' order for `left-join` and
`join` would be to generate the INNER JOIN first,
followed by the LEFT JOIN.
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))))))