Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
The aim is to join 3 tables, so far I succeeded with two, but the third table query requires calculations to be made, and I'm stuck with DB::raw and select... So far I have this:
$products = DB::table('products')
->join('users', 'username', '=', 'products.owner_id')
->join('likeable_like_counters', 'likeable_id', '=', 'products.id')
->join('user_reviews', 'user_id', '=', 'users.id')
->select([
'products.*',
'likeable_like_counters.count AS productLikeCount',
'users.image AS userAvatar',
'users.id AS userID',
])
->get();
$userRating = DB::table('user_reviews')
->select(DB::raw('SUM(rating) / count(rating) as userRating, user_id'))
->groupBy('user_id')
->get();
I would like to have $userRating query within $products. I tried:
$products = DB::table('products')
->join('users', 'username', '=', 'products.owner_id')
->join('likeable_like_counters', 'likeable_id', '=', 'products.id')
->join('user_reviews', 'user_id', '=', 'users.id')
->select([
'products.*',
'likeable_like_counters.count AS productLikeCount',
'users.image AS userAvatar',
'users.id AS userID',
])
->select(DB::raw('SUM(user_reviews.rating) / count(user_reviews.rating) as userRating, user_id'))
->groupBy('user_id')
->get();
but then it just gives me the userRating and user_id. Am I using syntax wrong? or is it impossible?
Try changing the one select to an addSelect instead
->addSelect([
'products.*',
'likeable_like_counters.count AS productLikeCount',
'users.image AS userAvatar',
'users.id AS userID',
])
Im gettin error:
Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'imusau.products.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select SUM(user_reviews.rating) / count(user_reviews.rating) as userRating, user_id, `products`.*, `likeable_like_counters`.`count` as `productLikeCount`, `users`.`image` as `userAvatar`, `users`.`id` as `userID` from `products` inner join `users` on `username` = `products`.`owner_id` inner join `likeable_like_counters` on `likeable_id` = `products`.`id` inner join `user_reviews` on `user_id` = `users`.`id` group by `user_id`)
@SPL3S yeah. Your group by and selects always need to match.
@Sinnbeck I don't understand the error, does group by has to be in both select?
@SPL3S well, it needs to group multiple rows into 1.. How can it group an ID for instance? Imagine an excel sheet with 3 rows. You want the group the two first, to get a new row which is the sum of column 2. The first column is the ID.. How should that be represented in the new row? A random ID? Or no ID?
Consider each column one by one. How would it look if you group 2 rows into one
@Sinnbeck Im trying to understand the structure of it. Should it be grouped by the same ID? or The groupBy is picking what should follow in the row after the new ID from the tables Im trying to group?
@SPL3S it is in theory copying data to a new table where you deside the columns. If you grouped by id, then the table would be the same, as no two ids are the same. Instead remove products.* and add the columns you need one by one (also in the group by)
@Sinnbeck Im sort of starting to understand the mechanics, now Im specifying to groupBy as are select clauses
$products = DB::table('products')
->join('users', 'username', '=', 'products.owner_id')
->join('likeable_like_counters', 'likeable_id', '=', 'products.id')
->join('user_reviews', 'user_id', '=', 'users.id')
->select(DB::raw('SUM(user_reviews.rating) / count(user_reviews.rating) as userRating'))
->addSelect([
'products.name AS productName',
'likeable_like_counters.count AS productLikeCount',
'users.image AS userAvatar',
'users.id AS userID',
])
->groupBy(
'userRating',
'productName',
'productLikeCount',
'userAvatar',
'userID'
)
->get();
but getting an error:
Syntax error or access violation: 1056 Can't group on 'userRating' (SQL: select SUM(user_reviews.rating) / count(user_reviews.rating) as userRating, `products`.`name` as `productName`, `likeable_like_counters`.`count` as `productLikeCount`, `users`.`image` as `userAvatar`, `users`.`id` as `userID` from `products` inner join `users` on `username` = `products`.`owner_id` inner join `likeable_like_counters` on `likeable_id` = `products`.`id` inner join `user_reviews` on `user_id` = `users`.`id` group by `userRating`, `productName`, `productLikeCount`, `userAvatar`, `userID`)
@SPL3S you don't actually group on an aggregate (sum) like userRating. Just remove that from group by
@Sinnbeck Success!
{
"userRating": "5.0000",
"productName": "Agato akmens vėrinys",
"productLikeCount": 1,
"userAvatar": "user-dummy-300x300.png",
"userID": 617
},
{
"userRating": "5.0000",
"productName": "Akiniai nuo saulės",
"productLikeCount": 1,
"userAvatar": "1632411222_64678.jpeg",
"userID": 9
},
Thanks for your help, now I need to understand why aggregates are not grouped
@SPL3S Awesome :) Happy to have helped. If you group aggregates then you need to to it in an outer query :) But take the query and play around in a database manager.. It might make more sense then
If you have a query that looks something like this
SELECT col1, col2, COUNT(*)
FROM table
WHERE col1 = 'something'
AND col2 = 'something else'
You need to group col1 and col2 together.
SELECT col1, col2, COUNT(*)
FROM table
WHERE col1 = 'something'
AND col2 = 'something else'
GROUP BY col1, col2
If you try to just group by col1 you will get the error you just got.
The SUM is just like COUNT an aggregate and they need a group by.
So in your case add list to your group by clause.
Please or to participate in this conversation.