Rails join through 2 other tables

Question

I am trying to join tables to get an object.

I have these models:

class Company < ApplicationRecord
    has_many :users
end

class Claim < ApplicationRecord
  has_many :uploads, dependent: :destroy
  validates :number, uniqueness: true
  belongs_to :user, optional: true
end

class User < ApplicationRecord
  belongs_to :company
  has_many :claims
end

Basically I want to select all claims that belong to users that belong to a company.

Somethings I have tried:

(This works but is terrible and not the rails way)

@claims = []
@company = Company.find(params[:id])
@users = @company.users
@users.each do |u|
  u.claims.each do |c|
    @claims.push(c)
  end
end

@claims = @claims.sort_by(&:created_at)
if @claims.count > 10
  @claims.shift(@claims.count - 10)
end
@claims = @claims.reverse

This is close but doesn't have all the claim data because its of the user:

    @claims = User.joins(:claims, :company).where("companies.id = users.company_id").where("claims.user_id = users.id").where(company_id: params[:id]).order("created_at DESC").limit(10)

I tried this but keep getting an error:

    @claims = Claim.joins(:user, :company).where("companies.id = users.company_id").where("claims.user_id = users.id").where(company_id: params[:id]).order("created_at DESC").limit(10)

error: ActiveRecord::ConfigurationError (Can't join 'Claim' to association named 'company'; perhaps you misspelled it?)

Any ideas what I should do or change?

Answer 1

Based on your relations, you should use

Claim.joins(user: :company)

Because the Company is accessible through the relation Claim <> User. If you wanted to join/preload/include/eager load another relation, let's say if Claim belongs_to :insurance_company, then you would add it like this:

Claim.joins(:insurance_company, user: :company)

Similar questions:

• Join multiple tables with active records
• Rails 4 scope to find parents with no children

---

That being said, if you want to

select all claims that belong to users that belong to a company

Then you can do the following:

Claim
  .joins(:user) # no need to join on company because company_id is already on users
  .where(company_id: params[:id])
  .order(claims: { created_at: :desc })
  .limit(10)

Tada!