Friendship system

Question

I’m making a friendship system (look at the img)

In users shows all the people I can add as friends, I want to remove from the list of users all the people who have already sent me friend request are all people to whom I sent friend request.

I’m confused about the database with this! In this example below if I entered with user number 1 (Jose) should only appear to me (3) girafao because the (2) cat already and my friend

It is if I entered with the user (2) cat in the table of users should not appear anyone, because the (1) Josh is already my friend and the (3) girafao I already sent friend request so there is no need to appear.

Answer 1

Consider the following table structure:

create table usuario (
  id int primary key auto_increment,
  nome varchar(50),
  email varchar(50),
  senha varchar(50)
);

create table relacionamento (
  de int not null,
  para int not null,
  estado boolean,
  primary key (de, para),
  foreign key (de) references usuario(id),
  foreign key (para) references usuario(id)
);

They are basically the same as yours, except for the fact that the name of some fields are a little different, and in the relationship table, there is no primary auto increment key (I think the fields are better de and para perform this function together).

Then entering the data you showed in the question:

insert into usuario (nome, email, senha) 
values ('jose', 'email', 'senha'), ('gato', 'email', 'senha'),
('girafão', 'email', 'senha');

insert into relacionamento (de, para, estado) values (1, 2, true), (2, 3, false);

To solve your problem just use the following sql query:

select * from usuario WHERE usuario.id NOT IN (SELECT de FROM
relacionamento where para = ID_USUARIO_ENTROU)
AND usuario.id NOT IN (SELECT relacionamento.para 
FROM relacionamento where relacionamento.de = ID_USUARIO_ENTROU) 
AND usuario.id != ID_USUARIO_ENTROU;

Basically all table records are returned usuario, except those that are different from the field of and for the relationship table, while that of or for are equal to the entered user. Of course if the user id is different from the user that entered.

The following consultations demonstrate the following statement::

I’m confused with the database with this! In this example below if i entered with user number 1 (Jose) should only appear to me (3) girafao because the (2) cat already is my friend

select * from usuario WHERE usuario.id NOT IN (SELECT de 
FROM relacionamento where para = 1)
AND usuario.id NOT IN (SELECT relacionamento.para 
FROM relacionamento where relacionamento.de = 1) 
AND usuario.id != 1;

Generates as output:

+---+----------+------+-------+
|id |   nome  | email | senha |
+-----------------------------+
|3  | girafão | email | senha |
+---+---------+-------+-------+

It is if I entered with the user (2) cat in the user table not no one should appear, because (1) Jude is already my friend and (3) girafao I have already sent friend request so there is no need to appear.

select * from usuario WHERE usuario.id NOT IN (SELECT de 
FROM relacionamento where para = 2)
AND usuario.id NOT IN (SELECT relacionamento.para 
FROM relacionamento where relacionamento.de = 2) 
AND usuario.id != 2;

Generates as output:

+---+----------+------+-------+
|id |   nome  | email | senha |
+-----------------------------+
|   |         |       |       |
+---+---------+-------+-------+

And if you enter with user 3:

select * from usuario WHERE usuario.id NOT IN (SELECT de 
FROM relacionamento where para = 3)
AND usuario.id NOT IN (SELECT relacionamento.para 
FROM relacionamento where relacionamento.de = 3) 
AND usuario.id != 3;

Generates as output:

+---+----------+------+-------+
|id |   nome  | email | senha |
+-----------------------------+
|1  | jose    | email | senha |
+---+---------+-------+-------+

Basically the three examples use Subqueries. You can test on sqlFiddle