用户活动供稿（ala脸书）。如何分组类似的活动？

问题描述

在我正在处理的电影数据库项目中，每个用户都有一个活动供稿。我想分组类似的活动，比如Facebook如何做，但又有点不同。

例如，如果用户为电影写评论，一分钟后他还会投票对于那部电影和5分钟后他上传了一部电影的预告片，我想在活动供稿中一起显示（分组）。

我认为最好的是在查询数据库时对它进行分组，但我不认为它在这种情况下是可能的，因为我使用一个非常大的JOIN来获取所有数据：

  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电影m ON ua.imdbID = m.imdbID 
 LEFT OUTER JOIN个人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 J OIN user_persons_vote upv ON upv.userID = ua.userID AND upv.personID = ua.personID 
 LEFT OUTER JOIN评论r ON r.userID = ua.userID AND r.imdbID = ua.imdbID 
 LEFT OUTER JOIN（
）从预告片
 group中选择imdbID，userID，youTubeID，max（hd）作为MaxTrailerStatus 
 by imdbID 
）t ON ua.imdbID = t.imdbID AND ua.userID = t.userID 
 LEFT OUTER JOIN（
）从海报中选择imdbID，文件名，max（main）作为MaxPosterStatus 
 
 by imdbID 
）ps ON ua.imdbID = ps.imdbID 
 LEFT OUTER JOIN（
）选择personID，文件名，max（main）作为MaxPhotosStatus 
从photos 
 group by personID 
）ph ON ua.personID = ph.personID 
 WHERE ua.userID =？ 
 ORDER BY ua.datetime DESC 
  

我想知道如何通过修改该查询或使用PHP。
Thanks。

解决方案

您可能会对正在修改的项目的项目标识进行分组 - 这种情况下看起来像：ua.imdbID。

一旦你这样做了，你就会失去其他数据行，所以你可以使用GROUP_CONCAT来获取所有相关的ID 。

GROUP_CONCAT（sa.activityID）;

所以你的最终结果是：

  SELECT ua.datetime，ua.imdbID，ua.personID，GROUP_CONCAT（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电影m ON ua.imdbID = m.imdbID 
 LEFT OUTER JOIN个人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（
选择imdbID，userID，youTubeID，max（hd）作为MaxTrailerStatus 
从预告片
组合由imdbID 
 ）t ON ua.imdbID = t.imdbID AND ua.userID = t.userID 
 LEFT OUTER JOIN（
从海报中选择imdbID，文件名，max（main）作为MaxPosterStatus 
 
组由imdbID 
）ps ON ua.imdbID = ps.imdbID 
 LEFT OUTER JOIN（
）选择personID，文件名，max（主）作为MaxPhotosStatus 
从照片
按人物ID 
）ph ON ua.personID = ph.personID 
 WHERE ua.userID =？ GROUP BY ua.imdbID 
 
 
 ORDER BY ua.datetime DESC 
  

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.

解决方案

You'd likely group on the item id of the item being modified - which in this case looks like: ua.imdbID.

Once you do that, you'll lose out on the other rows of data, so you'd use GROUP_CONCAT to get all related IDs.

GROUP_CONCAT(sa.activityID);

So your end result would be:

SELECT ua.datetime, ua.imdbID, ua.personID, GROUP_CONCAT(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 = ?  GROUP BY ua.imdbID


ORDER BY ua.datetime DESC

这篇关于用户活动供稿（ala脸书）。如何分组类似的活动？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！