Mysql选择查询获取当前用户帖子和关注的朋友帖子 [英] Mysql select query for getting current user post and followed friend post

问题描述

我当前的选择查询语句:

My current select query statement:

 SELECT
    T.postID, 
    T.message, 
    T.time,
    U.userID,
    U.name, 
    U.username, 
    U.picture_url
 FROM
    post AS T,
    users AS U 
 WHERE
    T.postID = '$uid' //$uid holds the id of the current logged in user
    order by T.postID DESC;

我的选择查询输出当前登录用户的帖子，但我想输出当前登录用户的关注用户的帖子，我该怎么做?

My select query outputs the post of the current logged in user but I want to output the post of the followed user of the current logged in user as well, how can I do it?

用户表:

+------------+------------+------+-----+---------+----------------+
| Field      | Type       | Null | Key | Default | Extra          |
+------------+------------+------+-----+---------+----------------+
| userID     |int(11)     | NO   | PRI | NULL    | auto_increment |
| name       |VARCHAR(60) | NO   |     | NULL    |                |
| username   |VARCHAR(20) | NO   |     | NULL    |                |
| picture_url|VARCHAR(200)| NO   |     | NULL    |                |
+------------+------------+------+-----+---------+----------------+

发布表:

+------------+------------+------+-----+---------+----------------+
| Field      | Type       | Null | Key | Default | Extra          |
+------------+------------+------+-----+---------+----------------+
| postID     |int(11)     | NO   | PRI | NULL    | auto_increment |
| pUserID    |int(11)     | NO   |     | NULL    |                |
| message    |VARCHAR(140)| NO   |     | NULL    |                |
| time       |datetime    | NO   |     | NULL    |                |
+------------+------------+------+-----+---------+----------------+

关注用户表

+------------+------------+------+-----+---------+----------------+
| Field      | Type       | Null | Key | Default | Extra          |
+------------+------------+------+-----+---------+----------------+
| fid        |int(11)     | NO   | PRI | NULL    | auto_increment |
| userId1    |int(11)     | NO   |     | NULL    |                |
| userId2    |int(11)     | NO   |     | NULL    |                |
+------------+------------+------+-----+---------+----------------+

推荐答案

首先建议使用显式 JOINS 超过 隐式 CROSS JOIN

First it is recommended to use explicit JOINS over implicit CROSS JOIN

尝试以下查询.

 SELECT
    T.postID, 
    T.message, 
    T.time,
    U.userID,
    U.name, 
    U.username, 
    U.picture_url,
    F.userID2,
 FROM
    users AS U  
 INNER JOIN 
    follow_user AS F
 ON U.userID = F.userId1    
 INNER JOIN 
    post AS T
 ON T.pUserID = U.userID OR T.pUserID = F.userId2 
 WHERE
    U.userID = '$uid' //$uid holds the id of the current logged in user
    order by T.postID DESC;

这篇关于Mysql选择查询获取当前用户帖子和关注的朋友帖子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！