I'm creating a mini e-commerce app, where users can rate items they purchase.
The rating is an int from 1 to 5 (representing stars).
A user can only rate an item once.
I have the following tables:
From there I would like to make a query that select all items, and for each item I need to know how many of each rating value each product has (i.e., how many 5 stars, how many 4 stars, ..., how many 1 stars).
So far, I've got this (complex) query, which works:
select i.id,
i.label,
t1.rating,
t1.nb_ratings
from lks_item i
inner join
(
select item_id,
rating,
count(*) as nb_ratings
from lks_user_rates_item h
group by h.rating, h.item_id
) as t1 on i.id = t1.item_id
where i.item_type = '1'
and i.status = '1'
order by i.id asc, t1.rating asc
Here is an excerpt of what I get:
+--+--------+------+----------+
|id|label |rating|nb_ratings|
+--+--------+------+----------+
|21|item1 |1 |2 |
|21|item1 |3 |1 |
|21|item1 |4 |1 |
|21|item1 |5 |1 |
|22|item2 |1 |3 |
|22|item2 |2 |4 |
|22|item2 |3 |3 |
|22|item2 |5 |4 |
|23|item3 |1 |2 |
+--+--------+------+----------+
Although this gets the job done, it returns one row per rating value. So for instance the item #21 has 4 rows (because it has 4 different rating values), and item #22 get 4 rows too.
I'm curious if there is a way to have only one row per item, with the help of the mysql GROUP_CONCAT function.
In other words, is it possible to have a query that get something more like this ?
+--+--------+-------------------+
|id|label |nb_ratings |
+--+--------+-------------------+
|21|item1 |1:2, 3:1, 4:1, 5:1 |
|22|item2 |1:3, 2:4, 3:3, 5:4 |
|23|item3 |1:2 |
+--+--------+-------------------+
In your query you can group by i.id, i.label and use group_concat():
select i.id, i.label,
group_concat(concat(t.rating, ':', t.nb_ratings) order by t.rating separator ', ') as nb_ratings
from lks_item i
inner join (
select item_id,
rating,
count(*) as nb_ratings
from lks_user_rates_item
group by rating, item_id
) as t on i.id = t.item_id
where i.item_type = '1' and i.status = '1'
group by i.id, i.label
order by i.id