Level 122
you are using pluck wrong I think. Use get() instead or reverse the parameters
the second parameter is the key
Nov 18, 2022
7
Level 12
Query distributed by review stars not showing proper results
Hey everyone!
I have this query
$reviewArr = Review::select('stars', \DB::raw('COUNT(*) as total'))
->where('website_id', $request->website_id)
->groupBy('stars')
->pluck('stars', 'total');
which is supposed to print an array like
#items: array:1 [▼
1 => 15,
2 => 31,
3 => 81,
4 => 64,
5 => 75
]
which is actually an count on how many reviews for each rating possible (1-5) a specific website have. As you can see 1star count = 15, 2star count = 31, 3star count = 81 etc... But it is not working properly. Sometimes it's showing strange result like
#items: array:1 [▼
1 => 5
]
Which is not correct. The result printed is for website id 2, and I can clearly see that website id 2 has 5 reviews (one 1star, one 2star, one 3star, one 4star and one 5star) so this means the query is not working properly. What might be the problem? Here's my full code
$reviewArr = Review::select('stars', \DB::raw('COUNT(*) as total'))
->where('website_id', $request->website_id)
->groupBy('stars')
->pluck('stars', 'total');
dd($reviewArr);
$totalReviews = Review::where('website_id', $request->website_id)->count();
return response()->json([
'html' => view('admin.ajaxViews.addbulkDonutchart', [
'website' => Website::find($request->website_id),
'total' => $totalReviews,
'onestar' => $reviewArr[1] ?? 0,
'twostar' => $reviewArr[2] ?? 0,
'threestar' => $reviewArr[3] ?? 0,
'fourstar' => $reviewArr[4] ?? 0,
'fivestar' => $reviewArr[5] ?? 0,
])->render(),
'total' => $totalReviews,
'onestar' => $reviewArr[1] ?? 0,
'twostar' => $reviewArr[2] ?? 0,
'threestar' => $reviewArr[3] ?? 0,
'fourstar' => $reviewArr[4] ?? 0,
'fivestar' => $reviewArr[5] ?? 0,
]);
Level 12
$reviewArr = Review::select('stars', \DB::raw('COUNT(*) as total'))
->where('website_id', $request->website_id)
->groupBy('stars')
->pluck('stars');
$reviewArr = array_reverse($reviewArr->toArray());
dd($reviewArr);
prints wrong info again.
array:5 [▼
0 => 5
1 => 4
2 => 3
3 => 2
4 => 1
]
and with the current information for website_id = 2 it must be
array:5 [▼
1 => 1
2 => 1
3 => 1
4 => 1
5 => 1
]
Level 12
Anyway, could not find a solution so I just separated the queries:
$total_1_star = Review::where('website_id', $request->website_id)->where('stars', 1)->count();
$total_2_star = Review::where('website_id', $request->website_id)->where('stars', 2)->count();
$total_3_star = Review::where('website_id', $request->website_id)->where('stars', 3)->count();
$total_4_star = Review::where('website_id', $request->website_id)->where('stars', 4)->count();
$total_5_star = Review::where('website_id', $request->website_id)->where('stars', 5)->count();
Level 122
@Laralex No,no no
DB::table('reviews')
->select(DB::raw('stars, count(*) as count'))
->where('website_id',5)
->groupBy('stars')
->get()
->pluck('count','stars');
Level 122
@Laralex V2 with Eloquent
Review::query()
->select(DB::raw('stars, count(*) as count'))
->where('website_id',5)
->groupBy('stars')
->orderBy('stars')
->pluck('count','stars');
Level 122
@Laralex Your original query works fine and gives the same result with the pluck statement swapped as per my first suggestion
Review::select('stars', \DB::raw('COUNT(*) as total'))
->where('website_id', 5)
->groupBy('stars')
->pluck('total','stars');
NOTE, if there are none of one star rating (maybe the site has only 4 and 5 stars) then the others will be missing from the array, and you should allow for this possibility when displaying the results.
Level 12
@Snapey I ended up with
DB::table('reviews')
->select(DB::raw('stars, count(*) as count'))
->where('website_id',5)
->groupBy('stars')
->get()
->pluck('count','stars');
Thanks a ton for your time dude!
Please or to participate in this conversation.