Querying a model using conditions in relationship

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


class Banner extends Model
    public function categories()
        return $this->belongsToMany(Category::class, 'banner_category');


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', '', '=', 'banner_category.banner_id')
            ->where('banners.up_at','<=', $today)
            ->where('banners.down_at','>=', $today)
            ->where('visible', 1)

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);

Any advice will be appreciated. Thanks!

rocketpastsix replied 11 months ago

This can be cleaned up a lot...


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.

farid silva replied 11 months ago Solution

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();
    ->whereHas('categories', function($query) use ($categoryId)  {
        $query->where('id', $categoryId);

Sign in to participate in this thread!

We'd like to thank these amazing companies for supporting us