Back

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

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!

rocketpastsix replied 1 week ago

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.

farid silva replied 1 week 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();
    ->with('categories')
    ->whereHas('categories', function($query) use ($categoryId)  {
        $query->where('id', $categoryId);
    })->get();

Sign in to participate in this thread!



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