Question 214121 in Laravel Question posted in
The official Laravel Documentation can be found here.

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

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 filter apply 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);
    }
Tags:

2

Answers


  1. 0

    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 ) ]) )
                        ->withCount(['transactions'])->get();

        return response()->json($users);
    }
    Login or Signup to reply.
  2. 0

    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)));
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search