1

I have a Rails 5 application and I'm having a problem writing a finder. I have a PostGres 10 database. I want to query all the invoices that belong to a particular business. I have these models ...

class Business < ApplicationRecord
  belongs_to :owner, :optional => true

class Owner < ApplicationRecord
  has_many :invoices, :through => :accountants
  has_one :business, :dependent => :nullify

class Accountant < ApplicationRecord
  belongs_to :owner, :optional => false, :inverse_of => :accountants, :touch => true
  has_many :invoices, :dependent => :nullify

I would like to get all Invoices given a small business, so I tried this

@invoices = Invoice.joins(:accountant => :owner).where(:owners => { :business_id => @business_id })

but this results in the below error

ActiveRecord::StatementInvalid:
       PG::UndefinedColumn: ERROR:  column owners.business_id does not exist
       LINE 1: ...owners"."id" = "accountants"."owner_id" WHERE "owners"...
                                                                    ^
       : SELECT "invoices".* FROM "invoices" INNER JOIN "accountants" ON "accountants"."id" = "invoices"."accountant_id" INNER JOIN "owners" ON "owners"."id" = "accountants"."owner_id" WHERE "owners"."business_id" = $1 ORDER BY "invoices"."created_at" DESC

It is not an option to add an additional database column. Is there a way I can rewrite the above query to satisfy my models?

Eyeslandic
  • 14,553
  • 13
  • 41
  • 54
satish
  • 703
  • 5
  • 23
  • 52
  • I'm not understanding how to apply the answer there to my question. – satish May 22 '20 at 23:27
  • You must have some column as a foreign key from `owners` to `business`. By Rails convention it should be `business_id` but the error says you don't have that column. – Eyeslandic May 23 '20 at 09:40
  • How are tables connected in the db? – Eyeslandic May 23 '20 at 09:40
  • @Eyeslandic, Correct, there is a business.owner_id column but not the other way around. At this time, it is not an option to add or remove columns in the database. – satish May 25 '20 at 00:37

0 Answers0