Rails query records based on attribute of association

Question

I'm trying to create a query based on an attribute of an associated record but am failing miserably

I've the following models:

class CompanyPay < ActiveRecord::Base
  has_many :ee_pay
  has_many :pay, :through => :ee_pay 
end

class EePay < ActiveRecord::Base
  belongs_to :company_pay
  has_many :pay
end

class Pay < ActiveRecord::Base
  belongs_to :ee_pay
  has_one :company_pay, :through => :ee_pay
end

I'm trying to create a query that returns all Pay objects where the attribute apply_paye on their associated CompanyPay is true.

I've been trying to get my head around joins, scope, lambdas etc since yesterday but I'm just confusing myself more at the stage.

The query I want would be something like this but I just don't know how to phrase it properly

@records = Pay.where(employee_id: current_employee.id, {|p| p.company_pay.apply_paye == true}).order(:id)

Can anyone help me out. Can I write a one line query? Should I be using a join? Should a lambda be used?

Thanks for looking

Edit 1

I've edited the original post as I gave the wrong attribute name by mistake (should be apply_paye not taxable).

Tried the following query provided by fylooi

@records = Pay.joins(:ee_pay => :company_pay).where(:pay => {:employee_id => current_employee.id }, :company_pay => {:apply_paye => true})

But am getting the following error:

    ActiveRecord::StatementInvalid in PayLinesController#update

    PG::UndefinedTable: ERROR: missing FROM-clause entry for table "pay" LINE 1: ...any_pays"."id" = "ee_pays"."company_pay_id" WHERE "pay"."emp... ^ : SELECT "pays".* FROM "pays" INNER JOIN "ee_pays" ON "ee_pays"."id" = "pays"."ee_pay_id" INNER JOIN "company_pays" ON "company_pays"."id" = "ee_pays"."company_pay_id" WHERE "pay"."employee_id" = 1 AND "company_pay"."apply_paye" = 't' ORDER BY "pays"."id" ASC

Edit 2 - Solved

Pluralising the table names in the query worked, as per the suggestion from David Aldridge.

This is the final query:

@records = Pay.joins(:ee_pay => :company_pay).where(:pays => {:employee_id => current_employee.id }, :company_pays => {:apply_paye => true})

Thanks for your help

Answer 1

Something like this?

Pay.joins(:ee_pay => :company_pay).where(:company_pay => {:taxable => true})