3

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?

2

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
  • @Oskar, do you run additional mysql queries through PHP, when you parse the concatenated result, e.g. usernames from Erwin's sqlfiddle?. – John Smith Dec 16 '15 at 11:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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