I have two “top-level” entities joined with a third table (many-to-many relationship) which uses yet another table (lookup/reference table) to describe the nature of the relationship of the join. ActiveRecord is adding an unnecessary JOIN which results in duplicate rows being returned.
[See model class definitions below.]
users INNER JOIN request_user_roles INNER JOIN request_role_types
@request.users.count # 1
users INNER JOIN request_user_roles INNER JOIN request_role_types INNER JOIN request_user_roles
@request.users.is_request_initiator.count # 898 @request.users.is_request_initiator.uniq.count # 1
users INNER JOIN request_user_roles INNER JOIN request_role_types INNER JOIN request_user_roles
@request.users.joins(:request_role_types).merge(RequestRoleType.is_initiator).count # 898
I want to select users for a particular request based on their role with respect to it.
I can write this in SQL, and I can write this in a single AR query, but I would like to simplify my code with scopes, if possible. What I can’t do is structure a scope such that I don’t return duplicates. Or, to put it another way, I can’t structure the scope to not automatically include the extra JOIN (on request_user_roles). Is there a way to express this with AR scopes or in another modular, re-usable way? The uniq() feels dirty.
relevant classes
User
|
RequestUserRole - RequestRoleType
|
Request
class User < ActiveRecord::Base has_many :request_user_roles has_many :requests, :through => :request_user_roles has_many :request_role_types, :through => :request_user_roles
scope :is_request_initiator, joins(:request_role_types).where(:request_role_types => {:request_role_type => ‘Initiator’}) end
class Request < ActiveRecord::Base has_many :request_user_roles has_many :users, :through => :request_user_roles has_many :request_role_types, :through => :request_user_roles end
class RequestUserRole < ActiveRecord::Base belongs_to :user belongs_to :request belongs_to :request_role_type end
class RequestRoleType < ActiveRecord::Base has_many :request_user_roles
scope :is_initiator, where(:request_role_type => ‘Initiator’) end
