Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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
share|improve this question

migrated from webapps.stackexchange.com Nov 27 '11 at 13:17

This question came from our site for power users of web applications.

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
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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