How can I query a has_many through relationship which contains an additional attribute in the join table?

Question

Situation

In my application a user can create a plan. Once the plan is created, the user can define the stakeholders/team members of the plan. Each team member becomes a responsibility assigned. There are many plans and users can be stakeholders of multiple plans and in each plan they have a different responsibility.

Example

Admin creates a plan and assigns 10 users as stakeholders. 1 is accountable, 2 are responsible, 7 just need to be informed

What I did so far

I set up a has_many through relationship between two models:

class User < ActiveRecord::Base
  has_many :assignments
  has_many :plans, through: :assignments
end

class Plan < ActiveRecord::Base
  has_many :assignments
  has_many :users, through: :assignments
end

The assignment table looks like this:

    create_table :assignments do |t|
        t.belongs_to :user
        t.belongs_to :plan
        t.string :responsibility
    end

    add_index :assignments, [:user_id, :plan_id]

the column responsibility contains one of 4 different values (responsible, accountable, informed, consulted.)

What I am looking for

I know how I can query all users that have been assigned to the plan (@plan.users.to_a) but I do not know how I can additionally supplement the user information with the responsibility they have in this plan.

The query I need is something along the lines of:

Select users which belong to plan X by looking at the assignment table. Do not just use the assignment table to identify the user, but also take the value from the responsibility column in the assignment table and return an array which contains:

• user.first_name
• user.last_name
• user.responsibility (for this specific plan)

Answer 1

We had this exact requirement, and solved it in 2 ways:

---

Use an SQL Alias Column

The first way is to use an SQL Alias Column & append it to your has_many association, like this:

Class User < ActiveRecord::Base
  has_many :assignments
  has_many :plans, -> { select("#{User.table_name}.*, #{Plan.table_name}.responsibility AS responsibility") }, through: :assignments, dependent: :destroy
end

This will allow you to call @user.plans.first.responsibility, and will fail gracefully if no record exists

---

Use ActiveRecord Association Extensions

This is the best, but more complicated, way, as it uses the proxy_association object in memory (instead of performing another DB request). This script took us 2 weeks to create, so we're very proud of it!! Not tested with Rails 4.1:

#app/models/user.rb
Class User < ActiveRecord::Base
    has_many :assignments
    has_many :plans, through: :assignments, extend: Responsibility
end

#app/models/plan.rb
Class Plan < ActiveRecord::Base
    attr_accessor :responsibility
end

#app/models/concerns/Responsibility.rb
module Responsibility

    #Load
    def load
        captions.each do |caption|
            proxy_association.target << responsibility
        end
    end

    #Private
    private

    #Captions
    def captions
        return_array = []
        through_collection.each_with_index do |through,i|
            associate = through.send(reflection_name)
            associate.assign_attributes({responsibility: items[i]}) if items[i].present?
            return_array.concat Array.new(1).fill( associate )
        end
        return_array
    end

    #######################
    #      Variables      #
    #######################

    #Association
    def reflection_name
        proxy_association.source_reflection.name
    end

    #Foreign Key
    def through_source_key
        proxy_association.reflection.source_reflection.foreign_key
    end

    #Primary Key
    def through_primary_key
        proxy_association.reflection.through_reflection.active_record_primary_key
    end

    #Through Name
    def through_name
        proxy_association.reflection.through_reflection.name
    end

    #Through
    def through_collection
        proxy_association.owner.send through_name
    end

    #Responsibilities
    def items
        through_collection.map(&:responsibility)
    end

    #Target
    def target_collection
        #load_target
        proxy_association.target
    end

end

Answer 2

Query the appointement table directly fitering for all the users in the current plan:

Appointement.select(:id).where(user_id: Plan.find(params[:id]).users.pluck(:user_id), plan_id: params[:id]).group(:id).having('count(*) = ?', Plan.find(params[:id]).users.count)