如何选择我或我的朋友在新闻源中创建的帖子？

Question

我已经尝试了很长时间了，然后才发现它是SQL查询，因此我一开始无法使其正常工作。

我正在尝试创建一个类似于Facebook的简单新闻提要页面，您可以在其中看到您在个人资料以及您的朋友个人资料上发布的帖子，还可以看到您的朋友在您的个人资料以及他们自己的个人资料上发表的帖子。

SELECT 
    friendsTbl.profile_one,
    friendsTbl.profile_two,
    user_profile.u_id as my_uid,
    user_profile.f_name as my_fname,
    user_profile.l_name as my_lname,
    friend_profile.u_id as friend_uid,
    friend_profile.f_name as friend_fname,
    friend_profile.l_name as friend_lname,
    profilePostTbl.post as post,
    profilePostTbl.post_to as posted_profile,
    profilePostTbl.post_by as posted_by
FROM friendsTbl
LEFT JOIN profileTbl AS user_profile ON user_profile.profile_name = IF(friendsTbl.profile_one = 'john123', friendsTbl.profile_one, friendsTbl.profile_two)
LEFT JOIN profileTbl AS friend_profile ON friend_profile.profile_name = IF(friendsTbl.profile_one = 'john123', friendsTbl.profile_two, friendsTbl.profile_one)
LEFT JOIN profilePostTbl ON (post_by = IF(profilePostTbl.post_to = friendsTbl.profile_one,profilePostTbl.post_to, profilePostTbl.post_by))
WHERE friendsTbl.profile_one = 'john123' OR friendsTbl.profile_two = 'john123'

这是一个小提琴：http ://sqlfiddle.com/#!2/
a10f39/1

在此示例中，john123是当前登录的用户，并且是hassey，smith和joee的朋友，因此，只有那些帖子必须显示在john123自己或他的朋友的帖子以及他的朋友的帖子所发布的新闻提要中在他们自己的个人资料以及john123的个人资料上。

Answer 1

我知道您已经接受了答案，但是我正在写这篇文章的中途，所以我还是决定将其发布。

在希望回答您的问题之前，我将稍作讨论。在开发应用程序和构建数据库时，您应该 始终
尝试将内容描述和紧凑化。拥有一个变量/列命名color并在其中存储加密的用户密码（很奇怪，对吗？）确实很尴尬。有一些标准的数据库命名约定，在遵循这些约定后，特别是在开发复杂的应用程序时，它们使工作变得更加轻松。我建议您阅读一些有关命名约定的博客。一个很好的起点可能就是这个。

我完全意识到，通过以下建议的更改，您可能需要部分/全部重写到目前为止已编写的应用程序代码，但是，如果您确实希望事情做得更好，则取决于您。

让我们从修复数据库结构开始。从外观上看，您正在做的应用程序类似于facebook的新闻提要。在这种情况下，使用FOREIGN KEYS几乎是强制性的，因此可以保证一定的数据一致性。下面的示例数据库模式显示了如何实现这一点。

-- Application users are stored here.
CREATE TABLE users (
  user_id      INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  first_name   VARCHAR(255),
  last_name    VARCHAR(255),
  profile_name VARCHAR(255)
) ENGINE=InnoDb;

-- User friendship relations go here
CREATE TABLE friends (
  friend_id   INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  profile_one INT NOT NULL,
  profile_two INT NOT NULL,
  FOREIGN KEY (profile_one) REFERENCES users (user_id),
  FOREIGN KEY (profile_two) REFERENCES users (user_id)
) ENGINE=InnoDb;

-- User status updates go here
-- This is what will be displayed on the "newsfeed"
CREATE TABLE statuses (
  status_id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  author_id    INT NOT NULL,
  recipient_id INT NOT NULL,
  message      TEXT,
  -- created date ?
  -- last updated date ?
  FOREIGN KEY (author_id)    REFERENCES users (user_id),
  FOREIGN KEY (recipient_id) REFERENCES users (user_id)
) ENGINE=InnoDb;

-- Replies to user statuses go here. (facebook style..)
-- This will be displayed as the response of a user to a certain status
-- regardless of the status's author.
CREATE TABLE replies (
  reply_id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  status_id INT NOT NULL,
  author_id INT NOT NULL,
  message   TEXT,
  FOREIGN KEY (status_id) REFERENCES statuses (status_id),
  FOREIGN KEY (author_id) REFERENCES users    (user_id)
) ENGINE=InnoDb;

现在，此问题已解决，我们可以继续下一步-为john123（拥有user_id=1）的人选择新闻源。这可以通过以下查询来实现：

SET @search_id:=1; -- this variable contains the currently logged in user_id so that we don't need to replace the value more than once in the whole query.

SELECT 
    statuses.*,
    author.first_name     AS author_first_name,
    author.last_name      AS author_last_name,
    recipient.first_name  AS recipient_first_name,
    recipient.last_name   AS recipient_last_name
FROM statuses
JOIN users AS author      ON author.user_id    = statuses.author_id
JOIN users AS recipient   ON recipient.user_id = statuses.recipient_id
WHERE (statuses.author_id = @search_id OR statuses.recipient_id = @search_id)
ORDER BY status_id ASC

而在这里，你可以看到它在行动中的sqlfiddle。如您所见，仅通过更好地构建数据库，我就消除了对子查询的需求（EXISTS / NOT EXISTS根据docs和的要求EXPLAIN）。此外，上面的SQL代码将更易于维护和扩展。

无论如何，我希望您觉得这很有用。