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!
This can be cleaned up a lot...
Banner::isVisible()->startDate($today)->endDate($today)->categories()->byCategoryId($category->id)->get();
All those wheres could be thrown into query scopes so you can create more readable code. To access inside the categories relationship, you need the parens with creates a query builder. I don't have your data to test, but this should help you.
Thank you rocketpastsix for your advice, I will read more about those wheres and take it in count.
I received solution from users in laracast forum, so I share here, because it may helps somebody else.
Correct query in eloquent way is:
$banners = Banner::where('visible', 1)
->where('visible', 1)
->where('up_at','<=', $today)
->where('down_at','>=', $today)->get();
->with('categories')
->whereHas('categories', function($query) use ($categoryId) {
$query->where('id', $categoryId);
})->get();
Sign in to participate in this thread!
The Laravel portal for problem solving, knowledge sharing and community building.
The community