List posts of friends

Question

I have a database with the following structure:

Table users:

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

Table friendships:

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 friends, tried to use INNER JOIN but I don’t know how to use it properly...

Answer 1

If I understand well the meaning of the fields in the tables you can get so:

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 did the join of all tables considering that you want to pick up posts that a friend sent 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

Making Join with post where the sender of the message (user’s friend logged that sending the message) is the owner of the post a.id_remenente = p.id_autor

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

Here the Fiddle with it working.

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

UPDATING

According to the comment, you can do so: (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)

Grab all posts from a user who has 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.