3

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
  • 2
    Are you looking for help crafting your SQL query, or are you looking for high-level design help? – user8 Nov 26 '11 at 21:12
  • High level design help really its the logic I cannot get my head around... – Chris Dowdeswell Nov 27 '11 at 11:32
1

My suggestion is to use a subquery to select only those messages and to group them: as long as all the messages begin with the same string ("Chris is not a friend with") you can easily select them and extract the friends' names. Then you just have to concatenate those and add it to your personal webpage.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.