How can I optimize my Laravel code? Such as counting the number of tasks in a particular project according to a task's status (done, todo, doing).

public function ShowTaskStatus(Project $project)
{
    $tasks = $project->tasks()->get();
    $totaltask = $tasks->count();
    $totaltask_doing = $tasks->where('status', "TODo")->groupBy('status')
        ->map(function ($row) {

            return $row->count();
        });
    $totaltask_todo = $tasks->where('status', "Doing")->groupBy('status')
        ->map(function ($row) {

            return $row->count();
        });
    $totaltask_done = $tasks->where('status', "Done")->groupBy('status')
        ->map(function ($row) {

            return $row->count();
        });

    return [$totaltask, $totaltask_doing, $totaltask_todo, $totaltask_done];
}

CodePudding user response：

If you only need the counts from different type of task, then dont retrieve them with get()

Use the queryBuilder count() method instead of the collection count() method

public function ShowTaskStatus(Project $project)
{
    $taskCount = $project->tasks()->count();
    $taskToDoCount = $project->tasks()->where('status', "TODo")->count();
    $taskDoingCount = $project->tasks()->where('status', "Doing")->count();
    $taskDoneCount = $project->tasks()->where('status', "Done")->count();
    return [$taskCount,$taskToDoCount, $taskDoingCount, $taskDoneCount];
}

If you only have those statuses, you can also omit the $taksCount variable.

public function ShowTaskStatus(Project $project)
{
    $taskToDoCount = $project->tasks()->where('status', "TODo")->count();
    $taskDoingCount = $project->tasks()->where('status', "Doing")->count();
    $taskDoneCount = $project->tasks()->where('status', "Done")->count();
    return [$taskToDoCount   $taskDoingCount   $taskDoneCount, $taskToDoCount, $taskDoingCount, $taskDoneCount];
}

You will notice a big difference in performance from doing a get then counting and doing the counting directly in the DB. especially if you have a lot of tasks entries.

CodePudding user response：

You can do this in one query;

return $project->tasks()->toBase()
       ->selectRaw(count(*) as total)
       ->selectRaw(count(case when status='TODo'  then 1 end) as todo)
       ->selectRaw(count(case when status='Doing' then 1 end) as doing)
       ->selectRaw(count(case when status='Done'  then 1 end) as done)
       ->first()
       ->values();