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.