How to generate a USING clause in JOIN queries with Laravel's Query Builder?

Question

PostgreSQL supports USING clauses in JOIN statements, and I want to use it to simplify my query. For example:

 select * from a join b using (x, y, z) where ... order by x;

How do I express this with Laravel's Query Builder or Eloquent?

DB::table('a')
    ->select('*')
    ->join('b', function($join))
    ->where(...)
    ->orderBy('x');

Note that this question is different from https://stackoverflow.com/questions/31465918/how-to-generate-a-natural-join-query-with-laravels-query-builder where I was asking about NATURAL JOIN, not JOIN USING.

Also, I'm aware that I can have almost the same effect with multiple $join->on(...) statements, but then I have duplicate columns and must always qualify columns that are in both a and b. That is, I'd have to write 'a.x' instead of 'x' even though it doesn't matter, because the join query ensures that both are equal anyway. The USING clause solves this problem neatly, by generating only a single result column x, knowing there is only this one value.

Answer 1

So, the answer is - it's impossible.

I continued this question on Laravel 5, and it's still impossible.

My question - Laravel 5. Using the USING operator

My pull request - https://github.com/laravel/framework/pull/12773

Answer 2

This is an old question but it's very much possible with raw expressions. Here is what it would look like for the question's given example:

DB::table('a')
    ->join(DB::raw('b using (x, y, z)'), fn (JoinClause $j) => $j)
    ->where(...)
    ->orderBy('x')
    ->get()

DB::raw() is needed or else the whole thing gets escaped and your database will complain about not finding the "'b using (x,y,z)'" table if it does not complain about syntax first.

You can add your own escaping too if you want.

DB::raw('`b` using (`x`, `y`, `z`)')

---

I've come to prefer the following format.

->join(DB::raw('b using (x, y, z)'), function () {})