add add - 6 months ago 10
SQL Question

Rails 4 Query Interface WHERE IN

I can't seem to figure out how to write the following simple SQL using Rails Active Record Query Interface.

SELECT *
FROM product_sales
WHERE (product_location_id, net_sale)
IN
(
SELECT product_location_id, MAX(net_sale)
FROM product_sales
GROUP BY product_location_id
)
AND (sale_date BETWEEN '2016-05-01' AND '2016-05-31');


Note: I've looked at the following link. However, it only specifies a single column in the outer WHERE clause, whereas I need two.

Link: subqueries in activerecord

Thanks for any assistance.

UPDATE

Models

[ProductSale]

references :product_location, index: true, foreign_key: true
decimal :net_sale, precision: 16, scale: 6
date :sale_date


[ProductLocation]

references :product, index: true, foreign_key: true
etc...


Relations

ProductSale -> belongs_to :product_location
ProductLocation -> has_many: product_sales


Please note my DB is in MySQL.

Answer

I have an alternative suggestion, try

For Postgresql

ProductSale
        .select("DISTINCT ON (product_sales.product_location_id), product_sales.*")
        .where("product_sales.sale_date BETWEEN '2016-05-01' AND '2016-05-31'")
        .order("product_sales.product_location_id, product_sales.net_sale DESC")

Use DISTINCT ON to select uniq row on product_location_id sorted net_sale from high to low

For MySQL

sub_query = ProductSale.select("product_location_id, MAX(net_sale) as max_net_sale").where("sale_date BETWEEN '2016-05-01' AND '2016-05-31'").group("product_location_id").to_sql
ProductSale
       .joins("INNER JOIN (#{sub_query}) t ON product_sales.product_location_id = t.product_location_id AND product_sales.net_sale = t.max_net_sale")
Comments