Laravel withAvg and Where Condition returns unknown column-CodePudding

I want return users having rating more than 4 . so I have tried the query as follows but it shows error like

Illuminate\Database\QueryException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'received_reviews_avg_rating'

My code is

 $users = User::when($name, function ($query)use ($name) {
                    return $query->where('name', 'like', '%' . $name . '%');
                 })
            ->where('id', '!=', auth()->user()->id)
            ->with('profile')
            ->withCount('receivedReviews')
            ->withAvg('receivedReviews as received_reviews_avg_rating', 'rating')
            ->where('received_reviews_avg_rating', 4)
            ->get();

Whats the issue here and how to solve it please help

CodePudding user response：

$users = User::query()
->when($name, function ($query) use ($name) {
    return $query->where('name', 'like', '%' . $name . '%');
})
->where('id', '!=', auth()->user()->id)
->with('profile')
->withCount('receivedReviews')
->withAvg('receivedReviews', 'rating')
->having('received_reviews_avg_rating', 4)
->get();

CodePudding user response：

You can use avg column without rename it, because laravel put name automatically, example below:

$users = User::query()
    ->when($name, function ($query) use ($name) {
        return $query->where('name', 'like', '%' . $name . '%');
    })
    ->where('id', '!=', auth()->user()->id)
    ->with('profile')
    ->withCount('receivedReviews')
    ->withAvg('receivedReviews', 'rating')
    ->having('received_reviews_avg_rating', 4)
    ->get();

Now, you can access the average column by key: received_reviews_avg_rating