CODEWITHSUNDEEP
mysqlsqlruby-on-railsrubyactiverecord
Pedro Sturmer
Pedro Sturmer

Reputation: 604

Complex SQL to ActiveRecord

i'm getting a lot confused by includes hashes and joins in Active Record, i would like to know how i can do this kind of query on Rails

SELECT 
p.name,
o.id AS order_id,
op.id AS order_product_id,
op.name
FROM orders o 
INNER JOIN order_products AS op
ON op.order_id = o.id 
INNER JOIN product_providers AS pp
ON pp.id = op.product_provider_id 
INNER JOIN providers as p 
ON p.id = pp.provider_id 
WHERE o.id = 1

Upvotes: 0

Views: 54

Answers (1)

moveson
moveson

Reputation: 5213

I think you have the following relationships:

--An order has many order_products,

--An order_product has one (belongs_to) product_provider,

--A product_provider has one (belongs_to) provider

If those relationships are correct, then you would want something like this:

Order.joins(order_products: {product_provider: :provider})
     .where(orders: {id: 1})
     .select('orders.id AS order_id, order_products.id AS order_products_id, order_products.name')

Though you are not selecting anything from product_providers or providers, so not sure why you want to join them.

Upvotes: 1

Related Questions