withCount() is not working acccording to filters applied in when()

Question

Have problem with this is withCount() not changing after when() is applied. For example, without when(), transaction_count is 5000, and after when() is applied, it still remains 5000. It should change according to the filter applied in when().

public function allUsers(Request $request)
{
    $date = $request->input('date');
    $location = $request->input('location');

    $users = User::when(!empty($date), function ($query) use ($date) {
        return $query->whereRelation('transactions', 'date', '=', date('Y-m-d', strtotime($date)));
    })->when($location, function ($query) use ($location) {
        return $query->whereRelation('transactions', 'location', '=', $location);
    })->withCount(['transactions'])->get();

    return response()->json($users);
}

Answer 1

You need to place your withCount function inside the closure of when function, then only it will count the data based on your condition, else it will always return the whole transaction_count of 5000,

you need to change your query something like below.

public function allUsers(Request $request)
{
        $date = $request->input('date');
        $location = $request->input('location');

        $users = User::when($date, fn($q) => $q->withCount(['transactions'=> fn($qr) => $qr->where('date', '=', date('Y-m-d', strtotime($date) )) ]) )
                        ->when($location, fn($q) => $q->withCount(['transactions'=> fn($qr) => $qr->where( 'location', '=', $location ) ]) )
                        ->when(!$date && !$location, fn($q) => $q->withCount('transactions') )
                        ->get();

        return response()->json($users);
}

Answer 2

From what I remember, whereRelation() executes a second query, separately, and withCount() executes a subquery within the same one.

Instead of whereRelation(), try to use join()

Instead of:

return  $query->whereRelation('transactions', 'date', '=', date('Y-m-d', strtotime($date)));

Try:

return $query->join('transactions', 'users.id', '=', 'transactions.user_id')
    ->where('transactions.date', date('Y-m-d', strtotime($date)));