phplaravellaravel-5eloquent

Get models that have specific value(s) in a relationship


I am trying to filter 'activities' according to categories given, the categories of an activity are given by a belongsToMany relationship.

Is it possible to filter the activities to only allow the ones with (or without) the categories inside the array $categories ?

After searching mostly on SO, I learned that a 'line' function can be executed inside the with statement, and I tried to use the 'wherePivotIn' modifier thinking it will filter the pivot 'id_category' to be in the list, but I get an undefined variable error on $categories.

If this doesnt work,do any have a good solution to filter models according to one of its relationships?

Inside the controller

public function get_activity_cat($categories_db){

    $categories = [];    
    foreach (json_decode($categories_db) as $key => $value) {
       array_push($categories, $value->id);
    }

    return Activity::select('id', 'name_spa', 'name_eng', 'price')
                   ->with('covers')
                   ->with('languages')
                   ->with([ 'categories' => function($query){ $query->wherePivotIn('id_category', $categories); } ])
                   ->get()
                   ->toJson()
    ;

}

My model

class Activity extends Model
{
    protected  $primaryKey = 'id';
    public function covers()
    {
        return $this->belongsToMany('App\Cover', 'activities_covers', 'id_activity', 'id_cover')
                    ->select(array('id', 'name_spa', 'name_eng'));
    }

    public function languages()
    {
        return $this->belongsToMany('App\Language', 'activities_languages', 'id_activity', 'id_language')
                    ->select(array('id', 'id_lang', 'lang_name'));
    }

    public function categories()
    {
        return $this->belongsToMany('App\Category', 'activities_categories', 'id_activity', 'id_category');
    }
}

----EDIT----

Thanks to the comment of LC Yoong the undefined variable error is solved, but than I am without a solution.

That method only filters the result of the relationship, but it doesn't exculde the 'activity' when the pivot is not in the $categories array.


Solution

  • You can narrow the results based on a relation condition using whereHas. Try this:

    return Activity::select('id', 'name_spa', 'name_eng', 'price')
            ->whereHas('categories', function($q) use ($categories ){
                $q->whereIn('id', $categories);
            })
            ->with(['covers', 'languages', 'categories'])
            ->get()
            ->toJson();