gpt4 book ai didi

php - 用户事件提要(ala facebook)。如何对相似的事件进行分组?

转载 作者:行者123 更新时间:2023-11-29 05:44:09 24 4
gpt4 key购买 nike

在我正在处理的电影数据库项目中,每个用户都有一个事件提要。我想将类似的事件归为一组,例如 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 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

我想知道如何通过修改查询或使用 PHP 来实现。谢谢。

最佳答案

您可能会根据正在修改的项目的项目 ID 进行分组 - 在本例中类似于: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 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

关于php - 用户事件提要(ala facebook)。如何对相似的事件进行分组?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/4115337/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com