How to list users who has no roles (Many to Many) with ActiveRecord-CodePudding

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

Question is: How can i list editors who has no roles only? who has roles_count == 0 ?

I used this with postgres...

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

but i'm sure it's incorrect

CodePudding user response：

I managed to do it useing:

Editor.all.select { |e| e.roles.count == 0 }

still i don't like this solution for some reason, still want to know how to do it in SQL way

CodePudding user response：

# TLDR
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

To get editors with roles, use joins. This will filter out editors who have no roles, so it is not good for finding something that's missing.

>> 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>]

Why does the active records join method produce duplicate values?

To build a query that matches something that's missing, we need to keep the editors without roles. left_joins does exactly that.

>> 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 has a shortcut for this query:

>> 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