CODEWITHSUNDEEP
ruby-on-railsrails-activerecord
Augusto Samamé Barrientos
Augusto Samamé Barrientos

Reputation: 1333

Trouble with simple Rails nested associations where clause for parent

I have the following models:

class Business < ApplicationRecord
  has_many :shopping_trips
end

class ShoppingTrip < ApplicationRecord
  belongs_to :business
  has_many :purchases
end

class Purchase < ApplicationRecord
  belongs_to :shopping_trip
end

So a Business can have many shopping trips, and each of these shopping trips can have many purchases.

I am trying to run a simple query on the Purchase table to find purchases that belong to a particular business. So I'm writing this:

purchases = Purchase.joins(:shopping_trip => :business ).where(:shopping_trip => {:business_id => 1})

Unfortunately it's not working. I get the following error:

ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "shopping_trip"
LINE 1: ...sses"."id" = "shopping_trips"."business_id" WHERE "shopping_...
                                                             ^
: SELECT "purchases".* FROM "purchases" INNER JOIN "shopping_trips" ON "shopping_trips"."id" = "purchases"."shopping_trip_id" INNER JOIN "businesses" ON "businesses"."id" = "shopping_trips"."business_id" WHERE "shopping_trip"."business_id" = $1

The join looks about right but the where clause seems to fail.

Upvotes: 0

Views: 136

Answers (3)

max
max

Reputation: 102249

A better solution is to set up indirect associations so that you can query through the join model without manually joining:

class Business < ApplicationRecord
  has_many :shopping_trips
  has_many :purchases, through: :shopping_trips
end

class ShoppingTrip < ApplicationRecord
  belongs_to :business
  has_many :purchases
end

class Purchase < ApplicationRecord
  belongs_to :shopping_trip
  has_one :business, through: :shopping_trip
end

You can now query from either side:

@business = Business.eager_load(:purchases).find(1)
@purchases = @business.purchases

# or
@purchases = Purchase.eager_load(:buisness).where(businesses: { id: 1 })

Upvotes: 1

Arun P
Arun P

Reputation: 55

Check This...

all_purchase = Purchase.all

all_purchase.each do |each_purchase| each_purchase.shopping_trip.business end

Upvotes: -1

Pavan
Pavan

Reputation: 33542

ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: missing FROM-clause entry for table "shopping_trip"

You need to specify table name not the association name inside the where. So shopping_trip should be shopping_trips

purchases = Purchase.joins(:shopping_trip => :business ).where(:shopping_trips => {:business_id => 1})

Upvotes: 1

Related Questions