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

Postgresql – Search for a list of users and check if they are also following you

I’m fairly new to PostgreSQL after not doing SQL for over 10 years.

I’m trying to get a list of users but also check if the searcher is following those said users in the list.

The data looks like so:

enter image description here

enter image description here

So far I have this, but it makes duplicates.

SELECT
    users.id, 
    users.name,
    "user-follows"."userToFollow",
    "user-follows"."userWhoIsFollowing"
FROM database.users
    LEFT JOIN database."user-follows"
    ON ("user-follows"."userWhoIsFollowing" = 'BNvAn_')
WHERE users."name" ILIKE '%olly%'
ORDER BY users."updatedAt" DESC
LIMIT 30

enter image description here

As a bonus an "isFollows" boolean would be nice too.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I figured this out with sub-queries, but not sure it's optimal.

    SELECT
    users.id,
    users.name,
    (
        SELECT "user-follows"."userToFollow" 
        FROM vegiano."user-follows" 
        WHERE "user-follows"."userToFollow" = users.id AND "user-follows"."userWhoIsFollowing" = 'BNvAn_'
    ) as "isFollowing"
FROM vegiano.users
WHERE users."name" ILIKE '%%'
ORDER BY users."updatedAt" DESC
LIMIT 30

  2. 0

    Add a condition on userToFollow to the join so you only join to the specific row in the following table that applies to you following the particular user:

    SELECT
    u.id, 
    u.name,
    (f.userToFollow is not null) as is_following
FROM users u
LEFT JOIN user_follows f
    ON f.userToFollow = u.id
    AND f.userWhoIsFollowing = 'BNvAn_'
WHERE u."name" ILIKE '%olly%'
ORDER BY u.updatedAt DESC
LIMIT 30

    If the outer join fails to find a row, all columns in the joined table will be null, so f.userToFollow being null tells you if you’re following.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search