如何选择我或我的朋友在新闻源中创建的帖子? [英] How to select posts created by me or my friends in a news feed?

问题描述

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

I've been trying to do this for quite a while now and then I figured that it is the SQL query that I cannot get it working properly in the first place.

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

I am trying to create a simple news feed page similar to Facebook where you can see the post you made on your profile as well as your friends profile and also you can see posts which your friends made on your profile as well as their own profile.

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的个人资料上发布.

For this example, john123 is the user that is currently logged in and is friends with hassey, smith and joee and therefore only those posts must show in the news feed which john123 posted on his own or his friend's post and the ones that his friends posted on their own profile as well as on john123's profile.

此问题是对.

推荐答案

我知道您已经接受了答案，但是由于我正在编写此书，所以我决定仍然将其发布.

I know you've already accepted an answer, but I was half-way writing this so I decided to post it anyway.

在希望回答您的问题之前，我将稍作讨论.在开发应用程序和构建数据库时，应 ALWAYS 尝试使结构尽可能具有描述性和紧凑性.拥有一个名为color的变量/列并在其中存储加密的用户密码真的很尴尬(很奇怪，对吧?).有一些标准的数据库命名约定，在遵循这些约定后，特别是在开发复杂的应用程序时，它们使工作变得更加轻松.我建议您阅读一些有关命名约定的博客.一个很好的起点可能是这个一个.

I'm going to go a little bit back before hopefully answering your question. When developing applications and constructing databases, you should ALWAYS try to structure things as descriptive and compact as possible. It would be really awkward to have a variable/column named color and store encrypted user passwords there (weird, right?). There are some standard database naming conventions which, when followed, make life a lot easier specially when developing complicated applications. I would advice you to read some blogs regarding the naming conventions. A good starting point may be this one.

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

I fully realize that with the suggested changes below you might need to partially/fully rewrite the application code you've written so far, but it's up to you if you really want things working better.

让我们从修复数据库结构开始.从外观上看，您正在做的应用程序类似于facebook的新闻提要.在这种情况下，必须使用FOREIGN KEYS，因此可以保证某些数据的一致性.下面的示例数据库架构显示了如何实现此目标.

Let's begin by fixing the database structure. By the looks of it, you're doing an application similar to facebook's newsfeed. In this case, using FOREIGN KEYS is pretty much mandatory so you could guarantee some data consistency. The example database schema below shows how you can achieve that.

-- 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的用户)选择新闻源.这可以通过以下查询来实现:

Now that this is fixed, we could proceed with the next step - selecting the newsfeed for john123 (who has user_id=1). This can be achieved with the query below:

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中看到它的运行情况.如您所见，仅通过更好地构建数据库，我就消除了对子查询的需求(根据

And here you could see it in action in an sqlfiddle. As you can see, just by structuring the database better, I've eliminated the need of a sub-query (which is what EXISTS / NOT EXISTS do according to the docs and EXPLAIN). Furthermore the above SQL code would be a lot easier to maintain and extend.

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

Anyway, I hope you find this useful.

这篇关于如何选择我或我的朋友在新闻源中创建的帖子?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！