Rails finding from nested records

user_7688667 0
Viewed 58

product.rb

  class Product < ApplicationRecord
    has_many :product_items
  end

product_item.rb

 class ProductItem < ApplicationRecord
    belongs_to :product
    has_many :prices
  end

price.rb

class Price < ApplicationRecord
   belongs_to :product_item
end

Now price table has a column called otc, it stores one-time cost for the product item and product item can have many prices.

Objective: The product has many product items but I need to find one minimum cost product item(say default_product_item). Need to select product item(along with otc) for a product with minimum otc from price table.

I am using ruby 3.0.1, rails 6.1.3.2, database, Postgres.

postgresql ruby-on-rails
1 Answers
product_items = ProductItem.where(product: @product).joins(:prices).select("product_items.*", "MIN(prices.otc) as min_price").group('product_items.id').order('min_price').first

This should return SQL like

SELECT product_items.*, MIN(prices.otc) as min_price
FROM product_items
INNER JOIN 
prices
WHERE product_items.product_id = 6
ON product_items.id = prices.product_item_id
GROUP BY product_items.id
ORDER BY min_price 
LIMIT 1
user_10326630
Related