Laravel Query builder with pivot table and where condition

Question

I have 2 tables - Videos and Categories. And one pivot table Video_Category because category can have more videos.

I tried to get all ids from pivot table:

$allcategoryvideos = CategoryVideo::where('category_id', $category_id)->pluck('video_id');

But I want to filter all videos (video table) with where('featured', 1), So I used foreach and tried to get all videos:

  $featured = [];
            
            foreach ($allcategoryvideos as $video){
                array_push($featured, [
                    $video = Video::where('id', $video)->whereNotNull('featured')->first(),
                    
                ]);
                
            }
            return $featured;

and at model CategoryVideo pivot have this relationship:

public function videofeatured() {
       return $this->hasOne(Video::class, 'id', 'video_id')->where('featured', 1);
        
       
    } 

So, it almost works, but if pluck() on first line has 2+ records and all arent featured, it's giving me empty index at array like this:

[[{"id":1,"created_at":"2022-07-24T14:20:30.000000Z","updated_at":"2022-07-24T14:20:34.000000Z","name":"Video 1","slug":"video","description":"123","video_link":"123","mp3_link":"123","image_url":"123","minutes":10,"sort_id":null,"featured":1,"is_visible":1}],[null]]

I need help to fix this or create some query, get all videos belong to specific category and filtr specific video if its featured or not and paste it to array without empty indexes. Thanks a lot for help.

Answer 1

You can make this logic

Video::where('featured', 1)->whereHas('categories', function ($q)use($category_id) {
        $q->where('categories.id', $category_id);
    })->get();

In Video model you should make this

public function categories() {
    return $this->belongsToMany(Category::class, 'category_video');
}

Also you can make relation to pivot table like this

public function category_video() {
    return $this->hasMany(CategoryVideo::class);
}

And make more prefomed query

    Video::where('featured', 1)->whereHas('category_video', function ($q)use($category_id) {
        $q->where('category_video.category_id', $category_id);
    })->get();