Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

On my website I'm building an activity feed where I got the basics done and are now making some improvements here and there.

One of them is the grouping. I've built it so that if multiple users buys the same 2 or more products the activity feed shows it as one activity: "user1 & user2 bought product 1, 2 & 3".

The problem is that if user1 buys product 1 & 2 and user2 buys product 1, 2 & 3 it appears as two different activities: "user1 bought product 1 & 2" AND "user2 bought product 1, 2 & 3".

I would like it to show as "user1 & user2 bought product 1 & 2" and then another saying "user2 bought product 3"

I've made a simplified sqlfiddle of what I've got so far. Try changing it by deleting NULL, 'Bought', 1, 3

Is this something that is possible to do in MySQL or should I do it in PHP?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

You need to group users by activity and product in the first step, and then group products by the groups of users.
I use ORDER BY with GROUP_CONCAT(), so that the aggregated usernames are concatenated in the same order.

SELECT x.usernames
      ,x.activity
      ,GROUP_CONCAT(p.product_name ORDER BY p.product_name) AS product_names
FROM   products p
JOIN  (
   SELECT a.activity
         ,a.productId
         ,GROUP_CONCAT(u.name ORDER BY u.name) AS usernames
   FROM   activities a
   LEFT   JOIN users u ON u.id = a.userId
   GROUP  BY 1, 2
   ) x ON x.productId = p.id
GROUP  BY 1, 2
ORDER  BY 1, 2;

-> sqlfiddle

Returns:

USERNAMES  | ACTIVITY   | PRODUCT_NAMES
-----------+------------+------------------------------
John       | Bought     | The Dark Knight Rises
John,Peter | Bought     | Batman Begins,The Dark Knight
share|improve this answer
    
Amazing, thanks! :) –  Oskar Persson Jan 7 '13 at 12:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.