Eloquent join using USING clause with N query

Question

I am using Slim Framework with an illustration of a database .
I want to make a JOIN request using USING clausa. Say give Sakila . Diagram:

How to make a connection to a USING clause (not included) in an 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 impatient download with an EXACT 1 query . Using the eloquent relationships described in the API does not meet my expectation, since any impatient attitude uses an N + 1 query, I want to make this less IO for the 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 data like

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

But there is con if I need to add additional behavior (e.g. where or order ).

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

I need to pass a table name to avoid an ambiguous field. Not good . So I want for future reference, 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

You can try to find in the code you can make a USING JOIN or add a 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: the best way is to use a function like Arr :: get ($ arr, $ index, $ default) (see code example)