SELECT random rows with a condition from 2 different table in PostgreSQL

Question

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.

I hope it is sufficent now. Feel free to remind for future misunderstandings. — Alteran
– Alteran, Commented Dec 26, 2021 at 19:18

Peter Kusza · Accepted Answer · 2021-12-26 20:35:33Z

2

The next select will return exactly 5 friendsname which are not friends of John (in this case). The second filter must be applied, to not return John for John.

SELECT DISTINCT(friendsname) FROM relations 
WHERE username <> 'John' AND friendsname <> 'John'
LIMIT 5

To add a random factor to this you can modify the select this way:

SELECT * FROM (
SELECT DISTINCT(friendsname) FROM relations 
WHERE username <> 'John' AND friendsname <> 'John'
) as s
ORDER BY random()
LIMIT 5

Notice, that the random() ordering is a heavy operation, because it does a scan.

UPDATE To select from the users table instead of the relations table the query could look like this:

SELECT * FROM users AS u
WHERE u.username NOT IN (SELECT friendname FROM relations WHERE username = 'user3') AND u.username <> 'user3'
ORDER BY random()
LIMIT 3

There are two selects made without join, just filtering, it could possibly perform well. I did not tested it on larger tables.

edited Dec 26, 2021 at 20:35

answered Dec 26, 2021 at 18:04

Peter Kusza

4515 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alteran Over a year ago

Thanks for the reply but i need to use 'Users' table for random selection. relations table is for checking only.

Peter Kusza Over a year ago

@Alteran I have updated the query to use the 'Users' table for random selection

Jayvee · Accepted Answer · 2021-12-26 19:32:54Z

2

Without using cursors, one thing you can do is find all possible friendships with a cross join, then remove the existing ones and finally pick 5 random rows of the resulting set, something like this

   select usernm, friend 
from
(
select distinct greatest(a.username,b.username) usernm,least(a.username,b.username) friend 
from users a
cross join users b
where a.username <> b.username
except
select distinct greatest(username, friendname) usernm, least(username, friendname) friend
from relations
) x
order by random() limit 5

the use of greatest and least is to ensure that we don't repeat the same friendship. Also the "where a.username <> b.username" in the cross join is to not introduce "self friendships".

db fiddle: https://www.db-fiddle.com/f/gpYMWHa2dK7BcZpxjHqwGa/0

edited Dec 26, 2021 at 19:32

answered Dec 26, 2021 at 18:04

Jayvee

10.9k3 gold badges35 silver badges43 bronze badges

6 Comments

Alteran Over a year ago

Somehow i couldn't make it execute. Could you check again in consideration of the update.

Jayvee Over a year ago

Thank you for the sample, I fixed the query, please have a look at the db fiddle to test it and let us know if this is what you need

Alteran Over a year ago

I dont know how but it seems working. Though It is suggesting only one way. e.g User5 is suggested to user2 but not the other way. Also result is expected for a given/certain username. I need to suggest some friend to "user 3". Can you explain how greatest or least work in here. As i know it is used in comparable ints or times. Since i couldn't catch the logic behind this code, i am not able to interpret it for certain user extraction.

Jayvee Over a year ago

I'm actually avoiding the two ways by using greatest and least, which for strings orders alphabetically. Greatest(a, b) = b and Least(a, b) = a.

Jayvee Over a year ago

if we don't do it in this, the query might return the same friendship both ways, which doesn't make sense as we know that if it doesn't exist one way, it doesn't the other way either. Once you have have the 5 new friendships you can suggest them both ways.

|

Collectives™ on Stack Overflow

SELECT random rows with a condition from 2 different table in PostgreSQL

2 Answers 2

2 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related