Help with an associative table of friends

1

I have a friends table, which has the following composition:

  

friends_id (relationship id)

     

id_user (id of the user who submitted the friend request)

     

id_user_to (id of the user who received the friend request)

     

id_status (whether the user who received the request accepted it or not)

The whole send / accept system is ready, now I want to have a list of the user's friends logged in, in which I thought of the following logic (using the same table).

If : id_usuario_de is equal to id_usuario logged in, bring id_usuario_para (which will be a friend of logged in user)

Otherwise : If id_usuario_para is equal to id_usuario logged in, bring id_usuario_de (which will also be a friend of the logged in user, / p>

I need to bring only the id_usuario of the friend of the logged in user, but I can not / have no idea how to do this with MySQL.

I thank you for the help and apologize if the question was too confusing.

edit

I got the expected result with the following query:

SELECT usuario.id_usuario, usuario.nome_completo, caminho,  usuario.descricao FROM usuario, foto_usuario 
WHERE (usuario.id_usuario 
IN (SELECT id_usuario2 FROM amigos WHERE id_usuario1 = $usuario AND  id_status = 8) 
OR usuario.id_usuario 
IN (SELECT id_usuario1 FROM amigos WHERE id_usuario2 = $usuario AND id_status = 8)) 
AND usuario.id_usuario != $usuario AND usuario.id_usuario = foto_usuario.id_usuario

But it got really big, I've been wondering if there's a "easier" way to do it ...

PS: - id_user1 = id_user, id_user2 = id_user_for - In the query I get some extra data like photo and description - id_status = 8 is the id that indicates that the relation was accepted, that is, id_usuario_para accepted the request for friendship

    
asked by anonymous 04.10.2018 / 19:53

2 answers

1

You can use the following query to bring all users who have sent invitations to the logged in user.

SELECT id_usuario, usuario.nome, usuario.ultimo_nome FROM usuario 
INNER JOIN amigos ON amigos.id_usuario_de = usuario.id 
WHERE amigos.id_usuario_para = <id_do_usuario_logado>

This query will result in all users sending invitations to the logged in user.

    
04.10.2018 / 21:08
0

Good afternoon! In case you can cross the two tables.

I believe you have two tables tb_user, tb_user_request (example only). Make the Following.

SELECT * FROM tb_usuario AS usuario
JOIN tb_usuario_solicitacao AS solicitacao ON solicitacao.id_usuario = usuario.id_usuario
WHERE solicitacao.id_usuario_para = <id_user>

You will get a list of all the users who made the request

To improve it can still pull only the friends he accepted:

SELECT * FROM tb_usuario AS usuario
JOIN tb_usuario_solicitacao AS solicitacao ON solicitacao.id_usuario = usuario.id_usuario
WHERE solicitacao.id_usuario_para = <id_user> AND solicitacao.id_status = 1
    
04.10.2018 / 21:21