A GROUP BY can be tricky, and can give unexpected results if not written correctly.
Use toSql() to see the query and study over:
https://www.mysqltutorial.org/mysql-basics/mysql-group-by/
for a better understanding.
Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Hello, i use Laravel 9 and have issue with records counting. Somebody can explain to me why count = 2, please? Here is my code:
$request->validate([
'limit' => 'required|integer|min:1|max:100',
]);
$base_query = Review::rightJoin('excursion_types', 'excursion_types.id', '=', 'reviews.excursion_type_id')
->rightJoin('excursions', 'excursions.id', '=', 'excursion_types.excursion_id')
->when($request->category, function ($query) use ($request) {
$query->rightJoin('excursion_categories', 'excursion_categories.excursion_id', '=', 'excursions.id');
$query->rightJoin('categories', function (JoinClause $join) use ($request) {
$join->on('categories.id', '=', 'excursion_categories.category_id')
->where('categories.alias', '=', $request->category);
});
}, function ($query) {
$query->rightJoin('excursion_categories', 'excursion_categories.excursion_id', '=', 'excursions.id');
$query->rightJoin('categories', 'categories.id', '=', 'excursion_categories.category_id');
})
->when($request->city, function ($query) use ($request) {
$query->rightJoin('city_categories', 'city_categories.category_id', '=', 'categories.id');
$query->rightJoin('cities', function (JoinClause $join) use ($request) {
$join->on('cities.id', '=', 'city_categories.city_id')
->where('cities.alias', '=', $request->city);
});
})
->where('reviews.status', ReviewStatusEnum::ACTIVE->value)
->where('reviews.rating', '>=', 4)
->groupBy('reviews.id');
$reviews_count = $base_query->count();
$reviews = $base_query->with('reviewImgs')
->select(array_merge(
['reviews.*'],
$request->category ? ['categories.alias as category_alias'] : [],
$request->city ? ['cities.alias as city_alias'] : []
))
->orderBy('reviews.created_at', 'desc')
->limit($request->limit)
->get();
return [
'reviews' => $reviews,
'reviews_count' => $reviews_count,
];
And i get answer from server like:
{
reviews: [
{
id: 1337,
user_id: 1609,
excursion_type_id: 43,
body: 'text 1337',
rating: 5,
status: 'active',
responded_at: '2024-05-14 08:06:53',
is_anonymously: 0,
created_at: '2025-04-14T20:00:00.000000Z',
updated_at: '2025-04-14T15:35:11.000000Z',
review_imgs: []
},
{
id: 1333,
user_id: 1605,
excursion_type_id: 248,
body: 'text 1333',
rating: 5,
status: 'active',
responded_at: '2024-05-14 08:06:53',
is_anonymously: 0,
created_at: '2025-04-14T20:00:00.000000Z',
updated_at: '2025-04-14T15:32:41.000000Z',
review_imgs: []
},
{
id: 1342,
user_id: 1614,
excursion_type_id: 106,
body: 'text 1342',
rating: 5,
status: 'active',
responded_at: '2024-05-14 08:06:53',
is_anonymously: 0,
created_at: '2025-04-12T20:00:00.000000Z',
updated_at: '2025-04-14T15:39:05.000000Z',
review_imgs: []
},
{
id: 1331,
user_id: 1603,
excursion_type_id: 266,
body: 'text 1331',
rating: 5,
status: 'active',
responded_at: '2024-05-14 08:06:53',
is_anonymously: 0,
created_at: '2025-04-12T20:00:00.000000Z',
updated_at: '2025-04-14T15:31:45.000000Z',
review_imgs: []
},
{
id: 1345,
user_id: 1617,
excursion_type_id: 307,
body: 'text 1345',
rating: 5,
status: 'active',
responded_at: '2024-05-14 08:06:53',
is_anonymously: 0,
created_at: '2025-04-12T20:00:00.000000Z',
updated_at: '2025-04-14T15:40:46.000000Z',
review_imgs: []
},
{
id: 1335,
user_id: 1607,
excursion_type_id: 252,
body: 'text 1335',
rating: 5,
status: 'active',
responded_at: '2024-05-14 08:06:53',
is_anonymously: 0,
created_at: '2025-04-12T20:00:00.000000Z',
updated_at: '2025-04-14T15:33:56.000000Z',
review_imgs: []
},
{
id: 1347,
user_id: 1619,
excursion_type_id: 257,
body: 'text 1347',
rating: 5,
status: 'active',
responded_at: '2024-05-14 08:06:53',
is_anonymously: 0,
created_at: '2025-04-12T20:00:00.000000Z',
updated_at: '2025-04-14T15:42:16.000000Z',
review_imgs: []
},
{
id: 1339,
user_id: 1611,
excursion_type_id: 21,
body: 'text 1339',
rating: 5,
status: 'active',
responded_at: '2024-05-14 08:06:53',
is_anonymously: 0,
created_at: '2025-04-12T20:00:00.000000Z',
updated_at: '2025-04-14T15:37:20.000000Z',
review_imgs: []
},
{
id: 1348,
user_id: 1620,
excursion_type_id: 62,
body: 'text 1348',
rating: 5,
status: 'active',
responded_at: '2024-05-14 08:06:53',
is_anonymously: 0,
created_at: '2025-04-12T20:00:00.000000Z',
updated_at: '2025-04-14T15:43:03.000000Z',
review_imgs: []
},
{
id: 1336,
user_id: 1608,
excursion_type_id: 35,
body: 'text 1336',
rating: 5,
status: 'active',
responded_at: '2024-05-14 08:06:53',
is_anonymously: 0,
created_at: '2025-04-12T20:00:00.000000Z',
updated_at: '2025-04-14T15:34:34.000000Z',
review_imgs: []
},
{
id: 1340,
user_id: 1612,
excursion_type_id: 83,
body: 'text 1340',
rating: 5,
status: 'active',
responded_at: '2024-05-14 08:06:53',
is_anonymously: 0,
created_at: '2025-04-12T20:00:00.000000Z',
updated_at: '2025-04-14T15:37:55.000000Z',
review_imgs: []
},
{
id: 1341,
user_id: 1613,
excursion_type_id: 104,
body: 'text 1341',
rating: 5,
status: 'active',
responded_at: '2024-05-14 08:06:53',
is_anonymously: 0,
created_at: '2025-04-12T20:00:00.000000Z',
updated_at: '2025-04-14T15:38:21.000000Z',
review_imgs: []
}
],
reviews_count: 2
}
Please or to participate in this conversation.