Rails join two tables based on relation in third

Question

I want to join 2 tables, but I need to use 3th to set a relation between them.

Each Order is prepared by an Employee. I want to find OrderDetails only for specific employees. To check that, I need to find who was assigned to shop orders.

I'm almost sure that I know how to do it in SQL. Just concept query:

SELECT OrderDetails.payment_id FROM OrderDetais
    INNER JOIN ShopOrders ON ShopOrders.id = OrderDetails.shop_order_id
    INNER JOIN Employees ON Employees.id = ShopOrders.employee_id
WHERE Employees.id IN (5, 6, 8)

I'm just trying to write the same in Rails:

query = OrderDetail
        .where('order_details.created_at > ? AND order_details.created_at < ?', 1.year.ago, 2.days.ago)
query = query.joins(:shop_order)

I use to_sql to check if my query looks good. Everything goes ok, until I join query.joins(: employee)

query = OrderDetail
        .where('order_details.created_at > ? AND order_details.created_at < ?', 1.year.ago, 2.days.ago)
query = query.joins(:shop_order)
query = query.joins(:employee)

then I receive an error:

ActiveRecord::ConfigurationError: Can't join 'OrderDetail' to association named 'employee'; perhaps you misspelled it?>

I tried to follow Joining Nested Associations (Multiple Level). But I don't know how to use it properly.

In OrderDetail model there is:

delegate :employee, to: :shop_order, allow_nil: true

In ShopOrder model:

  belongs_to :employee, optional: false
  has_many :order_details, dependent: :restrict_with_error

And Employee model:

  has_many :shop_orders
  has_many :order_details, through: :shop_orders

GProst · Accepted Answer · 2019-07-28 23:51:00Z

2

Basing on the 'Joining Nested Associations (Multiple Level)' reference you provided the following should do what you want:

query
  .joins(shop_orders: :employee)
  .where(employees: { id: [5, 6, 8] })

answered Jul 28, 2019 at 23:51

GProst

10.3k3 gold badges28 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

eudaimonia Over a year ago

I saw this in documentation, but I receive same error "Can't join 'OrderDetail' to association named 'employee'; `

engineersmnky Over a year ago

@eudaimonia there is no way you received that error if you used the solution provided in this answer. If anything it would be Can't join 'ShopOrder' to association named 'employee' which also would not occur based on your post

eudaimonia Over a year ago

yes second one. but .joins(shop_order: :employee) fixed that

eudaimonia · Accepted Answer · 2019-07-28 23:07:19Z

1

I fixed it like that:

query.joins(:shop_order)
     .joins('JOIN employees ON employees.id = shop_orders.employee_id')
     .where(employees: { id: [5, 6, 8] })

answered Jul 28, 2019 at 23:07

eudaimonia

1,5362 gold badges17 silver badges30 bronze badges

Collectives™ on Stack Overflow

Rails join two tables based on relation in third

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related