Question 89279 in PostgreSQL Question posted in
The official documentation can be found here.

SQL Query – Single SELECT instead of UNION – Postgresql

Building a social site which allows users to follow each other, and enforcing a business rule that only allows DMs when two users mutually follow each other.

Follows are stored in a lookup:

TABLE FOLLOWS
- userA_ID FK
- userB_ID FK

Given this dataset in the FOLLOWS table:

|userA_ID  |userB_ID  |
-----------------------
|123       |456       | <- Gerald follows Jane
|789       |456       | <- Grace follows Jane
|456       |789       | <- Jane follows Grace

If I’m logged in as Jane, I can DM Grace but not Gerald, and vice-versa.

My current Query….

--  mutual followers
SELECT DISTINCT (u.id) AS userId, u."name" AS "userName"
FROM "Follows" 
    INNER JOIN "User" u ON u.id = "userA_ID"
WHERE "userB_ID" = $1
INTERSECT
SELECT DISTINCT (u.id) AS "userId", u."name" AS "userName"
FROM "Follows" 
    INNER JOIN "User" u ON u.id = "userB_ID"
WHERE "userA_ID" = $1

…feels clunky. Is there a way to write this in a single SELECT instead of with a UNION?

Tags:

3

Answers


  1. 0

    Yes, you don’t need to use UNION INTERSECT. The intersection is basically computed as a WHERE EXISTS(…) statement checking for the same values to be in the result of the second query. But the query to arrive at these results can be simplified a bit by using EXISTS directly – you don’t need to join User again:

    SELECT DISTINCT (u.id) AS "userId", u."name" AS "userName"
FROM "Follows" x
INNER JOIN "User" u ON u.id = x."userA_ID"
WHERE x."userB_ID" = $1
  AND EXISTS (
    SELECT *
    FROM "Follows" y
    WHERE y."userA_ID" = $1 -- x."userB_ID", for more symmetry
      AND y."userB_ID" = x."userA_ID"
  )

    Btw you can also drop the DISTINCT (u.id) – if your primary keys / unique constraints on User(id) and Follows(userA_ID, userB_ID) are set up correctly then this query can’t generate multiple rows for the same user.

    And you can actually express that WHERE EXISTS(*) as another JOIN:

    SELECT DISTINCT (u.id) AS "userId", u."name" AS "userName"
FROM "Follows" x
INNER JOIN "Follows" y ON (y."userA_ID" = x."userB_ID" AND y."userB_ID" = x."userA_ID")
INNER JOIN "User" u ON u.id = x."userA_ID"
WHERE x."userB_ID" = $1

    Another approach to simplify the query is to move the intersection to a straightforward subquery specifically for mutual followers:

    SELECT u.id AS "userId", u.name AS "userName"
FROM "User" u
JOIN (
  SELECT "userA_ID", "userB_ID" FROM "Follows"
  INTERSECT
  SELECT "userB_ID", "userA_ID" FROM "Follows"
) AS mutual(a, b) ON mutual.a = u.id
WHERE mutual.b = $1
    Login or Signup to reply.
  2. 0

    What you need is an inner join
    and what people mostly don’t know is that you’re not limited to a simple a.id = b.a_id

    Answer

    select left_side.userA_ID as user_one, left_side.userB_ID as user_two
from follows as left_side 
    inner join follows as right_side on 
        left_side.userA_ID = right_side.userB_ID and 
        left_side.userB_ID = right_side.userA_ID

    and get

     user_one | user_two
----------+----------
      456 |      789
      789 |      456
(2 rows)

    As for how to make only one row, I don’t yet know
    If you need this for production, you can leave it at that, though. Making it distinct won’t be faster.

    Explanation

    So quick stuff
    I did

    CREATE TABLE follows (
  userA_ID INTEGER NOT NULL,
  userB_ID INTEGER NOT NULL,
  PRIMARY KEY (userA_ID, userB_ID)
);
INSERT INTO follows (userA_ID, userB_ID) VALUES
  (123, 456),
  (789, 456),
  (456, 789);

    and your main query

    select * 
from follows as left_side 
    inner join follows as right_side on 
        left_side.userA_ID = right_side.userB_ID and 
        left_side.userB_ID = right_side.userA_ID

    result:

     usera_id | userb_id | usera_id | userb_id
----------+----------+----------+----------
      789 |      456 |      456 |      789
      456 |      789 |      789 |      456
(2 rows)

    well, you could fix it up a bit, but that’s ok

    Login or Signup to reply.
  3. 0

    Your requirement could be translated as per each mutual follower, there will be more than 1 related records in table "Follows".

    SELECT u.id AS userId, u."name" AS "userName"
FROM   "User" u 
WHERE 1 < (
    SELECT COUNT(1)
    FROM "Follows" f
    WHERE  ((f."userA_ID" = $1 AND u.id = f."userB_ID")
         OR (f."userB_ID" = $1 AND u.id = "userA_ID"))
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search