MYSQL select top 5 of a count from table that is grouped by user

Question

I apologize in advance as I may not be describing my problem properly. I am trying to write a query that takes the top 5 most popular chosen_user_items per user and concatenating the top rows per user into a comma separated string via group_concat and then grouping by user_id's.

For example, if user_id of 1 has five rows for item_id of 1, two rows for item_id of 2, three rows for item_id of 3, and a single row for 4 5 and 6, then the result would be 1, 3, 2, 4, 5.

Here is my example table structure.

Name: chosen_user_items

id | user_id | item_id
------------------------
1  | 1       | 1
2  | 1       | 4
3  | 1       | 19
4  | 1       | 10
5  | 1       | 13
.  | 1       | 1
.  | 1       | 11
.  | 1       | 18
.  | 1       | 212
.  | 1       | 654
.  | 2       | 1
.  | 2       | 28
.  | 2       | 568
.  | 2       | 112
.  | 2       | 354
.  | 3       | 4
.  | 3       | 4
.  | 3       | 19
.  | 3       | 212
.  | 3       | 654
.  | 3       | 4
.  | 3       | 4
.  | 3       | 253
.  | 3       | 187
.  | 3       | 212

And here is an example of my desired output:

user_id | group_concat_results
------------------------------
1       | 1, 4, 19, 13, 212
2       | 1, 28, 568, 212, 354
3       | 4, 212, 19, 654, 253

Here is the query that I have so far

SELECT `chosen_user_items`.`item_id`, COUNT(`chosen_user_items`.`item_id`) AS 'item_count' 
FROM `chosen_user_items` 
WHERE `chosen_user_items`.`user_id` = 1
GROUP BY `chosen_user_items`.`item_id` 
ORDER BY `item_count` DESC 
LIMIT 5

While this works great for a single user, I want to be able to run this query just once for all users (in order to avoid doing hundreds or thousands of database queries), and having to manually concat the results in a language such as PHP.

Thanks in advance.

Answer 1

With some sort of ranking it is possible to do it with 1 query.

select user_id, group_concat(item_id) from 
(
select 
  user_id
  ,item_id
  ,@item_rank := if(@current_item = user_id, @item_rank+1,1) as item_rank 
  ,@current_item:=user_id
  from
      (      
      select 
        user_id
        ,item_id
        ,count(*) aantal
      from chosen_user_items
      group by user_id,item_id
      order by user_id,count(*) desc
      ) a )b
      where item_rank <6
      group by user_id

Here's an sqlfiddle to test it.

I only have a problem with the order of the group_concat, it isn't ordered accordingly.

try the following query to see the result before the group_concat maybe you can concat it better.

select 
  user_id
  ,item_id
  ,@item_rank := if(@current_item = user_id, @item_rank+1,1) as item_rank 
  ,@current_item:=user_id
  from
      (      
      select 
        user_id
        ,item_id
        ,count(*) aantal
      from chosen_user_items
      group by user_id,item_id
      order by user_id,count(*) desc
      ) a 

---

this query sorts the concat correctly according to the data in your question:

select user_id, group_concat(item_id) from 
(
select 
  user_id
  ,item_id
  ,@item_rank := if(@current_item = user_id, @item_rank+1,1) as item_rank 
  ,@current_item:=user_id
  from
      (      
      select 
        user_id
        ,item_id
        ,count(*) aantal
      from chosen_user_items
      group by user_id,item_id
      order by user_id,count(*) desc
      ) a )b
      where item_rank <6
      group by user_id
      order by user_id,item_rank asc