Logical mistake with eloquent query

I have below query code but i am not getting correct result. Is there any logical mistake in my code?

$tasks = Task::where('deleted_at', null);
        $tasks = $tasks->where(function($q) use ($user_id,$company_id, $task_ids){
            $q->where(function($q2) use ($user_id,$company_id, $task_ids) {
                $q2->where('created_by',$user_id)
                    ->where('company_id',$company_id);
            })
                ->orWhereIn('id',$task_ids);
        });
        $tasks_today = $tasks;
        $tasks_tomorrow = $tasks;
        $tasks_week = $tasks;

        $tomorrow = date("Y-m-d", strtotime("+1 day"));
        $today_cnt = $tasks_today->whereDate('due_date','=',date('Y-m-d H:i:s'))->count();
        $tomorrow_cnt = $tasks_tomorrow->whereDate('due_date','=',$tomorrow)->count();

        $week_count = $tasks_week->whereBetween('due_date', [Carbon::now()->startOfWeek(), 
        Carbon::now()->endOfWeek()])->count();

        $due_result[] = array(
            'due_today' => $today_cnt,
            'due_tomorrow' => $tomorrow_cnt,
            'due_this_week'=>$week_count,
            'due_all'=>$tasks->count()
        );

Snapey

6 years ago

Level 122

Could do with more information about what its not doing.

Also you should leverage relationships or scopes to make your code more readable.

For instance if these are only the specific user's tasks then why not;

$tasks = $user->tasks ......

Test each query individually.

Does the user have more than one company? If the user cannot belong to more than one company then including company id is pointless?

engrlaravel

6 years ago

Level 2

1- Yes, for safty purpose i added company id as well. 2- it should have users created tasks or tasks assigned to him.like all below task ids

$task_ids = TaskUser::where('user_id',$request->input('user_id'))->pluck('task_id')->all();

I am worried about his part, can we do like this?

       $tasks_today = $tasks;
        $tasks_tomorrow = $tasks;
        $tasks_week = $tasks;

then apply where conditions on individuals? is this correct approach? will it change or effect $tasks query string as well?

Snapey

6 years ago

Best Answer

Level 122

So if you already have the task_ids concerned then you don't need EITHER user or company in your query.

Yes, you can reuse the query but it would be better done in a single query

You can also in-line most of the query parameters.

    $query = Task::whereIn('id',$task_ids);

        $due_result = array(
            'due_today' => $query->whereDate('due_date', now())->count(),
            'due_tomorrow' => $query->whereDate('due_date', now()->addDay())->count(),
            'due_this_week'=> $query->whereBetween('due_date', [Carbon::now()->startOfWeek(), 
        Carbon::now()->endOfWeek()])->count(),
            'due_all'=>$task_ids->count(),
        );

engrlaravel

6 years ago

Level 2

For all people having this problem, it wont work.

   $today_cnt = $tasks_today->whereDate('due_date','=',date('Y-m-d H:i:s'))->count();
    $tomorrow_cnt = $tasks_tomorrow->whereDate('due_date','=',$tomorrow)->count();

    $week_count = $tasks_week->whereBetween('due_date', [Carbon::now()->startOfWeek(), 
    Carbon::now()->endOfWeek()])->count();

The problem is it cancatenate with previous conditions as well.

for example if you print

 $tomorrow_cnt = $tasks_tomorrow->whereDate('due_date','=',$tomorrow)->count();

it will have two due_date condition

Please or to participate in this conversation.