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.

EchoAro · Accepted Answer · 2022-07-24 12:51:05Z

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

answered Jul 24, 2022 at 12:51

EchoAro

8381 gold badge8 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ondraasek Over a year ago

Thank you so much, after 4 hours of trying its relief and understand the point now. Thanks again bro, appreciate your help. Works like a charm.

Ondraasek Over a year ago

youre my hero bro, you made my day., thanks a lot again

Collectives™ on Stack Overflow

Laravel Query builder with pivot table and where condition

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related