如何使用ActiveRecord列出没有角色(多对多)的用户

Question

class Editor < ApplicationRecord
has_many :editor_roles, dependent: :destroy
has_many :roles, through: :editor_roles
end

class Roles < ApplicationRecord
has_many :editor_roles, dependent: :destroy
has_many :editors, through: :editor_roles
end

class EditorRole < ApplicationRecord
belongs_to :editor
belongs_to :role
end

问题是：如何列出没有角色的编辑？谁拥有roles_count==0？

我用这个和postgres。。。

Editor.joins(:roles).group('editors.id').having('count(roles) = 0')

但我确信不正确

Answer 1

# TLDR
Editor.left_joins(:roles).where(roles: { id: nil })
Editor.left_joins(:editor_roles).where(id: nil)
# rails 6.1+ https://github.com/rails/rails/pull/34727
Editor.where.missing(:roles)                                     
Editor.where.missing(:editor_roles)

---

Editor.create([{}, {roles: [Role.create]}, {roles: [Role.create, Role.create]}])
# <Editor:0x00007f63a40962a8 id: 1> # no roles
# <Editor:0x00007f63a409ebb0 id: 2> # 1 role
# <Editor:0x00007f63a5ffa518 id: 3> # 2 roles

要获取具有角色的编辑器，请使用joins。这将过滤掉没有角色的编辑，因此不利于找到缺失的内容。

>> Editor.joins(:roles)                                                                
=> [#<Editor:0x00007f639fad9ee0 id: 2>, 
#<Editor:0x00007f639fad9d50 id: 3>, 
#<Editor:0x00007f639fad9c60 id: 3>]
# NOTE: to avoid duplicates use `distinct`
>> Editor.joins(:roles).distinct                                                         
=> [#<Editor:0x00007f63a612bba8 id: 2>,
#<Editor:0x00007f63a612bae0 id: 3>]

为什么活动记录联接方法会产生重复的值

为了构建一个匹配缺失内容的查询，我们需要让编辑器不带角色。left_joins正是这样做的。

>> Editor.left_joins(:roles)
=> [#<Editor:0x00007f639fa962d0 id: 1>,
#<Editor:0x00007f639fa96618 id: 2>,
#<Editor:0x00007f639fa96528 id: 3>,
#<Editor:0x00007f639fa963e8 id: 3>]
# NOTE: we can see the returned database result
#       and select `roles.id` column to get some context.
>> ActiveRecord::Base.connection.execute(Editor.select("editors.*", "roles.id as role_id").left_joins(:roles).to_sql).to_a
=> [{"id"=>1, "role_id"=>nil}, # NOTE: this `role_id` is what we're looking for
{"id"=>2, "role_id"=>1},
{"id"=>3, "role_id"=>2},
{"id"=>3, "role_id"=>3}]
# NOTE: now just find where `roles.id` is `nil`
>> Editor.left_joins(:roles).where(roles: { id: nil})
=> [#<Editor:0x00007f639fc2c068 id: 1>]     

Rails对此查询有一个快捷方式：

>> Editor.where.missing(:roles)
=> [#<Editor:0x00007f639fd33358 id: 1>] 
# NOTE: this works too and does a single join
>> Editor.where.missing(:editor_roles)
=> [#<Editor:0x00007f63a59e0dd0 id: 1>]

---

https://api.rubyonrails.org/classes/ActiveRecord/QueryMethods/WhereChain.html#method-i-missing

Answer 2

我设法使用

编辑.all.select｛|e|e.roles.count＝＝0｝

出于某种原因，我仍然不喜欢这个解决方案，仍然想知道如何用SQL方式