CODEWITHSUNDEEP
ruby-on-railsjoinassociationshas-many-throughhas-many
hobbydev
hobbydev

Reputation: 1709

Join for has_many_through in rails

I have 3 models as following.

class Order
 belongs_to :item
 belongs_to :category
end

class Item
 has_many :orders
 belongs_to :category
end

class Category
 has_many :items
 has_many :orders, through: :items
end

I want to join the tables like Order.joins(:item).joins(:category), but it's not working.

Desired SQL is 

SELECT * FROM `orders` 
INNER JOIN `items` ON `items`.`id` = `orders`.`item_id` 
INNER JOIN `categories` ON `items`.`category_id` = `categories`.`id`

I hope your helps.

Upvotes: 0

Views: 425

Answers (3)

max
max

Reputation: 101811

The proper way to setup these associations is:

class Order < ApplicationRecord
  belongs_to :item
  # This references items.category_id
  has_one :category, through: :item
end

class Item < ApplicationRecord
  has_many :orders
  belongs_to :category
end

class Category < ApplicationRecord
  has_many :item, through: :orders
  has_many :orders
end

You want to remove the orders.category_id column (if it exists) and use an indirect association through the items table to avoid duplication. The semantics of belongs_to and has_one can be confusing but belongs_to assumes that the foreign key is on this model (orders), while has_one places it on the other models table (items).

This will let you join/include/eager_load the association with:

irb(main):002:0> Order.joins(:category)
  Order Load (1.4ms)  SELECT  "orders".* FROM "orders" INNER JOIN "items" ON "items"."id" = "orders"."item_id" INNER JOIN "categories" ON "categories"."id" = "items"."category_id" LIMIT $1  [["LIMIT", 11]]

And as you can see Rails will handle joining the join table (items) automatically.

If you want both associations to be loaded you can use a hash or just list both:

Order.eager_load(item: :category) 
Order.eager_load(:item, :category)

Upvotes: 1

arieljuod
arieljuod

Reputation: 15838

I'm a little confused because Order and Item both belongs_to Category and Category already has_many Orders with that setup, the :through option is unnecesary.

For your desired output I guess you want to do a nested join (order > item > category) instead of multiple joins (order > item+category)

https://guides.rubyonrails.org/active_record_querying.html#joining-multiple-associations

12.1.3.1 Joining Nested Associations (Single Level) Article.joins(comments: :guest)

This produces:

SELECT articles.* FROM articles INNER JOIN comments ON comments.article_id = articles.id INNER JOIN guests ON guests.comment_id = comments.id

So, you should do something like Order.joins(item: :category)

Upvotes: 1

Marcin Kołodziej
Marcin Kołodziej

Reputation: 5313

The syntax you're looking for is

Order.joins(item: :category)

Check here for more information.

Upvotes: 1

Related Questions