How to join in rails active record using alias

Question

I am trying to convert following SQL into rails active record query

"SELECT  * FROM    stocks
        INNER JOIN
        (
            SELECT  product_id, MIN(ask_price) min_price
            FROM    stocks
            GROUP   BY product_id,size
        ) sub ON  stocks.product_id = sub.product_id AND
                stocks.ask_price = sub.min_price
                where stocks.product_id = 1"

This query fetches the lowest price of the stock group by product and size of product.

So far I have tried to translate it like this it not right.

sub_query = Stock.select("product_id, MIN(ask_price) min_price").group(:product_id,:size)
stocks = Stock.joins("#{sub_query} stocks.product_id = sub.product_id AND
                stocks.ask_price = sub.min_price")

Answer 1

You should pass into joins method all your join clause if you want to do some custom joining, like this:

stocks = Stock.joins(<<-SQL
  INNER JOIN
  (
    SELECT product_id, MIN(ask_price) min_price
    FROM stocks
    GROUP BY product_id, size
  ) sub ON stocks.product_id = sub.product_id AND stocks.ask_price = sub.min_price
SQL).where(product_id: 1)