php - 组合两个 MYSQL 查询以仅获取好友的帖子

Question

我有一个查询来获取用户的好友，还有一个查询来获取用户作为状态发布的所有帖子，现在我需要将它们合并起来。

friend 们:

SELECT CASE WHEN userID=$session THEN userID2 ELSE userID END AS friendID 
FROM friends 
WHERE userID=$session OR userID2=$session

然后用户发帖:

SELECT * FROM posts 
WHERE toID=fromID AND state='0' 
ORDER BY id DESC LIMIT 10

所以两者都应该一起工作。第一个查询中的friendID 应等于ID 或fromID，哪个并不重要，因为它们都需要相同。

Answer 1

您发布帖子的第二个条件是它们来自 friend 。所以:

SELECT * FROM posts  
WHERE toID=fromID AND state='0' AND toID IN
    (SELECT CASE WHEN userID=$session THEN userID2 ELSE userID END AS friendID  
     FROM friends  
     WHERE userID=$session OR userID2=$session)
ORDER BY id DESC LIMIT 10