I am in the process of creating a news feed. The news feed will say something like… You have just updated your profile picture. Every time a user updates their profile picture, a news feed row is added to the news feed.
The problem:
If a user uploads their picture three times in a row it will say…
You have just updated your profile picture.
You have just updated your profile picture.
You have just updated your profile picture.
Which looks terrible. I can adjust the group by to concatenate the similar rows into one row, but then if I ever update my profile picture again, even when I don’t have repeated actions it will continue to group those actions into one row.
Solution I need:
If the actions above or below a give row are the same, then group them together into one row. If not don’t group them.
Current Model:
You have just updated your profile picture.
You have just updated your profile picture.
You have just joined XYZ company.
You have just updated your profile picture.
You have just updated your profile picture.
New Result I Need:
You have just updated your profile picture.
You have just joined XYZ company.
You have just updated your profile picture.
Current Query:
SELECT
users.id,
users.firstname,
users.lastname,
users.username,
companies.companyid AS companyid,
companies.industry AS industry,
companies.stage AS stage,
companies.companytag AS companytag,
‘’ AS gender,
‘’ AS accounttype,
companies.country,
companies.state,
companies.city,
UNIX_TIMESTAMP(company_actions.time) AS approve_date,
‘’ AS FeedId,
companies.companyname AS FeedFirstName,
‘’ AS FeedLastName,
‘’ AS FeedUserName,
GROUP_CONCAT(actions.actionsdescription SEPARATOR ‘~’) AS action_id,
GROUP_CONCAT(company_actions.details SEPARATOR ‘~’) AS details
FROM users
INNER JOIN employees ON employees.userid = users.id AND users.accounttype IN (“.implode(‘,’,$user_types).”)
INNER JOIN companies ON employees.companyid = companies.companyid
INNER JOIN company_actions ON company_actions.company_id = companies.companyid
INNER JOIN actions ON company_actions.action_id = actions.id
WHERE company_actions.time < ‘${time}’
GROUP BY
users.id,
users.firstname,
users.lastname,
users.username,
companyid,
industry,
stage,
companytag,
gender,
accounttype,
companies.country,
companies.state,
companies.city,
approve_date,
FeedId,
FeedFirstName,
FeedLastName,
FeedUserName
What I probably need is a way to group actions over a time interval. If the same action occurs within 5 minutes of the first one, then group by.