How to filter Eloquent collection with data from pivot table

Question

I'm searching for a fine and neat solution to filter my Eloquent collection with the data in the related pivot table. I actually found a solution to my problem already though it feels like my solution is somehow bad. Here is, what I got:

Two models Video and User where every user can track separately the progress on a video. For this I need to save the progress for every video in the ratings table related to the user. When you want to search for just the progress you can do that besides some other filters (category, full text search). What I find a bit quirky is that I have to double "where()" the part where I'm checking the video progress for the logged in user.

Video.php

class Video extends Model
{
    use SearchableTrait;
    use Taggable;
    [...]
    public function videocreator(){
        return $this->belongsTo('App\User', 'create_user_id');
    }

    public function users(){
        return $this->belongsToMany('App\User', 'progress', 'video_id', 'user_id')
            ->withPivot('progress_index')
            ->withTimestamps();
    }
    [...]
}

User.php

class User extends Authenticatable
{
    use Notifiable;
    [...]
    public function videoscreated(){
        return $this->hasMany('App\Video');
    }

    public function videos(){
        return $this->belongsToMany('App\Video', 'progress', 'user_id', 'video_id')
            ->withPivot('progress_index')
            ->withTimestamps();;
    }
}

VideoController.php

class VideoController extends Controller
{
    public function index(Request $request)
    {
    [...]
    $videos = Video::with('videocreator')->with(['users' => function ($query) {
                $query->where('users.id', '=', auth()->user()->id);
        }])->latest();

    if($request->filled('progress') && $request['progress'] !== 'all'){
            $videos = $videos->whereHas('users', function($query) use($selectedProgress) {
                $query->where('progress_index', $selectedProgress)->where('users.id', '=', auth()->user()->id);
                });
        }

    $videos = $videos->get();
    [...]
}

As you can see this part where('users.id', '=', auth()->user()->id) is duplicated. A friend of mine from the Ruby on Rails Faction proposed to come from the User Model first and then fetch the Videos (that's how you would do it there). Though this way you would limit the videos to the user from the progress table. This is not what you want. The App shall track only the per user progress per video, meaning, all the users can see all the videos (just not the progress which is not their own. Another approach would be to put the part where you filter for the user into the relationship. A method like: myProgress() or something similar.

What is your opinion to that? Is there a more 'eloquent' way to solve that? Thanks in advance for reading this post!

user_12570965 · Answer

I would do something like this :

// Get an instance of the videos relationship of the current authenticated user
// Eager load the videocreator relationship
$videoQuery = request()->user()->videos()->with('videocreator');

if ($request->filled('progress') && $request->input('progress') !== "all") {
  // Constrain the query : only get the videos with rated_index equal to $selectedProgress
  $videoQuery = $videoQuery->wherePivot('rated_index', $selectedProgress);
}

// Finally, run the query against the database
$videos = $videoQuery->latest()->get();

How to filter Eloquent collection with data from pivot table

1 Answers