Four way JOIN (many-to-many + lookup); preventing duplicates when using scopes

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

Nevermind. I don’t know what I was thinking using @request.users
directly
when I should have been using @request.request_user_roles instead.

This works fine:
@request.request_user_roles.joins(:request_role_types).merge(RequestRoleType.is_initiator)

Then I can get the User and the user’s Role from the RequestUserRole
objects returned.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs