Sort collection by relationship value

Question

I want to sort a laravel collection by an attribute of an nested relationship.

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

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 don't know if im even in the right direction? Any advice is appreciated!

Answer 1

A nice clean way of doing this is with the . operator

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

Answer 2

I think you need to use something like join() and then sort by whatever 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 with in your query as:

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

Answer 3

Try This,

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

After that you can access project properties like below

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