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 clause. 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 $incrementing = 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();`

If you want only 1 query don't use eloquent, use query builder instead. — Felippe Duarte, Jan 19 '18 at 19:39
@FelippeDuarte could you post an answer instead ? i can't find something like `$join->using('users.id')` https://laravel.com/docs/5.4/queries#joins https://laravel.com/api/5.0/Illuminate/Database/Query/JoinClause.html — Egy Mohammad Erdin, Jan 19 '18 at 19:50
@bishop : Thanks for your effort. Well, i'll keep this question open, in case there will be an actual answer for iluminate future version. — Egy Mohammad Erdin, Jan 23 '18 at 05:01
"any eager relation use N+1 query" - What is "N"? And where did you read/hear that? — Paul Spiegel, Jan 28 '18 at 00:17
@PaulSpiegel : https://laravel.com/docs/5.4/eloquent-relationships#eager-loading at conclusion, it reduce the operation to just 2 queries — Egy Mohammad Erdin, Jan 28 '18 at 09:35

score 1 · Answer 1 · edited Aug 08 '21 at 09:45

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.

score 0 · Answer 2 · answered Feb 09 '18 at 01:48

You can try find in code is possible to make USING JOIN, or add some proxy dictionary:

$kat_dict =  ["film_id" => "film.film_id", "title"=> 'title', "first_name" => 'first_name', "last_name" => 'last_name'];

$kat = $kat_dict[$request->getParam("kat","first_name")];

BTW: better way is using function like Arr::get($arr, $index, $default) (See at code example)

score 0 · Answer 3 · answered Jun 18 '21 at 18:02

You can simply call raw query via select

$query = "SELECT 
    film_id,title,first_name,last_name 
FROM 
    film_actor 
INNER join film USING(film_id)
INNER join actor USING(actor_id)
WHERE 
    film.film_id = :filmId 
ORDER BY film_actor.actor_id
LIMIT 0, 100";

$data = DB::select($query, [
    'filmId' => 1
]);
// or like this, if not using default connection
/**
$data = DB::connection('test')->select($query, [
    'filmId' => 1
]);
*/

Eloquent join using "USING" clause with N query

3 Answers3