match all in active record relations in a query

Question

I need an activerecord query to Match ALL items in a params array.

Lets say user has_many roles. and each role has a name.

when i pass ['actor', 'producer', 'singer']. I expect the query to return me the users with all the those three roles or more.

But my method implementation below would return users with having atleast one role name matching to those in the array passed

My current method gives results based on finding any of the tags, not "MATCH ALL"

class User < ActiveRecord::Base
    has_many :roles

    def self.filter_by_roles(roles)
        User.joins(:roles).includes(:roles).where(:roles => {:name => roles})
    end
end

I don't want to do any array operations after the query checking if the returned result objects contain all the roles or not. This is because I need the Active Record Relation object to be returned from this.

Thanks in advance.

Answer 1

Try this.

User.joins(:roles).includes(:roles).where(:roles => {:name => roles}).group('usermail').having("COUNT(DISTINCt role_id) = 3")

assuming that field usermail is using to identify users.

Answer 2

You could try this:

def self.filter_by_roles(roles)
  scope = User.joins(:roles).includes(:roles)
  roles.each do |role|
   scope = scope.where(roles: {name: role})
  end
  scope
end

It's untested, so I'm not sure whether it works.

Answer 3

If you pass role_ids array ([1,2,3]) you can do smth like this:

def self.filter_by_roles(role_ids)  
  User.select{|user| (role_ids - user.role_ids).empty?}
end

But if you pass roles by title (['actor', 'producer', 'singer']) you need smth like this:

def self.filter_by_roles(roles)
  role_ids = Role.find_all_by_title(roles).map(&:id)
  User.select{|user| (role_ids - user.role_ids).empty?}
end