>我有模型User
class User < ActiveRecord::Base
has_many :sent_messages, class_name: 'DirectMessage', foreign_key: :sender_user_id
has_many :received_messages, class_name: 'DirectMessage', foreign_key: :receiver_user_id
end
和模型DirrectMessage
class DirectMessage < ApplicationRecord
belongs_to :sender_user, class_name: 'User', foreign_key: :sender_user_id
belongs_to :receiver_user, class_name: 'User', foreign_key: :receiver_user_id
end
我需要编写查询来获取所有消息,其中:
sender_user_id = options[:current_user].id AND receiver_user.role = 'admin'
或receiver_user_id = options[:current_user].id AND sender_user.role = 'admin'
我只需要获取当前用户和每个管理员之间的所有消息。
现在,我正在尝试这样的事情:
DirectMessage.all.joins(:sender_user, :receiver_user).where('(sender_user_id = ? AND sender_user.role = ?) OR ( receiver_user_id = ? AND receiver_user.role = ?)', options[:current_user].id, 'admin', options[:current_user].id, 'admin')
但它只返回我#<DirectMessage::ActiveRecord_Relation:0x007f8ead7879c0>
.如果我尝试从中获取第一条记录,我会得到错误:
活动记录::语句无效异常: PG::未定义表: 错误: 缺少表 "sender_user" 第 1 行的 FROM 子句条目: ...r_id = 'b4de3f59-b234-459a-adb1-2a6c1a135919' 和sender_use...
请帮我写一个正确的查询。谢谢。
您需要创建一个将current_user值传递到模型的方法,当前用户在模型中不可用,只有视图和控制器可用。 注意:您可以将这些方法定义为作用域或方法。
class DirectMessage < ApplicationRecord
...
def self.messages_from_user(user)
where(sender_user: user, receiver_user: User.admins)
end
end
class User < ApplicationRecord
...
def self.admins
where(role: 'admin')
end
end
# then in your controller or view you can do
@direct_messages = DirectMessage.messages_from_user(current_user)
我找到了一个可行的解决方案。首先,我们需要向模型添加范围User
:
class User < ActiveRecord::Base
has_many :sent_messages, class_name: 'DirectMessage', foreign_key: :sender_user_id
has_many :received_messages, class_name: 'DirectMessage', foreign_key: :receiver_user_id
scope :admin, -> { where(role: 'admin') }
end
所以现在查询将如下所示:
DirectMessage.all.where('(sender_user_id = :current_user_id AND receiver_user_id IN (:admin_ids)) OR (sender_user_id IN (:admin_ids) AND receiver_user_id = :current_user_id)',
current_user_id: options[:current_user].id, admin_ids: User.admin.ids)
我不认为会有超过几十个管理员,所以这个解决方案非常好。