In a movies DB project I'm working on, each user have an activity feed. I want to group similar activities, like how Facebook does it but little different.
For example if an user writes a review for a movie, and one minute later he also votes for that movie and 5 minutes later he upload a trailer for that movie, I want to show it together (grouped) in the activity feed.
I think the best is to group it when querying the database but I don't think its possible in this case because I use a really big JOIN to get all the data:
SELECT ua.datetime, ua.imdbID, ua.personID, sa.activityID, sa.activity, m.title, m.year, p.name, umv.vote, upv.vote, r.review, t.youTubeID, ps.filename, ph.filename
FROM user_activity ua
JOIN sys_activities sa ON ua.activityID = sa.activityID
LEFT OUTER JOIN movies m ON ua.imdbID = m.imdbID
LEFT OUTER JOIN persons p ON ua.personID = p.personID
LEFT OUTER JOIN user_movies_vote umv ON umv.userID = ua.userID AND umv.imdbID = ua.imdbID
LEFT OUTER JOIN user_persons_vote upv ON upv.userID = ua.userID AND upv.personID = ua.personID
LEFT OUTER JOIN reviews r ON r.userID = ua.userID AND r.imdbID = ua.imdbID
LEFT OUTER JOIN (
select imdbID, userID, youTubeID, max(hd) as MaxTrailerStatus
from trailers
group by imdbID
) t ON ua.imdbID = t.imdbID AND ua.userID = t.userID
LEFT OUTER JOIN (
select imdbID, filename, max(main) as MaxPosterStatus
from posters
group by imdbID
) ps ON ua.imdbID = ps.imdbID
LEFT OUTER JOIN (
select personID, filename, max(main) as MaxPhotosStatus
from photos
group by personID
) ph ON ua.personID = ph.personID
WHERE ua.userID = ?
ORDER BY ua.datetime DESC
I want to know how to do it either by modifying the query or using PHP. Thanks.