Deep relationships in Rails

Question

I have some projects. Those projects have users through memberships.

However, those users belong to companies. Question is, how do I find out which companies can access a project?

Ideally I'd be able to do project.users.companies, but that won't work.

Is there a nice, pleasant way of doing this?

Answer 1

You should be able to set the relationships up to allow: project.users.companies.

The associations are:

Project has_one User belongs_to Company

Answer 2

I think you can do something like this.

class Project < ActiveRecord::Base
  def self.companies
    Company.all(:conditions => { :users => { :project_id => @project.id } }, :include => :users)
  end
end

But it's been a while since I have used these features, so I may be rusty.

Edit: this may not work. Not sure if I got the :include or :join right. But like I said, I'm rusty.

Answer 3

I'm assuming that you have this:

class Project < ActiveRecord::Base
  has_and_belongs_to_many :users
end

class User < ActiveRecord::Base
  has_and_belongs_to_many :projects
  belongs_to :company
end

class Company < ActiveRecord::Base
  has_many :users
end

And you want to get project.companies. The less painful one I can imagine is:

class Project < ActiveRecord::Base
  has_and_belongs_to_many :users
  def companies
    Company.all(
      :joins => {:users => :projects}, 
      :conditions => {'projects_users.project_id' => self.id}
    ).uniq
  end
end

Notice the uniq at the end. It will remove duplicated companies.

Answer 4

The other answers appear to be neglecting memberships that you mentioned. If those are actual objects which you have a recording of, then what you choose to do depends on the size of your tables. If they aren't terribly huge, then the "more OO" solution would probably look something like this:

class Project < ActiveRecord::Base
  has_many :memberships
  has_many :users, :through => :memberships

  def user_companies
    self.users.map {|user| user.companies}.flatten.uniq
  end
end

class Membership < ActiveRecord::Base
  belongs_to :user
  belongs_to :project
end

class User < ActiveRecord::Base
  has_many :memberships
  has_many :projects, :through => :memberships
  belongs_to :company
end

class Company < ActiveRecord::Base
  has_many :users
end

This might not perform great, as it pulls a lot of data from the database and then does all the filtering in memory, but it's pretty intuitive to read. If you wanted to shove all the computing down into the database, I think a good solution would likely look something like this in the Project class instead:

def user_companies
  Company.find_by_sql("SELECT company.* 
    FROM companies, users, memberships 
    WHERE companies.id = users.company_id 
    AND users.id = memberships.user_id 
    AND memberships.project_id = #{self.id}")
end

It's a little less clean but will put most of the processing nearest the data, and at only a three table join should not end up generating such a huge number of tuples that your DBMS falls apart at the seems.