0

I have the following structure (simplified for brevity). I am mostly frontend developer that suddenly needs to learn the basics of SQL, so bear with me, please :)

CREATE TABLE user (
    id text NOT NULL,
    lastOnlineAt timestamptz NOT NULL
);
CREATE TABLE pair (
    id text NOT NULL
);
CREATE TABLE userPair (
    userId text NOT NULL,
    pairId text NOT NULL
);

The important aspect here is that every pair is linked to exactly 2 user entities (through userPair). I am not sure if was the wisest approach, but I did not like the idea of having columns user1/2 inside the pair.

Now I need to do a SELECT that for every user finds associated pairs, picks the other user of that pair and gets his lastOnlineAt for a comparison. Effectively I need to find paired users that were online in the last 5 minutes.

I would probably start like this.

SELECT up.'pairId' 
  FROM 'userPair' AS up 
       LEFT JOIN pair as p 
 WHERE up.'userId' = $userId 
   AND p.id = up.'pairId'

But then I am getting lost in complexity here.

It's for the Postgres 12.

Mike Organek
  • 11,647
  • 3
  • 11
  • 26
FredyC
  • 3,999
  • 4
  • 30
  • 38

2 Answers2

1

You have a syntax errors in that a left join needs an on and identifiers cannot be enclosed in single quotes since that makes them literals.

You need to join userpair twice to get what you want:

SELECT u2.* 
  FROM "userPair" AS p1 
       JOIN "userPair" p2 
         ON p2."pairId" =  p1."pairId"
        AND p2."userId" != p1."userId"
       JOIN "user" u2 
         ON u2.id = p2."userId" 
 WHERE p1."userId" = $userId 
   AND u2."lastOnlineAt" >= now() - interval '5 minutes'

Also, using camel case for identifier is very bad idea.

Mike Organek
  • 11,647
  • 3
  • 11
  • 26
  • Yea, I am constantly lost about which quotes do what. As I said, I am mostly frontend dev, so I am learning on the road. I did not know that SQL can't handle camelCase and since I am used it from the code, I thought it's fine. Too late for that now :) – FredyC Sep 09 '20 at 14:11
  • 2
    @FredyC It's not that it cannot handle it, it is that the default without using double quotes will lowercase the identifier. So if you have camelcase, you have to enclose them in double quotes to prevent that lowercasing. That leads to headaches. – Mike Organek Sep 09 '20 at 14:13
  • Thank you, this is exactly what I was looking for. So simple and elegant :) – FredyC Sep 11 '20 at 15:44
0

One method uses a lateral join:

select u.*
from userpair p cross join lateral
     (values (userid, pairid), (pairid, userid)
     ) v(theuserid, theotherid) join
     users u 
     on u.id = v.theotherid
where v.theuserid = ?;

This uses the lateral join to split the data into two pairs -- with the users in either order. The first is matched to the input. The second is the other one that is returned.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786