I have an Actions table that records the actions taken by the users:
ActionId, UserId
The Actions table holds the name and other details about the action, and Users table similarly holds the name and details about the users.
Now, in my application, I want to inform a user about the actions his friends have taken, something like:
"Person A, Person B, Person C have taken this action."
To do that, I am currently using GROUP_CONCAT() to concat the names of the people like:
SELECT GROUP_CONCAT(name) FROM Users
LEFT JOIN Actions ON Users.UserId = Actions.UserId
WHERE ActionId = '123'
However, I want to personalise this message in two ways.
I want a "..., and " before the last user, so it sounds more natural than a machine generated list.
In cases where the user is also one of the people who have taken this action, I want to display a message like:
"Person A, Person B, Person C and you have taken this action."
What's the most elegant way to achieve this? I am looking for a solution which is efficient enough for a large scale system.