Facebook风格墙的Mysql查询

Question

我想在我的网站上创建会员墙（facebook 风格）。 基于：

• 会员朋友的最新评论。
• 会员朋友的最后档案。
• 成员文件的最后评论。

表结构：

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

这是我的 mysql 查询，它有效。但我想知道你的想法，以使其变得更好。

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

说明：

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    <union1,2,3,4,5>    ALL     NULL    NULL    NULL    NULL    NULL    Using filesort

弱点：

• 查询花费了很多时间。

• 所有表的列数应相等。

---

我使用 Friendship 表来查找成员的所有朋友。 当会员的好友已接受好友请求时，Status 为 1。

首先，Userid = Memberid：

Userid   | Friendid    | Status
1        |      4      |     0
1        |      8      |     1
1        |      9      |     1

然后，我使用 UNION 进行合并，其中 Friendid = Memberid：

Userid   | Friendid    | Status
2        |      1      |     0
3        |      1      |     0
5        |      1      |     1

Answer 1

让它变得更好的一个想法是停止认为您必须在单个查询中完成所有操作。

Answer 2

可能有人有更好的主意，当然，总有人比你聪明，无论如何我想分享这个方法，这是我一年前做的事情并为我工作，这是为 symfony 1.4 做的，不需要知道symfony 1.4，这是不言自明的。

public function getAllEvents($uid) {

    $result = Doctrine_Query::create()->from('AquilaWall w')->where('w.uid = ? && w.is_public=1 ', $uid)->execute();
    $array_uids = array();

    foreach ($result as $uid_record){
        array_push($array_uids, $uid_record->getUid());
        array_push($array_uids, $uid_record->getUids());
    }

    $array_uids = array_unique($array_uids);
    $array_uids = array_filter($array_uids);
    $array_uids = implode(",",$array_uids);

    $mdarray=array();
    $flagg=0;
    $rowcount= $result -> count();

    if ($rowcount>0) {
      // init arrays
      $arr_wall_videos = $arr_wall_comments = $arr_wall_news = $arr_wall_faqs =array();

      $num_videos = $this->getNumberOf("AquilaVideos", $uid);
      if ($num_videos>0) {
        $query = "SELECT * FROM aquila_videos WHERE uid IN ($array_uids) ";
        $arr_wall_videos = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAssoc($query);
        $flagg=1;
      }


      $num_news = $this->getNumberOf("AquilaNews", $uid);
      if ($num_news>0) {
        $query = "SELECT * FROM aquila_news WHERE uid IN ($array_uids) ";
        $arr_wall_news = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAssoc($query);
        $flagg=1;
      }

      $num_faqs = $this->getNumberOf("sfFaqFaq", $uid);
      if ($num_faqs>0) {
        $query = "SELECT  faq.uid, faq.id, faq.created_at, faq.question, faq.slug, cat.name FROM sf_faq_faq faq INNER JOIN sf_faq_category cat on cat.id=faq.category_id WHERE faq.uid IN ($array_uids)";
        $arr_wall_faqs = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAssoc($query);
        $flagg=1;
      }

      $num_comments = $this->getNumberOf("Comment", $uid);
      if ($num_comments>0) {
        $query = "SELECT * FROM comment WHERE user_id IN ($array_uids) ";
        $arr_wall_comments = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAssoc($query);
        $flagg=1;
      }

      if ($flagg) {
        $mdarray = array_merge((array)$arr_wall_videos, (array)$arr_wall_faqs);
        $mdarray = array_merge((array)$mdarray, (array)$arr_wall_news);
        $mdarray = array_merge((array)$mdarray, (array)$arr_wall_comments);

        foreach ($mdarray as $key => $row) {
          $dates[$key]  = $row['created_at']; 
        }
        array_multisort($dates, SORT_DESC, $mdarray);
      }
    }

    return $mdarray;

  }