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
P粉2587888312023-09-12 16:58:05
I think the ordering issue may be that you are calling latest()
and orderBy('end_date', 'desc')
as latest()
works on created_at
. You probably don't need the latest()
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 two orWhereHas
calls instead of whereHas
and orWhereHas< /code> which might work in
scopeForUser
causes problems.
Perhaps another approach could look like this:
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'); }