I have 2 different tables; First one is for ‘Users’ and the second is ‘Relations’.
Users has ‘username’ column and Relations has ‘username’ and ‘friendsname’. For every friendship instance 2 rows are inserted to this table. e.g If John adds Janna as friend this means John is added by Janna likewise.
I am trying to find out something like friend suggestion as in Facebook or Twitter. Random user will be selected from Users table and that will be checked in Relations table whether they are friend or not. And this will be done continously until finding 5 successful non-friend match and return all at once(if it’s possible).
I can do this with Select all rows and iterate/search in the server by myself but i don’t think this is something PostgreSQL can not handle. Also my way seems too expensive task for this level “relatively” trivial feature.
Is there any easy and elegant way to handle this task?
Thanks in advance and have a good sunday/Christmas night.
UPDATE: I am appending some mock data, sorry for the delay:
create table relations (
rel_id bigserial primary key not null ,
username VARCHAR(50) not null ,
friendname VARCHAR(50) not null ,
since DATE not null
);
insert into relations (rel_id, username, friendname, since) values (1,'user1', 'user2', '06/01/2021');
insert into relations (rel_id, username, friendname, since) values (2,'user2', 'user1', '06/01/2021');
insert into relations (rel_id, username, friendname, since) values (3,'user1', 'user3', '16/10/2021');
insert into relations (rel_id, username, friendname, since) values (4,'user3', 'user1', '16/10/2021');
insert into relations (rel_id, username, friendname, since) values (5,'user3', 'user5', '16/01/2020');
insert into relations (rel_id, username, friendname, since) values (6,'user5', 'user3', '16/01/2020');
create table Users (
user_id bigserial primary key not null ,
username VARCHAR(50) not null
);
insert into Users (user_id, username) values (1,'user1');
insert into Users (user_id, username) values (2,'user2');
insert into Users (user_id, username) values (3,'user3');
insert into Users (user_id, username) values (4,'user4');
insert into Users (user_id, username) values (5,'user5');
insert into Users (user_id, username) values (6,'user6');
insert into Users (user_id, username) values (7,'user7');
As expected result query may suggest User4-5-6 and 7 for User1 as friend. Because according to table user1 is friend only with user 2 and user3. Considering he cannot be friend with himself we dont expect to see user1 as in results also.