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
问题是:
我在postgres中使用了这个...
Editor.joins(:roles).group('editors.id').having('count(roles) = 0')
但我肯定这是不对的
# 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>]
100
为了构建一个匹配缺失内容的查询，我们需要让编辑器没有角色.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>]
100