CODEWITHSUNDEEP
sqlpostgresql
user1105595
user1105595

Reputation: 601

postgres: filter out some results with join

I’m trying to construct SQL query (I’m running postgres) which would filter out some posts:

Lets say, that I’ve fot User model(id, name, and so on...), Blacklist model(with id, user_id, blacklisted_user_id) nad Post model(id, author_id, title, and so on).

Imagine that user A(id=5) blocks user B(id=10).

Neither user A nor B should see their posts. I’m trying with query which looks sth. like this:

SELECT posts.* FROM "posts" 
LEFT JOIN blacklists b ON (b.user_id = posts.author_id OR 
                           b.blacklisted_user_id = posts.author_id)
WHERE (b.user_id = 5 AND b.blacklisted_user_id = posts.author_id) OR
      (b.user_id = posts.author_id AND b.blacklisted_user_id = 5)

However, result is exactly opposite that I need: I’m getting only posts from blacklisted user.

When I use b.user_id != 5, I’m getting empty response.

Upvotes: 1

Views: 5703

Answers (1)

Richard Huxton
Richard Huxton

Reputation: 22893

You're repeating the blacklisted_user_id=posts.author_id which is unnecessary.

Then, you presumably want posts that don't match the blacklist. Something like:

SELECT posts.* FROM posts 
LEFT JOIN blacklists b ON (b.user_id = posts.author_id OR 
                           b.blacklisted_user_id = posts.author_id)
WHERE posts.author_id = 5 AND b.user_id IS NULL

Is that the sort of thing you wanted?

Upvotes: 1

Related Questions