Search for a list of users and check if they are also following you-CodePudding

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:

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

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

CodePudding user response：

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

CodePudding user response：

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.