¿Cómo seleccionar publicaciones creadas por mí o por mis amigos en un service de noticias?

He estado tratando de hacer esto durante bastante time y luego me di count de que es la consulta SQL que no puedo hacer que funcione correctamente en primer lugar.

Intento crear una página de noticias sencilla similar a Facebook donde pueda ver la publicación que hizo en su perfil, así como el perfil de sus amigos y también puede ver las publicaciones que sus amigos hicieron en su perfil, así como su propio perfil.

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' 

Aquí hay un violín: http://sqlfiddle.com/#!2/a10f39/1

Para este ejemplo, john123 es el usuario que actualmente está conectado y es amigo de hassey, smith y joee y, por lo tanto, solo esas publicaciones deben aparecer en las noticias que john123 publicó por su count o la de su amigo y las que sus amigos publicaron en su propio perfil, así como en el perfil de john123.

Esta pregunta es una continuación de la Subconsulta PHP para seleccionar todos los perfiles del usuario que son amigos conmigo en la tabla Amigos .

Sé que ya has aceptado una respuesta, pero estaba a medio escribir esto, así que decidí publicarlo de todos modos.

Voy a ir un poco atrás antes de responder su pregunta. Al desarrollar aplicaciones y build bases de datos, SIEMPRE debe tratar de estructurar las cosas de la manera más descriptiva y compacta posible. Sería realmente incómodo tener una variable / columna llamada color y almacenar passwords de usuario cifradas allí (raro, ¿verdad?). Existen algunas convenciones de nombres de bases de datos estándar que, cuando se siguen, hacen la vida mucho más fácil, especialmente cuando se desarrollan aplicaciones complicadas. Te aconsejo que leas algunos blogs sobre las convenciones de nombres. Un buen punto de partida puede ser este .

Me doy count de que con los cambios sugeridos a continuación, es posible que necesite reescribir parcialmente / completamente el código de la aplicación que ha escrito hasta ahora, pero depende de usted si realmente desea que las cosas funcionen mejor.

Comencemos por arreglar la estructura de la database. Por lo que parece, estás haciendo una aplicación similar al suministro de noticias de Facebook. En este caso, usar FOREIGN KEYS es prácticamente obligatorio, por lo que podría garantizar cierta consistencia de los datos. El esquema de la database de ejemplo a continuación muestra cómo puede lograr eso.

 -- Application users are stonetworking 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; 

Ahora que esto está solucionado, podríamos proceder con el siguiente paso: seleccionar el john123 noticias para john123 (que tiene user_id=1 ). Esto se puede lograr con la siguiente consulta:

 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 

Y aquí puedes verlo en acción en un sqlfiddle. Como puede ver, simplemente estructurando mejor la database, eliminé la necesidad de una subconsulta (que es lo que EXISTS / NOT EXISTS hace de acuerdo con los documentos y EXPLAIN ). Además, el código SQL anterior sería mucho más fácil de mantener y ampliar.

De todos modos, espero que encuentres esto útil.

Creo que debes comenzar con tus publicaciones y usar EXISTS para limitar el resultado a los relevantes:

 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'; 

Si desea mostrar los nombres de los autores y destinatarios de publicaciones, simplemente únalos a la publicación:

 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';