Как выбрать сообщения, созданные мной или моими друзьями в ленте новостей?

Я пытаюсь сделать это некоторое время, а потом понял, что это 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.

Этот вопрос является продолжением подзапроса PHP, чтобы выбрать профиль пользователя, который дружит со мной в таблице друзей .

Я знаю, что вы уже приняли ответ, но я был на полпути, написав это, поэтому решил опубликовать его в любом случае.

Я собираюсь вернуться немного назад, надеюсь, отвечая на ваш вопрос. При разработке приложений и создании баз данных вы ВСЕГДА стараетесь структурировать вещи как описательные и компактные, насколько это возможно. Было бы неудобно иметь переменную / столбцы с именем 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 для 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 соответствии с документами и EXPLAIN ). Кроме того, вышеупомянутый код SQL будет намного проще поддерживать и расширять.

В любом случае, надеюсь, вы найдете это полезным.

Я думаю, вам нужно начать с ваших сообщений и использовать EXISTS, чтобы ограничить результат соответствующими:

 SELECT * FROM profilePostTbl post WHERE -- This post is by a friend of current user or his own post. EXISTS (SELECT * FROM friendsTbl WHERE post.post_by IN (profile_one, profile_two) AND 'john123' IN (profile_one, profile_two)) -- This post is addressing the current user OR post_to = 'john123'; 

Если вы хотите отобразить имена авторов и адресатов, просто присоедините их к сообщению:

 SELECT post.*, post_by.u_id as by_uid, post_by.f_name as by_fname, post_by.l_name as by_lname, post_to.u_id as to_uid, post_to.f_name as to_fname, post_to.l_name as to_lname FROM profilePostTbl post INNER JOIN profileTbl post_by ON post.post_by = post_by.profile_name INNER JOIN profileTbl post_to ON post.post_to = post_to.profile_name WHERE -- This post is by a friend of current user or his own post. EXISTS (SELECT * FROM friendsTbl WHERE post.post_by IN (profile_one, profile_two) AND 'john123' IN (profile_one, profile_two)) -- This post is addressing the current user OR post_to = 'john123';