List friends posts

Question

List friends posts

Navigation

#1 by (2 votes)

1

I have a database with the following structure:

Users table:

usuario_id   Primária   int(11)
nome                    varchar(128)
nascimento              date

Friendships table:

id_amizade   Primária   int(11)
id_remenente            int(11)
id_destinatario         int(11)
data_envio              datetime
data_resposta           datetime
sao_amigos              int(11)

Table posts:

post_id    Primária     int(11)
id_autor                int(11)
texto                   longtext
data                    datetime

I need to load posts from user's friends, I tried to use INNER JOIN but I do not know how to use them correctly ...

php mysql sql join

asked by anonymous 01.01.2016 / 13:22

1 answer

Combobox Css Help When capturing screen the error occurs: raised exception class EOutOfResources with message

score 2 · Accepted Answer

If I understand the meaning of the fields in the tables, you can get it like this:

select u.usuario_id as idUsuario,
       u.nome as nomeUsuario,
       a.id_remenente as idAmigo,
       a.sao_amigos as IndicadorDeAmigo,
       p.post_id as idDoPost

from usuario as u
inner join amizades as a on u.usuario_id = a.id_destinatario
inner join posts as p on a.id_remenente = p.id_autor
where u.usuario_id = 1
and a.sao_amigos = 1

I made join of all tables considering that you want to get the posts that a friend sends to the logged in user, then:

All users join with friendships where the logged in user is the recipient of the post u.usuario_id = a.id_destinatario

Doing join with post where the sender of the message (friend of the logged in user who sends the message) is the owner of the post a.id_remenente = p.id_autor

Filtering everything from the logged-in user 1 and who is friend ( a.sao_amigos = 1 )

Here's the Fiddle with it working.

In Fiddle, Ricardo is the logged-in user, Daniel and Felipe sent posts to him, but only Daniel is a friend, so only Daniel's post appeared.

UPDATING

According to the comment, you can do this: ( Fiddle )

select *
 from posts
 where id_autor in 
   (select id_destinatario 
      from amizades
     where id_remenente = 1 
       and sao_amigos = 1)
union
select *
 from posts
 where id_autor in 
   (select id_remenente
      from amizades
     where id_destinatario  = 1 
       and sao_amigos = 1)

Get all posts from a user who received a friend request from the logged in user and confirmed plus all posts from a user who submitted a request to the logged in user and the user accepted.