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();` 
0 comments:
Post a Comment