Activity stream / News feed

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
ed209
Forum Contributor
Posts: 153
Joined: Thu May 12, 2005 5:06 am
Location: UK

Activity stream / News feed

Post by ed209 »

popularised by Facebook, the idea of an "activity stream" on a users dashboard is actually a really great tool - so I'd like to build one for my own site :D

I have comments, ratings, blog posts, media posts, connecting with users etc etc on my site. Obviously, each feature has it's own set of tables, classes and methods. How would I go about creating an activity stream featuring all of these things for the entities that the user owns, but also the the users friend owns? Here are a couple of options:

1. just loop through all the users friends and request that data from lots of tables - lots of sub-queries to get appropriate media, names etc.
Pro: fresh, unduplicated data. Con: heavy processing - even if we cache the results.

2. query as above, but store each users feed in a file (xml or something). Run a cron job to regularly update the file, e.g every 12hrs. Then on viewing the stream, request and merge all relevant XML files.
Pro: easy format to use in other applications (like RSS feed) Cons: one hell of a cron job to be constantly running!

3. duplicate the data into a "feeds" table. For example, as a comment is written, as well as storing it to the comment table, store that data in a "feeds" table. I think elgg does something similar to this.
Pro: easy SQL lookup for all activity Con: duplicate data, got to make the storage generic enough to handle any type of activity.

Any thoughts, methods, projects that could help me find the best solution?

Thanks.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Re: Activity stream / News feed

Post by Jenk »

Number 1. It could be accomplished in one query. "Select activities from table where user in (select friends from users where user = me)"
User avatar
ed209
Forum Contributor
Posts: 153
Joined: Thu May 12, 2005 5:06 am
Location: UK

Re: Activity stream / News feed

Post by ed209 »

would that sub-select be a little to heavy on processing (I tried something similar once for a different function)? Let me give you an example, say I have 49 friends :roll: including me that's 50 people I'd like to get that data for. That data would consist of getting rows from:

comment table
"person A commented on Friend 1's profile" or "Friend 22 comment on SomeUser's photo".
media table
"Friend 35 added a new photo"
rate table
"Friend 12 rated this photo title Something" or "Someone rated Friend 41's photo"
friends table
"Friend 23 is now friends with Joe Bloggs"
favourites table
"Friend 45 added This Photo to their favourites" or "Friend 18 was added as a favourite by Someguy" or "Friend 29's photo was added as a favourite by AnotherUser"
(and a couple of other tables too)
etc etc etc

On top of that, I would need to get the corresponding data for each entity (where an entity is the thing commented on / favourited etc - e.g. a profile, or an artwork, or event). That would include selects for the images and other media.

I'm kind of leaning towards 3 myself. I could do:

1. SELECT all my friends
2. SELECT * FROM activities table WHERE entity_owner_user_id IN (......)
3. Loop through returned rows
SELECT entity_data for current row (e.g. get photo or get event)
User avatar
Inkyskin
Forum Contributor
Posts: 282
Joined: Mon Nov 19, 2007 10:15 am
Location: UK

Re: Activity stream / News feed

Post by Inkyskin »

I have just added this to a site im building too. Mine is as simple as looping though a buddy list table, and getting the latest activity from the table if they match the buddy list.
User avatar
ed209
Forum Contributor
Posts: 153
Joined: Thu May 12, 2005 5:06 am
Location: UK

Re: Activity stream / News feed

Post by ed209 »

Inkyskin wrote:and getting the latest activity from the table
how are you storing that latest activity? Do you have a separate table where you add each activity (effectively duplicating activity data) or does each new activity reside in its own table (i.e comments in comment table, event in events table)
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Re: Activity stream / News feed

Post by Jenk »

It all depends on your method of storing activities..

Activity table:
userId Integer
activity_string varChar(150)
activity_link varChar(200)

Friends table:
userId Integer
friendId Integer

Query:

Code: Select all

 
SET userId = <my_user_id>;
SELECT activity.activity_string, activity.activity_link FROM activity WHERE activity.userId IN (
  SELECT friends.friendId FROM friends WHERE friends.userId = @userID);
User avatar
ed209
Forum Contributor
Posts: 153
Joined: Thu May 12, 2005 5:06 am
Location: UK

Re: Activity stream / News feed

Post by ed209 »

So, store all of the activity in an activity table (that's the way I'm leaning)?

Example entry in my activity table for a comment on a photo (based on your post):

"34" :entity_owner_id (the user id of the person that owns the photo)
"56" :activity_user_id (the user id of the person who left the comment)
"www.site.com/example/photo/#comment7" :activity_URL (url to the place where that activity exists - probably won't store as URL though)
Joe Bloggs(link to profile) comment(link to comment) on A Sunset By The Beach(link to photo) by Mike Night(link to profile) : activity description

I'm not sure that storing the activity description (essentially as HTML) is such a good idea - what do you think? I guess I could store a custom entity name for each activity type, so replace the above 'activity description' with

"COMMENT" :activity type
"PHOTO" :entity type (other examples PROFILE, EVENT)
"4434" :entity_id (where the activity took place)

then generate the description on output?
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Re: Activity stream / News feed

Post by Jenk »

Or use another link table to store the activity strings.
You might also want to expand the activity table to include a column for whom the activity was against, in an example like "userA slaps userB"

userId would be userA, whilst the "victim" would be userB and of course the string would be "slaps"
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: Activity stream / News feed

Post by Kieran Huggins »

Incidentally, Facebook writes to the "news feed" when the activity happens. So if you have a block of code that writes to a friend's wall, for instance, that's when they write that to your activity log. Then displaying your log is a very simple query.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Re: Activity stream / News feed

Post by Jenk »

To be semantic, Facebook doesn't "push" data to the user agent, they have polling xmlhttprequests in constant time delay loops.
User avatar
ed209
Forum Contributor
Posts: 153
Joined: Thu May 12, 2005 5:06 am
Location: UK

Re: Activity stream / News feed

Post by ed209 »

this is great stuff, thanks. Jenk, could you dumb it down a bit? :oops: I guess I'm not so concerned with the way the data will be called, more the way the data should be stored, but in facebooks case, are you saying that they have some sort of cron type job using XMLHttpRequest to process that data offline? If so where is that data cached/stored?
Kieran Huggins wrote:Incidentally, Facebook writes to the "news feed" when the activity happens. So if you have a block of code that writes to a friend's wall, for instance, that's when they write that to your activity log. Then displaying your log is a very simple query.
That's what I intend to do. With comments as an example, at the time that I write to the comment table, I would also write to the "activity stream" table.

Taking Jenk's example a little further:

"userA slaps userB on the bum"

userA = activity_user_id;
userB = entity_owner_id;
the bum = entity_id; // would record an ID here, i.e. a photoID as fk to the photos table
BODYPART = entity_type; // Photo, Profile, i.e. where the activity took place
slaps = activity_type; // comments, rates, makes friends with, adds photo etc

using that info I can then create the above sentence with hyperlinking to userA's profile, userB's profile and the bum page (where the bum would more likely be a video or photo or something). Does that sound like a reasonable approach? Any issues with duplicate data?
Post Reply