开发者

Want to exclude from the query some records, but I do not know how

开发者 https://www.devze.com 2023-03-19 14:38 出处:网络
I have three tables like this: messages user_id | message 2| \'foo\' 3| \'bar\' blacklists user_id | blacklister_id

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
0

精彩评论

暂无评论...
验证码 换一张
取 消