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
Below is my final working solution. It turns out that moving this...
date_modified = (CASE
WHEN name <> values(name)
OR description <> values(description)
OR status <> values(status)
OR type <> values(type)
OR priority <> values(priority)
THEN UTC_TIMESTAMP()
ELSE date_modified
END),
to the top of the list, above these fields...
name='$name',
description='$description',
status='$status',
priority='$priority',
type='$type',
So the final solution looks like this below...
$sql = "
INSERT INTO
$this->tasksDbTableName(task_id, project_id, name, description, status, priority, type, date_entered, date_modified, sort_order, heading)
VALUES
('$taskId', '$projectId', '$name', '$description', '$status', '$priority', '$type', UTC_TIMESTAMP(), UTC_TIMESTAMP(), '$sort_order', '$heading')
ON DUPLICATE KEY UPDATE
date_modified = (CASE
WHEN name <> values(name)
OR description <> values(description)
OR status <> values(status)
OR type <> values(type)
OR priority <> values(priority)
THEN UTC_TIMESTAMP()
ELSE date_modified
END),
name='$name',
description='$description',
status='$status',
priority='$priority',
type='$type',
sort_order='$sort_order',
heading='$heading'";
Just moving my CASE Statement
to the top above the other set fields, made it start working correctly!
It almost seems as if these DB column fields name
description
status
priority
type
were getting Updated before my Case statement could run which resulted in them always appearing to be the same as the DB fields and never any different so my Case statement was about useless. Moving it to the top, it now works 100% correctly.
Hopefully this will help someone with a similar problem someday as many people across 3 sites tried to fix it without success. Just moving it to the top did the trick though so the Order in this case very much matters!
]
Thanks to all that have contributed their time to try and find a solution, I always appreciate the help I receive on the StackExchange network sites!
Best Answer
It is bad practice to have "arrays" stored as columns.
Instead, have another table, delete the row with 'U2', and number the entries while SELECTing WHERE ... IS NOT NULL.