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 theProjectmodel:

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

withBudgetis 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 thinkcreated_atinstead ofend_date< /代码>

Any ideas how to solve this problem? If you need more information please let me know
editIf 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:
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'); }

reply all (1)

P粉2587888312023-09-12 16:58:05 1 floor

I think the ordering issue may be that you are callinglatest()andorderBy('end_date', 'desc')aslatest()works oncreated_at. You probably don't need thelatest()call at all, since you'll be adding your own sorting anyway.

As for the user scope issue, I was wondering if you were using twoorWhereHascalls instead ofwhereHasandorWhereHas which might work inscopeForUsercauses problems.

Perhaps another approach could look like this: