Joining Nested Associations (Multiple Level)

Question

I have the following models and relationships:

A User has many Offers (where he/she is the seller), an Offer has many Purchases, a Purchase has many Accbooks

Models and associations:

class User < ApplicationRecord
  has_many :offers, foreign_key: :seller_id
  has_many :purchases, foreign_key: :buyer_id
end

class Offer < ApplicationRecord
  has_many :purchases
  belongs_to :seller, class_name: 'User'
end

class Purchase < ApplicationRecord
  belongs_to :offer
  belongs_to :buyer, class_name: 'User'
  has_one :seller, through: :offer
  has_many :accbooks,  class_name: 'Admin::Accbook', foreign_key: 'purchase_id' 
end

module Admin
  class Accbook < ApplicationRecord
    belongs_to :purchase
  end
end  

I want to get all the Accbooks of any given user (as a seller). The equivalent SQL statement would look like this:

SELECT  "accbooks".* 
FROM "accbooks" 
INNER JOIN "purchases" ON "purchases"."id" = "accbooks"."purchase_id"
INNER JOIN "offers" ON "offers"."id" = "purchases"."offer_id"
INNER JOIN "users" ON "users"."id" = "offers"."seller_id"
WHERE "users"."id" = ?

So far I've tried this:

Admin::Accbook.joins( {purchase: :offer} )

Which gives me this SQL as a result:

SELECT  "accbooks".*
FROM "accbooks"
INNER JOIN "purchases" ON "purchases"."id" = "accbooks"."purchase_id"
INNER JOIN "offers" ON "offers"."id" = "purchases"."offer_id"

Now I don´t know how to add the join to the User model, and then how to add the Where condition.

Thanks for any insight.

Fineman

You can do Admin::Accbook.joins(purchase: { offer: :seller } ).where(users: { id: 123 }) – Sport

@MrYoshiji: Your answer works nicely with a slight variation in the Where, i.e.: Admin::Accbook.joins(purchase: { offer: :seller } ).where(offers: { seller_id: 123 }). If you post this as an answer I will mark it as the correct one. Thanks! – Conciliator

Answer 1

You can joins the relations together and apply where clause on the joined relations:

Admin::Accbook
  .joins(purchase: :offer)
  .where(offers: { seller_id: 123 })

---

A thing to know, where uses the DB table's name. joins (and includes, eager_load, etc) uses the relation name. This is why we have:

Admin::Accbook
  .joins(purchase: :offer)
  #                 ^^^^^ relation name
  .where(offers: { seller_id: 123 })
  #      ^^^^^^ table name

Sport

Answer 2

Try Adding following association in users.rb

has_many :accbooks, through: :purchases

Illbred

Your answer is helpful, thanks a lot. However, it gives me all Accbooks where the user is the buyer (through purchases) and I am looking for Accbooks where she is the seller. – Conciliator

Answer 3

So your problem is user is acting as 2 roles for same accounts. You can try something like below stuff

class User < ApplicationRecord
  has_many :offers, foreign_key: :seller_id
  has_many :purchases, foreign_key: :buyer_id
  has_many :offers_purchases,
           through: :offers,
           :class_name => 'Purchase',
           :foreign_key => 'offer_id',
           :source => :purchases
end

Opinionated

Thanks, but this does not take into consideration the relationship with the Accbooks table. I need Accbooks that are children of a Purchase that is child of an Offer where a given user is the seller. – Conciliator