I have three tables like this:
messages
user_id | message
2 | 'foo'
3 | 'bar'
blacklists
user_id | blacklister_id
1 | 2
users
id | name
1 | 'me'
2 | 'blacklister'
3 | 'my friend'
I'm - user with id:1 from table users. I do not want to see messages from users that I added to t开发者_JS百科he blacklist (i added user with id:2 in blacklist). How can I display a messages without messages from blacklist users (with user_id:2) in one query?
Now I'm doing it this way in Rails:
@all_messages = Message.all
@filter_messages = Array.new
for message in @all_messages
@blacklist = Blacklist.where("user_id = ? and blacklister_id = ?",current_user.id,message.user_id).first
if @blacklist.nil?
@messages << message
end
end
return @messages
It's really terrible and I want to know how I can refactor this with one postgresql query.
select *
from messages m
inner join users u on m.user_id = u.id
left outer join blacklists b on u.id = b.user_id
where b.user_id is null
精彩评论