How to Get User's myProjects' Tasks with Eloquent in Laravel

Question

I'm new to Laravel and Eloquent. In my app, I want to retrieve the current user's and other project members' tasks and show them in the view. Here's the database relation I have made:

users (id)

projects (id, user_id)

user_project (id, user_id, project_id, role)

tasks (id, user_id, project_id)

(I have defined the required foreign keys in migrations)

To explain the relation, each project has a user (who has created the project). But in the user_project table, users can be assigned to other projects as well and their role is defined there. For example: There are two users:
id=1
id=2

And three projects:
id=1, user_id=1
id=2, user_id=2
id=3, user_id=1

user_project relationship:
id=1, user_id=1, project_id=1, role=admin
id=2, user_id=2, project_id=1, role=employee
id=3, user_id=2, project_id=2, role=admin
id=4, user_id=1, project_id=3, role=admin

And four tasks:
id=1, user_id=1, project_id=1
id=2, user_id=2, project_id=1
id=3, user_id=1, project_id=2
id=4, user_id=1, project_id=3

I want the user id=2 to be able to see tasks of project_id=1 because he is invited to that project as employee together with the tasks of project_id=2 because he has created that project. Of course the user shouldn't be able to see the tasks of project_id=3 because he's not a member. So, what's the neatest way to do this?

Here are the models I have defined:

class User extends Authenticatable
{
    public function projects(){
        return $this->hasMany(Project::class);
    }
    public function tasks(){
        return $this->hasMany(Task::class);
    }
    public function joinedProjects()
    {
        return $this->hasMany(ProjectUser::class);
    }
}

class Project extends Model
{
    public function tasks(){
        return $this->hasMany(Task::class);
    }
    public function users(){
        return $this->hasMany(User::class);
    }
}

class ProjectUser extends Model
{
}

class Task extends Model
{
    public function projects(){
        return $this->belongsTo(Project::class);
    }
    public function users(){
        return $this->belongsTo(User::class);
    }
}

Here's the way I'm trying to retrieve tasks of my project members (the ideal way I'm looking for to do this is: $tasks = $user->joinedProjects->tasks but I don't know how to do it, so here's how I'm currently trying to get this done):

class TasksController extends Controller
{
    public function index()
    {
        $user = Auth()->user();
        //I guess there are better ways to retrieve projects, but:
        $projects = app('App\Http\Controllers\HomeController')->allProjects($user);
        foreach($projects as $project){
            $tasks[] = Task::where('project_id', $project->id);
        }
        return view('tasks.index', compact(['tasks','user']));
        //gives error: Property [id] does not exist on the Eloquent builder instance
        //when I'm trying to get $task->id in a foreach loop.
    }
}

And here's the home controller (I need the allProjects() function in HomeController for some other class functions):

class HomeController extends Controller
{
    function allProjects($user){
        $projects = $user->projects;
        $otherProjects = \App\ProjectUser::where('user_id',$user->id)->get();
        foreach ($otherProjects as $project){
            $projects[] = \App\Project::find($project->project_id);
        }
        return $projects;
    }
}

Answer 1

First of all I think you should setup the joinedProjects as a many-to-many relationship instead, that way accessing it will feel more straightforward.

// in User model
public function joinedProjects()
{
    // i'm assuming you want to always have access to the role property
    return $this->belongsToMany(Project::class, 'user_project')->withPivot('role');
}

// in Project model
public function memberUsers()
{
    return $this->belongsToMany(User::class, 'user_project')->withPivot('role');
}

With this relationship you should be able to call $user->joinedProjects to get the list of project that the user has joined.

To get the task, of course you can call the joinedProjects relationship and loop through the resulting project like the for loop you have set up. Or as an alternative you can make use of the collection class' pluck method.

class TasksController extends Controller
{
    public function index()
    {
        // here I put load to eager load the project and task
        $user = Auth()->user()->load('joinedProjects.tasks');

        // OPTION 1: you can loop to get the tasks
        $tasks = collect();
        foreach($user->joinedProjects as $project){
            $tasks = $tasks->merge($project->tasks);
        }

        // OPTION 2: or use pluck (must be eager loaded to work)
        $tasks = $user->joinedProjects->pluck('tasks');

        // $tasks should be unique here, but if it is not you can call the unique method of collection
        $tasks = $tasks->unique('id');

        return view('tasks.index', compact(['tasks','user']));
    }
}

Your HomeController that you shared can also be simplified with the new relationship

class HomeController extends Controller
{
    function allProjects($user){
        // here i'm assuming user automatically joins a project when they create it
        $projects = $user->joinedProjects;

        return $projects;
    }
}

Here's some additional reference to the unique method i use in the code and the lazy eager loading