Sorting a collection by relationship value

Question

I want to sort the laravel collection by attribute of nested relationships.

Therefore, I request all the projects (only where the project has tasks related to the current user), and then I want to sort the projects by the end date of the relationship to the task.

Current code:

Project.php

public function tasks()
{
    return $this->hasMany('App\Models\ProjectTask');
}

Task.php

public function project()
{
    return $this->belongsTo('App\Models\Project');
}

Usercontroller

$projects = Project->whereHas('tasks', function($query){
        $query->where('user_id', Auth::user()->id);
    })->get()->sortBy(function($project){
        return $project->tasks()->orderby('deadline')->first(); 
    });

I do not know if im even in the right direction? Any advice is appreciated!

Answer 1

I think you need to use something like join(), and then sort everything you need.

For exapmle:

Project::join('tasks', 'tasks.project_id', '=', 'projects.id')
        ->select('projects.*', DB::raw("MAX(tasks.deadline) as deadline_date"))
        ->groupBy('tasks.project_id')
        ->orderBy('deadline_date')
        ->get()

Update

Project::join('tasks', function ($join) {
            $join->on('tasks.project_id', '=', 'projects.id')
                ->where('tasks.user_id', Auth::user()->id)
                ->whereNull('tasks.completed');
        })
        ->select('projects.*', DB::raw("MAX(tasks.deadline) as deadline_date"))
        ->groupBy('tasks.project_id')
        ->orderBy('deadline_date')
        ->get()

Update2

Add withas:

->with(['tasks' => function ($q) {
    $q->where('user_id', Auth::user()->id)
       ->whereNull('completed');
})

Answer 2

,

$tasks = Task::with('project')
            ->where('user_id',Auth::user()->id)
            ->orderBy('deadline')
            ->get();

,

$tasks->first()->project()->xxx

Answer 3

A nice clean way to do this with an operator .

$projects = Project::all()->load('tasks')->sortBy('tasks.deadline');