@tisuchi
I don't think that's supposed to work. The fields feedback_count and positive_feedback_count don't exist until the query is solved
You're going to have to use a cte to get those results and then join that one with users
$cte = User::query()
->select('id as user_id')
->withCount([
'sellerFeedbacks as positive_feedback_count' => function ($query) {
$query->onlyPositive();
},
'sellerFeedbacks as feedback_count' => function ($query) {
$query->onlyPositive()
->orWhere(function ($query) {
$query->onlyNegative();
});
},
]);
$users = User::query()
->select(['users.*', 'cte.*'])
->joinSub($cte, 'cte', function($join) {
$join->on('users.id', '=', 'cte.user_id');
})
->selectRaw('CASE WHEN feedback_count > 0 THEN (positive_feedback_count * 100 / feedback_count) ELSE 0 END as average_user_rating_percentage')
->orderByRaw('(CASE WHEN feedback_count > 0 THEN (positive_feedback_count * 100 / feedback_count) ELSE 0 END) desc')
->get();
Now feedback_count and positive_feedback_count do exist because they are being provided by the cte alias, which is solved first by the database
The orderBy has to be an expression as well because of the same reason you can't directly access the feedback fields
IIRC, this was possible in mysql older versions, on non-strict mode, where you could reference on the where or order by a field you were "calculating" as some expression on the select clause. But don't quote me on this. I might be misremembering...
I doubt this will work as copy/paste thing since I don't have the tables but that's the idea.
