How to build inner join in Rails with conditions?

Question

I've a model StockUpdate which keeps track of stocks for every product for a store. Table attributes are: :product_id, :stock, :store_id. I was trying to find out last entry for every product for a given store. According to that I build my query in PGAdmin which is given below and it's working fine. I'm new in Rails and I don't know how to represent it in Model. Please help.

SELECT  a.*
FROM    stock_updates a
    INNER JOIN
    (
        SELECT product_id, MAX(id) max_id
        FROM    stock_updates where store_id = 9 and stock > 0
        GROUP BY product_id
    ) b ON a.product_id = b.product_id AND
            a.id = b.max_id

Answer 1

What you need is really simple and can be easily accomplished with 2 queries. Otherwise it becomes very complicated in a single query (it's still doable though):

store_ids = [0, 9]
latest_stock_update_ids = StockUpdate.
                          where(store_id: store_ids).
                          group(:product_id).
                          maximum(:id).
                          values
StockUpdate.where(id: latest_stock_update_ids)

Two queries, without any joins necessary. The same could be possible with a single query too. But like your original code, it would include subqueries.

Something like this should work:

StockUpdate.
where(store_id: store_ids).
where("stock_updates.id = (
    SELECT MAX(su.id) FROM stock_updates AS su WHERE (
      su.product_id = stock_updates.product_id
    )
  )
")

Or perhaps:

StockUpdate.where("id IN (
  SELECT MAX(su.id) FROM stock_updates AS su GROUP BY su.product_id
)")

And to answer your original question, you can manually specify a joins like so:

Model1.joins("INNER JOINS #{Model2.table_name} ON #{conditions}")
# That INNER JOINS can also be LEFT OUTER JOIN, etc.