title: Mysql query of Facebook style wall
body: <p>I want to create Members Wall (facebook style) in my website.
  Based on:</p>
  
  <ul>
  <li>Last comments of member's friends.</li>
  <li>Last files of member's friends.</li>
  <li>Last comments of member's files.</li>
  </ul>
  
  <p>Tables structures:</p>
  
  <pre><code>friendship
  userid | friendid | status
  ---------------------------------
  user_table
  userid | username
  ---------------------------------
  files_table
  file_id | file_title | file_desc | dl_date
  ---------------------------------
  comments_table
  comid | userid | file_id | com_date
  </code></pre>
  
  <p>This is my mysql query and it works. But i want to know your ideas to make it better.</p>
  
  <pre><code>--Last files of member's friends
  -- Find files of friendship.friendid=$userid
  SELECT files_table.file_id AS c1, files_table.file_title AS c2, files_table.file_desc AS c3, files_table.dlauthor AS c4, files_table.dl_date AS date,  IF(files_table.file_id IS NOT NULL, 'Friends_eBooks',FALSE) as Type
      FROM friendship
      LEFT JOIN user_table ON friendship.userid = user_table.userid
      LEFT JOIN files_table ON files_table.dlauthor = user_table.username
      WHERE friendship.friendid = $userid
      AND friendship.STATUS = '1'
  -- Find files of friendship.userid=$userid
  UNION
  SELECT files_table.file_id AS c1, files_table.file_title AS c2, files_table.file_desc AS c3, files_table.dlauthor AS c4, files_table.dl_date AS date,  IF(files_table.file_id IS NOT NULL, 'Friends_eBooks',FALSE) as Type
      FROM friendship
      LEFT JOIN user_table ON friendship.userid = user_table.userid
      LEFT JOIN files_table ON files_table.dlauthor = user_table.username
      WHERE friendship.userid = $userid
      AND friendship.STATUS = '1'
  UNION ALL
  -- Last comments of member's friends
  -- Find comments of friendship.friendid=$userid
  SELECT comments_table.comid AS c1, user_table.username AS c2, comments_table.dl_comment AS c3, comments_table.file_id AS c4, comments_table.com_date AS date, IF(comments_table.comid IS NOT NULL, 'Friends_Comments', FALSE) as Type
      FROM friendship
      LEFT JOIN user_table ON friendship.userid = user_table.userid
      LEFT JOIN comments_table ON user_table.userid = comments_table.userid
      WHERE friendship.friendid = $userid
      AND friendship.STATUS = '1'
  UNION
  -- Find comments of friendship.userid=$userid
  SELECT comments_table.comid AS c1, user_table.username AS c2, comments_table.dl_comment AS c3, comments_table.file_id AS c4, comments_table.com_date AS date, IF(comments_table.comid IS NOT NULL, 'Friends_Comments', FALSE) as Type
      FROM friendship
      LEFT JOIN user_table ON friendship.friendid = user_table.userid
      LEFT JOIN comments_table ON user_table.userid = comments_table.userid
      WHERE friendship.userid = $userid
      AND friendship.STATUS = '1'
  -- Last comments on member's files
  UNION ALL
  SELECT comments_table.comid AS c1,user_table.username AS c2,comments_table.dl_comment AS c3, files_table.file_id AS c4,comments_table.com_date AS date, IF(comments_table.comid IS NOT NULL, 'My_Comments', FALSE) as Type
  FROM files_table
  LEFT JOIN comments_table ON files_table.file_id = comments_table.file_id
  LEFT JOIN user_table ON comments_table.userid = user_table.userid
  WHERE
  (files_table.status=1) AND
  (files_table.dlauthor=$userid)
  ORDER by date DESC
  </code></pre>
  
  <p>Explain:</p>
  
  <pre><code>id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
  1   PRIMARY     friendship  ref     friendid    friendid    3   const   22  Using where
  1   PRIMARY     user_table  eq_ref  PRIMARY     PRIMARY     4   ketabnak_ebooks.friendship.userid   1    
  1   PRIMARY     files_table     ref     dlauthor    dlauthor    92  func    10   
  2   UNION   friendship  ref     PRIMARY     PRIMARY     3   const   11  Using where
  2   UNION   user_table  const   PRIMARY     PRIMARY     4   const   1    
  2   UNION   files_table     ref     dlauthor    dlauthor    92  func    10   
  3   UNION   friendship  ref     friendid    friendid    3   const   22  Using where
  3   UNION   user_table  eq_ref  PRIMARY     PRIMARY     4   ketabnak_ebooks.friendship.userid   1    
  3   UNION   comments_table  ref     userid  userid  3   ketabnak_ebooks.user_table.userid   6    
  4   UNION   friendship  ref     PRIMARY     PRIMARY     3   const   11  Using where
  4   UNION   user_table  eq_ref  PRIMARY     PRIMARY     4   ketabnak_ebooks.friendship.friendid     1    
  4   UNION   comments_table  ref     userid  userid  3   ketabnak_ebooks.user_table.userid   6    
  5   UNION   files_table     ref     dlauthor    dlauthor    92  const   294     Using where
  5   UNION   comments_table  ref     file_id     file_id     3   ketabnak_ebooks.files_table.file_id     11   
  5   UNION   user_table  eq_ref  PRIMARY     PRIMARY     4   ketabnak_ebooks.comments_table.userid   1    
  NULL    UNION RESULT    &lt;union1,2,3,4,5&gt;    ALL     NULL    NULL    NULL    NULL    NULL    Using filesort
  </code></pre>
  
  <p>Weaknesses:</p>
  
  <ul>
  <li><p>Query took a lot of time.</p></li>
  <li><p>Number of columns of all tables should be equal.</p></li>
  </ul>
  
  <hr>
  
  <p>I have used <code>Friendship</code> table to find all of member's friends.
  <code>Status</code> is 1 when member's friend has been accepted friendship request.</p>
  
  <p>First, Userid = Memberid:</p>
  
  <pre><code>Userid   | Friendid    | Status
  1        |      4      |     0
  1        |      8      |     1
  1        |      9      |     1
  </code></pre>
  
  <p>Then, i have used UNION to merge where Friendid = Memberid:</p>
  
  <pre><code>Userid   | Friendid    | Status
  2        |      1      |     0
  3        |      1      |     0
  5        |      1      |     1
  </code></pre>