Nesting JOINs

[SebAlbert]

(I'm rather new to Clojure altogether and to honeysql in particular, so please bear with me if I missed something obvious.)

In advanced SQL usage, there are times when I want to nest JOINs, for example:

SELECT *
FROM tbl1
LEFT JOIN (
    tbl2 JOIN tbl3 USING (common_column)
    ) ON tbl2.col2 = tbl1.col2 AND tbl3.col3 = tbl1.col3

Is there a way to do this? I have started using :join-by as it seems it's the only way to control the "order" of JOINs, but inside the list I hand to :join-by, it won't accept a nested :join-by nor a :nest as join-type, and inside a join's "arguments" it won't recognize these as being another join rather than a table called "nest" or "join" etc.

[seancorfield]

I've never seen SQL syntax like -- can you link to database vendor documentation that shows that?

You don't show what HoneySQL DSL you've actually tried, but I suspect :nest will help you here.

[SebAlbert]

Please see https://www.postgresql.org/docs/15/sql-select.html with the following under join_type:

A JOIN clause combines two FROM items, which for convenience we will refer to as “tables”, though in reality they can be any type of FROM item. Use parentheses if necessary to determine the order of nesting.

I am playing around in the web editor at john.shaffe.rs and managed to get parantheses with [[[, but inside them I would only get a function call:

:join-by [:left-join [:draq [:= :f.b :draq.x]
                       :eldr [:= :f.e :eldr.t]]
           :left-join [[[:nest [***]
                         ]] [:= :f.a :c.d]]
           :right-join [:bock [:= :bock.z :c.e]]]

with *** marking the spot where I absolutely don't know what to write.

[SebAlbert]

I think my basic conceptual problem with understanding honeysql join syntax is that honeysql treats "joins" as individual clauses, whereas I would rather see them as operators within the "from" clause.

[seancorfield]

I'll have to give this some thought. I don't think, right now, this particular syntax is achievable in HoneySQL.

It supports a sequence of JOINs in order and it supports USING -- but there's currently no way to get that nested syntax.

[SebAlbert]

Thanks! When considering, note that with Postgres it is even possible to give an alias to a parenthesized sub-join after the closing parenthesis (which makes referencing the individual tables from outside the parentheses illegal, though).

[seancorfield]

I think the easiest solution to this is to provide function-like "special syntax" that allows expressions to be JOINed like that.

Here's a proposed syntax (overloading the :join keyword to introduce the nested JOIN):

user=> (sql/format {:left-join [[[:join :bar {:outer-join [:foo [:using :id]]}]] [:and [:= :tbl1.col2 :bar.col2] [:= :tbl1.col3 :foo.col3]]]})
["LEFT JOIN (bar OUTER JOIN foo USING (id)) ON (tbl1.col2 = bar.col2) AND (tbl1.col3 = foo.col3)"]
user=> (sql/format {:left-join [[[:join :bar {:outer-join [:foo [:using :id]]}] :wibble] [:and [:= :tbl1.col2 :bar.col2] [:= :tbl1.col3 :foo.col3]]]})
["LEFT JOIN (bar OUTER JOIN foo USING (id)) AS wibble ON (tbl1.col2 = bar.col2) AND (tbl1.col3 = foo.col3)"]

That second example is just to show how an alias would factor in here (which is already supported syntax in HoneySQL, even tho' it makes the overall result illegal SQL).

Thoughts?

[SebAlbert]

How would that :join function-like call nest inside itself? I.e., in your example, could I easily replace any of :bar or {:outer-join [:foo [:using :id]]} with yet another sub-"call" to the function-like :join?

Would the 'function-like' :join be a 'varargs function', so I could add a third (or fourth...) table inside the pair of parentheses it will generate, or would I rather extend the map in the second argument? In the latter case, how would I preserve ordering at that level?

[seancorfield]

Making it varargs and following that order of joins sounds sensible.

:join would be new function-like pseudo-syntax and could be used anywhere a function could be used, so it would automatically nest like functions.

[seancorfield]

develop has been updated with a variadic version of this new syntax, if you want to try it out.

I need to add tests and documentation but would like some feedback on it first.

[seancorfield]

If you don't want to use git deps to test this (or can't), there's an updated 2.4.9999-SNAPSHOT version on Clojars too.

[SebAlbert]

Thanks for the quick preview!
It takes a little getting used to, especially knowing "how many brackets" to open in which case/position, but I managed to nest on both sides and also in :from to my heart's content, like this:

(sql/format {:select [:a :b :c] :from [[[:join [:join :xx {:left-join [:yy [:= :abc :def]]}] {:inner-join [[[:join :sss {:inner-join [:ttt [:using :st]]}]] [:using :col1 :col2]]}]]]})

So, albeit a tad cumbersome (I don't have a better idea, though), it works for me, and I see that I will be able to produce this map structure from my own data structures recursively in order to produce SQL via honeysql, which is great.