Eloquent join using "USING" clause with N query

Question

I'm using Slim Framework with Illuminate Database.

I want to make JOIN query with USING clausa. Let's say given Sakila database. Diagram:

How to make join with USING clause (not ON) in eloquent model ?

SELECT film_id,title,first_name,last_name 
FROM film_actor 
INNER join film USING(film_id) -- notice 
INNER join actor USING(actor_id) -- notice 

What I want is an eager loading with EXACT 1 query. The use of eloquent relationships described in the API is not meeting my expectation, since any eager relation use N+1 query. I want to make it less IO to database.

FilmActor model :

class FilmActor extends Model
{
    protected $table = 'film_actor';
    protected $primaryKey = ["actor_id", "film_id"];
    protected $increamenting = false;
    protected $appends = ['full_name'];
    
    // i need to make it in Eloquent model way, so it easier to manipulate
    public function getFullNameAttribute()  
    {
        $fn = "";
        $fn .= isset($this->first_name) ? $this->first_name ." ": "";
        $fn .= isset($this->last_name) ? $this->last_name ." ": "";
        return $fn; 
    }

    public function allJoin()
    {
        // how to join with "USING" clause ?
        return self::select(["film.film_id","title","first_name","last_name"])
            ->join("film", "film_actor.film_id", '=', 'film.film_id')  
            ->join("actor", "film_actor.actor_id", '=', 'actor.actor_id');  

        //something like
        //return self::select("*")->joinUsing("film",["film_id"]);
        //or
        //return self::select("*")->join("film",function($join){
        //    $join->using("film_id");
        //});
    }
}

So, in the controller I can get the data like

$data = FilmActor::allJoin()  
        ->limit(100)  
        ->get();`  

But there's a con, if I need to add extra behavior (like where or order).

$data = FilmActor::allJoin()
        ->where("film.film_id","1")   
        ->orderBy("film_actor.actor_id")  
        ->limit(100)  
        ->get();`  

I need to pass table name to avoid ambiguous field. Not good. So I want for further use, I can do

$kat = $request->getParam("kat","first_name");  
// ["film_id", "title", "first_name", "last_name"]  
// from combobox html  
// adding "film.film_id" to combo is not an option  
// passing table name to html ?? big NO

$search = $request->getParam("search","");
$order = $request->getParam("order","");
$data = FilmActor::allJoin()
        ->where($kat,"like","%$search%")   
        ->orderBy($order)  
        ->limit(100)  
        ->get();`  

Answer 1

In Eloquent (and I think that was already available in 2018) the feature is not named using but with and should give something like :

    ForumActor::with(['film', 'actor'])->get();

Of course this has to be adapted to your cases, you may even nest relationships :

   ForumActor::with('actor.contacts')->get();

For instance.

Have a look : https://laravel.com/docs/8.x/eloquent-relationships#eager-loading Even though it's labelled as "Eager Loading" (which is great btw) it also works without eager loading, and moreover, when foreign keys are properly set (e.g. with migrations), then it uses only ONE query, so that keeps away the N+1 Problem.