Programming logic to group a users activities like facebook. E.g. Chris is now friends with A, B and C

Posted by Chris Dowdeswell on Programmers See other posts from Programmers or by Chris Dowdeswell
Published on 2011-11-26T16:44:11Z Indexed on 2011/11/27 18:04 UTC
Read the original article Hit count: 402

Filed under:
|

So I am trying to develop an activity feed for my site,

Basically If I UNION a bunch of activities into a feed I would end up with something like the following.

Chris is now friends with Mark

Chris is now friends with Dave

What I want though is a neater way of grouping these similar posts so the feed doesn't give information overload...

E.g.

Chris is now friends with Mark, Dave and 4 Others

Any ideas on how I can approach this logically?

I am using Classic ASP on SQL server.

Here is the UNION statement I have so far...

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

© Programmers or respective owner

Related posts about social-networks

Related posts about database