使用带有 N 查询的“USING”子句的雄辩连接
我正在使用Slim Framework和Illuminate Database。
我想用clausa进行查询。假设给定的Sakila数据库。图:JOIN
USING
如何在雄辩的模型中用USE子句(而不是ON)连接?
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
我想要的是一个急切的加载与 EXACT 1 查询。API 中描述的雄辩关系的使用不符合我的期望,因为任何预先的关系都使用 N+1 查询。我想让它减少数据库的IO。
电影演员型号:
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");
//});
}
}
因此,在控制器中,我可以获得如下数据:
$data = FilmActor::allJoin()
->limit(100)
->get();`
但是有一个缺点,如果我需要添加额外的行为(如或)。where
order
$data = FilmActor::allJoin()
->where("film.film_id","1")
->orderBy("film_actor.actor_id")
->limit(100)
->get();`
我需要传递表名以避免不明确的字段。不好。所以我想进一步使用,我可以做
$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();`