Reputation: 91
Hello I'm making a webapp with categories, pages and banners that can be displayed on pages based on categories selection. A banner should belongs to many categories so I created a pivot table to connect banners and categories.
The point is that I can easily select which banner to show using join but I wish to do it in a pure eloquent style.
My models are
Banners
class Banner extends Model
{
...
public function categories()
{
return $this->belongsToMany(Category::class, 'banner_category');
}
}
Categories
class Category extends Model
{
...
public function banners(){
return $this->hasMany(Banner::class, 'banner_category');
}
}
This query works fine, but it isn't so eloquent
$banners = \DB::table('banners')
->join('banner_category', 'banners.id', '=', 'banner_category.banner_id')
->where('banner_category.category_id',$category->id)
->where('banners.up_at','<=', $today)
->where('banners.down_at','>=', $today)
->where('visible', 1)
->get();
Based on previous research, I tried several approachs, which doesn't works including the following
$banners = \App\Banner::where('visible', 1)
->where('up_at','<=', $today)
->where('down_at','>=', $today)
->with(['categories' => function($query) use($category->id)
{
$query->where('category_id', '=', $category->id);
}
])
->get();
Any advice will be appreciated. Thanks!
Upvotes: 0
Views: 601
Reputation: 33216
The with
function will only filter the eager loaded results and not the main result set. The function you are looking for is whereHas
. This will only select the results that have a relation with a certain limitation.
$banners = \App\Banner::where('visible', 1)
->where('up_at','<=', $today)
->where('down_at','>=', $today)
->whereHas('categories', function($query) use ($category) {
$query->where('category_id', $category->id);
})
->get();
Upvotes: 2
Reputation: 950
Looks like your foreign key is banner_id, not banner_category. So your models should be edited as follow:
class Banner extends Model
{
...
public function categories()
{
return $this->belongsToMany(Category::class, 'banner_id');
}
}
class Category extends Model
{
...
public function banners(){
return $this->hasMany(Banner::class, 'banner_id');
}
}
This way you can query the model using whereHas function:
$banners = \App\Banner::with('categories')->where('visible', 1)
->where('up_at','<=', $today)
->where('down_at','>=', $today)
->whereHas('categories', function($query) use ($category) {
$query->where('category_id', '=', $category->id);
})->get();
By the way, this is not exactly same as your "join query" because in the result from model, the properties of categories will be grouped as a categories variable inside your banner model result:
$banners = [
0 => [
'id' => xxx,
'other model fields' => 'its value',
'categories' => [
0 => First category model result,
1 => Second related category,
...
],
...
]
since in your QueryBuilder result you will find both models properties together in one row:
$banners = [
0 => [
'id' => 'banner id',
'other banners properties',
'category properties',
],
...
]
Upvotes: 0