CODEWITHSUNDEEP
laraveleloquent
Farid Silva Aboid
Farid Silva Aboid

Reputation: 91

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!

Upvotes: 0

Views: 601

Answers (2)

Jerodev
Jerodev

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

Vahid
Vahid

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

Related Questions