Laravel get latest projects sorted by project end date-PHP Chinese Network Q&A

I need to write a scope to get items for the currently logged in user. If the currently logged in user has view all projects permission then I want to list all the projects in the system, otherwise I want to list the projects created by the logged in user or assigned by tasks in the user. I tried writing the following scope in the Project model:

public function scopeForUser($query, User $user)
  {
    if ($user->can('view-all-projects')) {
      return $query->withBudget()
        ->with(['customer:id,company_id' => ['company:id,name']])
        ->latest()
        ->orderBy('end_date', 'desc');
    }

    return $query->withBudget()
      ->with(['customer:id,company_id' => ['company:id,name']])
      ->where(function ($query) use ($user) {
        $query->where('user_id', $user->id)
          ->orWhereHas('users', function ($query) use ($user) {
            $query->where('users.id', $user->id);
          })
          ->orWhereHas('tasks', function ($query) use ($user) {
            $query->where('user_id', $user->id);
          });
      })
      ->latest()
      ->orderBy('end_date', 'desc');
  }

The range of

withBudget is as follows

public function scopeWithBudget($query)
  {
    return $query->addSelect(['budget' => function ($subQuery) {
      $subQuery->selectRaw('sum(cost)')
        ->from('tasks')
        ->whereColumn('project_id', 'projects.id');
    }]);
}

So far it works, but it's not accurate. Sometimes when I know the logged in user is an administrator and has all permissions, it only gets a few items and the items are also sorted in descending order, I think created_at instead of end_date< /代码>


Any ideas how to solve this problem? If you need more information please let me know
edit
If I remove some queries, the following code snippet works: 
if ($user->hasPermissionTo('view-all-projects')) {
      return $query->latest();
    } else {
      return $query->where(function ($query) use ($user) {
        $query->where('user_id', $user->id)
          ->orWhereIn('id', function ($query) use ($user) {
            $query->select('project_id')
              ->from('tasks')
              ->where('user_id', $user->id);
          });
      });
    }
 Then I get the items by doing: 
<?php

namespace App\Actions;

use App\Models\Project;
// use Illuminate\Support\Collection;
use Illuminate\View\View;

class ListProjects
{

  public function __invoke(): View
  {
    $projects = Project::forUser(auth()->user())->get();

    return view('projects.index', ['projects' => $projects, 'projectsCount' => $projects->count()]);
  }
}
While the above code snippet works, it doesn't do one thing (forget eager loading for now) but I need to list projects based on the time remaining before the deadline. So it should list in order items that are due soon, items that have more time, items that are already due

public function scopeForUser($query, User $user) { if ($user->can('view-all-projects')) { return $query->withBudget() ->with(['customer:id,company_id' => ['company:id,name']]) ->orderBy('end_date', 'desc'); } return $query->withBudget() ->with(['customer:id,company_id' => ['company:id,name']]) ->where('user_id', $user->id) ->orWhere(function ($query) use ($user) { $query->whereHas('users', function ($query) use ($user) { $query->where('users.id', $user->id); }) ->orWhereHas('tasks', function ($query) use ($user) { $query->where('user_id', $user->id); }); }) ->orderBy('end_date', 'desc'); }