Quindi sto cercando di sviluppare un feed di attività per il mio sito. Fondamentalmente If I UNION
un gruppo di attività in un feed finirei con qualcosa di simile al seguente.
Chris is now friends with Mark
Chris is now friends with Dave
Ciò che voglio è un modo più ordinato per raggruppare questi post simili in modo che il feed non dia un sovraccarico di informazioni ...
per es.
Chris is now friends with Mark, Dave and 4 Others
Qualche idea su come posso affrontare questo logicamente? Sto usando ASP classico sul server SQL. Ecco la dichiarazione UNION
che ho finora:
SELECT
U.UserID As UserID, L.UN As UN,Left(U.UID,13) As ProfilePic,U.Fname
+ ' '
+ U.Sname As FullName, 'said '
+ WP.Post AS Activity, WP.Ctime
FROM
Users AS U LEFT JOIN Logins L ON L.userID = U.UserID
LEFT OUTER JOIN
WallPosts AS WP ON WP.userID = U.userID WHERE WP.Ctime IS NOT NULL
UNION SELECT
U.UserID As UserID, L.UN As UN,Left(U.UID,13) As ProfilePic,U.Fname
+ ' '
+ U.Sname As FullName, 'commented '
+ C.Comment AS Activity, C.Ctime
FROM Users AS U
LEFT JOIN Logins L ON L.userID = U.UserID
LEFT OUTER JOIN
Comments AS C ON C.UserID = U.userID WHERE C.Ctime IS NOT NULL
UNION SELECT
U.UserID As UserID, L.UN As UN,Left(U.UID,13) As ProfilePic, U.Fname
+ ' '
+ U.Sname As FullName, 'connected with <a href="/profile.asp?un='+(SELECT Logins.un FROM Logins WHERE Logins.userID = Cn.ToUserID)+'">'
+ (SELECT Users.Fname
+ ' '
+ Users.Sname FROM Users WHERE userID = Cn.ToUserID)
+ '</a>' AS Activity, Cn.Ctime
FROM
Users AS U
LEFT JOIN
Logins L ON L.userID = U.UserID
LEFT OUTER JOIN
Connections AS Cn ON Cn.UserID = U.userID WHERE CN.Ctime IS NOT NULL