mysql - 如何在动态消息中选择我或我的 friend 创建的帖子？

Question

我已经尝试这样做了很长一段时间，然后我发现首先是 SQL 查询无法使其正常工作。

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

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'

这是一个 fiddle :http://sqlfiddle.com/#!2/a10f39/1

对于这个例子，john123 是当前登录的用户并且是 hassey、smith 和 joee 的 friend ，因此只有 john123 在他自己或他 friend 的帖子上发布的帖子必须显示在新闻提要中他的 friend 们在他们自己的个人资料和 john123 的个人资料上发帖。

此问题是 PHP Sub-query to select all user's profile who are friends with me in Friends table 的后续问题.

Answer 1

我知道你已经接受了一个答案，但我写到一半所以我还是决定发布它。

在回答您的问题之前，我要稍微回顾一下。在开发应用程序和构建数据库时，您应该始终尝试将事物结构化得尽可能具有描述性和紧凑性。拥有一个名为 color 的变量/列并在那里存储加密的用户密码真的很尴尬(很奇怪，对吧？)。有一些标准的数据库命名约定，如果遵循这些约定，在开发复杂的应用程序时尤其如此。我建议您阅读一些有关命名约定的博客。一个好的起点可能是 this一个。

我完全意识到，根据下面建议的更改，您可能需要部分/完全重写您目前编写的应用程序代码，但如果您真的想让事情运行得更好，这取决于您。

让我们从修复数据库结构开始。看起来，您正在做一个类似于 facebook 的 newsfeed 的应用程序。在这种情况下，使用 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

和here您可以在 sqlfiddle 中看到它的运行情况。如您所见，只是通过更好地构建数据库，我已经消除了对子查询的需要(这是 EXISTS/NOT EXISTS 根据 docs 和 EXPLAIN 所做的)。此外，上述 SQL 代码将更易于维护和扩展。

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