Help with an associative table of friends

Asked

Viewed 70 times

1

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

id_friends (id of the relation)

id_usuario_de (id of the user who sent the friend request)

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

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

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

If: the id_usuario_de is the same as id_usuario logated, bring the id_usuario_para (who will be a friend of the logged in user)

Otherwise: if the id_usuario_para is the same as id_usuario logated, bring the id_usuario_de (which will also be a friend of the logged in user, but it was he who sent the request).

I just need to bring the id_usuario of the logged-in user friend, but I can’t/I have no idea how to do this with Mysql.

Thanks in advance for the help and I apologize if the question has become too confusing.

Edit

I got the expected result with the following consultation:

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 she got really big, I was wondering if there’s an "easy" way to make her...

PS: - id_usuario1 = id_usuario_de, id_usuario2 = id_usuario_para - In the query I get some extra data like photo and description - The id_status = 8 is the id that signals that the relationship has been accepted, ie the id_usuario_para accepted the request for friendship

php mysql database codeigniter

  • Have you no starting point for the Mysql query? You can put this in your question?

2 answers

1

You can use the following query, to bring all users who 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.

  • Yes, but only brings the users who sent request to the logged user, and not all friends of the logged user, but, the logic of the query helped me understand how it works to query the two columns of the tables simultaneously

0

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

I believe you have two tables tb_usuario, tb_usuario_request (example only). Do 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 users who made the request

to better still can 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

  • In your example happened the same as the answer above, it returns only when the logged id_user is saved in the id_usuario_to, but helped me a lot in the question, thanks

Browser other questions tagged php mysql database codeigniter

You are not signed in. Login or sign up in order to post.