Postgresql – Subquery join not working as expected when trying to join an array

I’m trying to prepare a query for a user, which should return other users that this user hasn’t interacted with yet.

Suppose the following:

create schema if not exists bv;

create table if not exists bv.user (
  id serial primary key,
  user_name varchar(15) not null,
  is_male boolean not null
);

create table if not exists bv.chat (
  id serial primary key
);

create table if not exists bv.chat_message (
  id serial primary key,
  chat_id bigint references bv.chat (id),
  user_id bigint references bv.user (id),
  message_text varchar(255) not null
);

create table if not exists bv.junction_user_chat (
  chat_id bigint references bv.chat (id),
  user_id bigint references bv.user (id)
);

create table if not exists bv.user_coordinates (
  id serial primary key,
  user_id bigint references bv.user (id)
);

create table if not exists bv.user_like (
  id serial primary key,
  source_user_id bigint references bv.user (id),
  target_user_id bigint references bv.user (id)
);

insert into bv.user values
  (100, 'Mike', true),
  (101, 'John', true),
  (102, 'Jane', false),
  (103, 'George', true),
  (104, 'Lance', true);
insert into bv.chat values
  (10);
insert into bv.junction_user_chat values
  (10, 101),
  (10, 102);
insert into bv.chat_message values
  (500, 10, 101, 'hello'),
  (501, 10, 102, 'how are you?');
insert into bv.user_coordinates values
  (777, 100),
  (778, 101),
  (779, 102),
  (780, 103),
  (781, 104);
insert into bv.user_like values
  (1, 101, 102),
  (2, 102, 101),
  (3, 100, 102),
  (4, 103, 102),
  (5, 102, 104);

Let’s assume I am user with ID 102 (Jane). I would expect my query to return,

┌──────┬────────────────────┬──────────┐
│ id   │ user_name          │ is_male  │
├──────┼────────────────────┼──────────┤
│ 100  │ Mike               │ true     │
│ 103  │ George             │ true     │
└──────┴────────────────────┴──────────┘

Some remarks:

• Users 102 and 101 already have a chat (and chat messages) between them, which why user 101 is not returned in the query,
• User 102 does not exist as the source_user_id in bv."user_like" — if they did exist in that table, then user 103 would not be returned in the query
• Users 100 and 103 are returned because,
  • they’ve both "liked" user 102,
  • user 102 not exist as bv."user_like"."souce_user_id",
  • a chat doesn’t exist for neither users 102 - 100 nor 102 - 103
• User 104 is not returned because user 102 already "liked" them (even though user 104 hasn’t liked user 102 back at the moment)

Again, the idea here is to return all users that the current user(with id 102) hasn’t interacted with. By "interaction" I mean a record in bv."user_like", bv."chat" or bv."chat_message".

Here is my query:

SELECT DISTINCT ON (u.id)
    u.*
FROM bv.user_coordinates uc
JOIN bv.user u
    ON u.id = uc.user_id
JOIN (
    SELECT
      user_id,
      chat_id
    FROM bv.junction_user_chat
) chat_ids
  ON chat_ids.user_id = 102
LEFT JOIN bv.user_like ul
    ON u.id = ul.target_user_id
    AND ul.source_user_id = 102
LEFT JOIN bv.chat_message cm 
    ON cm.chat_id = any(chat_ids)
    AND (cm.user_id = u.id OR cm.user_id = 102)
WHERE u.id != 102
AND uc.user_id = u.id
AND u.is_male = true
AND ul.id is null
AND cm.id is null
GROUP BY u.id
ORDER BY u.id ASC

The problem is that I am unsure of how to make use of junction_user_chat here. Ideally, I’d like to form an array chat_ids (as I’ve tried to do), and then check to see if user ID 102 is present; if it is, then I’d like to omit it from the result.

How can I get this to work, is my approach even in the correct direction? Postgres’ array methods and data types are new to me in general, I’m much more used to junction tables, so I’d much more prefer that over the current attempt with the nested join and ANY comparison.

I will finally mention that this is a severely slimmed-down version of the actual query, which is why you are seeing a select...from bv.user_coordinates. This part can change, but I kept it in here just in case.