过滤雄辩 [英] Filter eloquent
问题描述
我在项目中使用laravel,我想创建一个端点,该端点过滤并搜索电影
模型,并返回与所选过滤器选项匹配的电影的集合,被用户选择的选项,位置和年龄等级
。
我创建了一个查询,查询的最初方式以symfony的形式加入表格(希望它是正确的),但是不确定如何将其调整为更Laravel或雄辩的语言。
电影模型
公共功能location():BelongsTo
{
return $ this-> belongsTo(Location ::类);
}
公共功能options():BelongsToMany
{
return $ this-> belongsToMany(
Option :: class,
'film_options',
'film_id',
'option_id'
);
}
公共函数ageRatings():BelongsToMany
{
return $ this-> belongsToMany(
AgeRating :: class,
'film_age_ratings',
'film_id',
'age_rating_id'
);
}
查询原则
选择*
从电影f
加入f_id的film_options = fo.film_id
加入选项o o.id = fo.option_id
在f.id上加入film_age_ratings fa.id = fa.film_id
在a.id上加入age_ratings = fa.age_rating_id
在l.id = p上加入位置l .location_id
我如何在laravel中编写此查询?
要实现目标,您可以混合使用 when()和 whereHas():
$ films = Film
:: when($ request-> input ('first'),函数($ query,$ first){
$ query-> whereHas('opti ons,函数($ query)使用($ first){
$ query-> where(‘first’,$ first);
});
})
-> when($ request-> input('second'),function($ query,$ second){
$ query-> whereHas('options' ,函数($ query)使用($ second){
$ query-> where('second',$ second);
});
})
-> ; when($ request-> input('age'),function($ query,$ age){
$ query-> whereHas('ageRatings',function($ query)use($ age){
$ query-> where('age',$ age);
});
})
-> when($ request-> input('country '),函数($ query,$ country){
$ query-> whereHas('locations,函数($ query)use($ country){
$ query-> where(' country',$ country);
});
})
-> when($ request-> input('city'),function($ query,$ city){
$ query-> whereHas('locations',function($ query)use($ city){
$ query-> where('city',$ city);
} );
})
-> orderBy('updated_at','desc')
-> get();
基本上,上面的意思是,当您提交了非空值时,它将添加查询该关系的where子句
例如是 main
提交后,它将电影限制为只有以main为提交值的类别的电影。
此外,仅供参考,使用Eloquent时无需添加 from(...)
方法。 / p>
限制行数的一种简单方法是添加范围到您的电影
模型,例如
在您的电影
模型中:
公共功能scopeHasMain($ query,$ main)
{
$ query-> whereHas('options',function($ query)use($ first){
$ query-&where; 'first',$ first);
});
}
然后您的 when()
方法为:
时($ request-> input('first'),函数($ query,$ first){
$ query-> hasFirst($ first);
})
Im using laravel for a project i am wanting to create an endpoint which filters and searches a Film
model and returns a collection of films that match the chosen filter options which will be Options, Location and Age Rating
picked by the user.
I have created a query with how i would initially join the tables in symfony(hopefully its correct) but im unsure on how i adjust this to be more Laravel or eloquent.
Film Model
public function location(): BelongsTo
{
return $this->belongsTo(Location::class);
}
public function options(): BelongsToMany
{
return $this->belongsToMany(
Option::class,
'film_options',
'film_id',
'option_id'
);
}
public function ageRatings(): BelongsToMany
{
return $this->belongsToMany(
AgeRating::class,
'film_age_ratings',
'film_id',
'age_rating_id'
);
}
Query in Doctrine
Select *
From films f
Join film_options fo on f.id = fo.film_id
Join options o on o.id = fo.option_id
Join film_age_ratings fa on f.id = fa.film_id
Join age_ratings a on a.id = fa.age_rating_id
Join location l on l.id = p.location_id
How would i write this query within laravel?
To achieve what you're after you can use a mixture of when() and whereHas():
$films = Film
::when($request->input('first'), function ($query, $first) {
$query->whereHas('options', function ($query) use ($first) {
$query->where('first', $first);
});
})
->when($request->input('second'), function ($query, $second) {
$query->whereHas('options', function ($query) use ($second) {
$query->where('second', $second);
});
})
->when($request->input('age'), function ($query, $age) {
$query->whereHas('ageRatings', function ($query) use ($age) {
$query->where('age', $age);
});
})
->when($request->input('country'), function ($query, $country) {
$query->whereHas('locations', function ($query) use ($country) {
$query->where('country', $country);
});
})
->when($request->input('city'), function ($query, $city) {
$query->whereHas('locations', function ($query) use ($city) {
$query->where('city', $city);
});
})
->orderBy('updated_at', 'desc')
->get();
Essentially, what the above is doing is saying when you have a non-empty value submitted it will add a where clause to the query for that relationship
e.g. is main
is submitted it will limit the films to only ones that have categories when main is the submitted value.
Also, just an FYI, you don't need to add the from(...)
method when using Eloquent.
A simple way to limit the number of lines would be to add scopes to your Film
model e.g.
In your Film
model:
public function scopeHasMain($query, $main)
{
$query->whereHas('options', function ($query) use($first) {
$query->where('first', $first);
});
}
Then your when()
method would be:
when($request->input('first'), function ($query, $first) {
$query->hasFirst($first);
})
这篇关于过滤雄辩的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!