Databases
StackOverflow2013
Postsdbo
POHow can I improve this PHP/MySQL news feed?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow can I improve this PHP/MySQL news feed?
    primarykey
    data
    text
    <p>Let me start right off the bat by saying that I know this is not the best solution. I know it's kludgy and a hack of a feature. <em>But that's why I'm here!</em></p> <p>This question/work builds off <a href="http://www.quora.com/What-are-best-practices-for-building-something-like-a-News-Feed" rel="noreferrer">some discussion on Quora with Andrew Bosworth</a>, creator of Facebook's news feed.</p> <p><strong>I'm building a news feed</strong> of sorts. It's built solely in <code>PHP</code> and <code>MySQL</code>.</p> <p><img src="https://i.stack.imgur.com/KPvit.gif" alt="alt text"></p> <hr> <h2>The MySQL</h2> <p>The relational model for the feed is composed of two tables. One table functions as an activity log; in fact, it's named <code>activity_log</code>. The other table is <code>newsfeed</code>. <em>These tables are nearly identical.</em></p> <p>The <strong>schema for the log</strong> is <code>activity_log(uid INT(11), activity ENUM, activity_id INT(11), title TEXT, date TIMESTAMP)</code></p> <p>...and the <strong>schema for the feed</strong> is <code>newsfeed(uid INT(11), poster_uid INT(11), activity ENUM, activity_id INT(11), title TEXT, date TIMESTAMP)</code>.</p> <p><strong>Any time a user does something</strong> relevant to the news feed, for example asking a question, <strong>it will get logged to the activity log</strong> immediately.</p> <hr> <h2>Generating the news feeds</h2> <p>Then <strong>every X minutes</strong> (5 minutes at the moment, will change to 15-30 minutes later), <strong>I run a cron job</strong> that executes the script below. This script loops through all of the users in the database, finds all the activities for all of that user's friends, and then writes those activities to the news feed.</p> <p>At the moment, the <code>SQL</code> that culls the activity (called in <code>ActivityLog::getUsersActivity()</code>) has a <code>LIMIT 100</code> imposed for performance* reasons. *Not that I know what I'm talking about.</p> <pre><code>&lt;?php $user = new User(); $activityLog = new ActivityLog(); $friend = new Friend(); $newsFeed = new NewsFeed(); // Get all the users $usersArray = $user-&gt;getAllUsers(); foreach($usersArray as $userArray) { $uid = $userArray['uid']; // Get the user's friends $friendsJSON = $friend-&gt;getFriends($uid); $friendsArray = json_decode($friendsJSON, true); // Get the activity of each friend foreach($friendsArray as $friendArray) { $array = $activityLog-&gt;getUsersActivity($friendArray['fid2']); // Only write if the user has activity if(!empty($array)) { // Add each piece of activity to the news feed foreach($array as $news) { $newsFeed-&gt;addNews($uid, $friendArray['fid2'], $news['activity'], $news['activity_id'], $news['title'], $news['time']); } } } } </code></pre> <hr> <h2>Displaying the news feeds</h2> <p>In the client code, when fetching the user's news feed, I do something like:</p> <pre><code>$feedArray = $newsFeed-&gt;getUsersFeedWithLimitAndOffset($uid, 25, 0); foreach($feedArray as $feedItem) { // Use a switch to determine the activity type here, and display based on type // e.g. User Name asked A Question // where "A Question" == $feedItem['title']; } </code></pre> <hr> <h2>Improving the news feed</h2> <p>Now forgive my limited understanding of the best practices for developing a news feed, but I understand the approach I'm using to be a limited version of what's called <strong>fan-out on write</strong>, limited in the sense that I'm running a cron job as an intermediate step instead of writing to the users' news feeds directly. But this is very different from a pull model, in the sense that the user's news feed is not compiled on load, but rather on a regular basis.</p> <p>This is a large question that probably deserves a large amount of back and forth, but I think it can serve as a touchstone for many important conversations that new developers like myself need to have. I'm just trying to figure out what I'm doing wrong, how I can improve, or how I should maybe even start from scratch and try a different approach.</p> <p>One other thing that bugs me about this model is that it works based on recency rather than relevancy. If anyone can suggest how this can be improved to work relevancy in, I would be all ears. I'm using Directed Edge's API for generating recommendations, but it seems that for something like a news feed, recommenders won't work (since nothing's been favorited previously!).</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJosh Smith
    1. USJosh Smith
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow can I improve this PHP/MySQL news feed?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow can I improve this PHP/MySQL news feed?
      1. USGabrielC
      1. VTFavorite
    3. VO
      singulars
      1. POHow can I improve this PHP/MySQL news feed?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat? No humorous image attached this time? You can do better than that! :P
      singulars
      1. POHow can I improve this PHP/MySQL news feed?
      1. USalex
    2. CO@alex Oh, man, you're right. I must be getting old.
      singulars
      1. POHow can I improve this PHP/MySQL news feed?
      1. USJosh Smith
    3. CO@Josh [Try this](http://www.timtim.com/public/images/drawings/large/Breakfast_News.gif) :)
      singulars
      1. POHow can I improve this PHP/MySQL news feed?
      1. USalex
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload