0

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.

Community
  • 1
  • 1
add
  • 111
  • 3
  • 16

2 Answers2

1

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")
lusketeer
  • 1,890
  • 1
  • 12
  • 29
  • Thanks, but this has some minor errors in it. The 'ON' clause does not work in MySQL. But, I did not mention that I'm using MySQL, so my bad. Also, there is a comma missing between the 2 columns in the select. With the errors out of the way, the output is not grouped or 'distinct' as we hoped, which is important. I'm still getting all the rows with repeated product_location_id's. Thanks again, I'm open to any other suggestions. – add May 23 '16 at 14:11
  • aw, okay, in that case, we can fall back on group by, does your product_sales table has an id field? – lusketeer May 23 '16 at 14:17
  • ah yes it does. The standard primary key that's added by rails. – add May 23 '16 at 14:20
  • Thanks, but I'm still getting the repeated rows. See below for the rails generated queries of the above command. – add May 23 '16 at 14:29
  • SELECT `product_sales`.`id` FROM `product_sales` GROUP BY id, product_location_id; SELECT `product_sales`.* FROM `product_sales` WHERE `product_sales`.`id` IN (1, 2, ... 10) – add May 23 '16 at 14:29
  • updated, fell back on your original query, doing a self join on the subquery – lusketeer May 23 '16 at 14:43
  • Tested it, but MySQL doesn't like the alias. See error below: `Mysql2::Error: Table 'sales_db_development.t' doesn't exist: SELECT `product_sales`.* FROM (SELECT product_location_id, MAX(net_sale) as max_net_sale FROM `product_sales` WHERE (sale_date BETWEEN '2016-05-01' AND '2016-05-31') GROUP BY `product_sales`.`product_location_id`) t INNER JOIN t ON product_sales.product_location_id = t.product_location_id AND product_sales.net_sale = t.max_net_sale` – add May 23 '16 at 14:50
  • noted, once last try, i just extract the sub_query into a variable, this looks more and more like plain sql now ... – lusketeer May 23 '16 at 14:54
  • lol, okay. You finally got it man. Thanks a million. I guess Rails has no simple way of doing it through their query interface. – add May 23 '16 at 14:59
  • no worries man, good luck on everything, you have no idea how many times i have to write a view to accommodate massive SQL queries – lusketeer May 23 '16 at 15:06
0

Well, the first part for query is the following, just use simple one-to-many relation query, then joins and conditions for request:

ProductSale
   .where(net_sale: ProductSale.pluck("MAX(net_sale)"))
   .and("sale_date BETWEEN ? AND ?", date1, date2)
   .where.not(product_location_id: nil)
   .group(:product_location_id)
Малъ Скрылевъ
  • 16,187
  • 5
  • 56
  • 69
  • Thanks. However, I'm getting an error with rails using the code. Error: undefined method 'and'. Are you by chance using a particular gem? – add May 23 '16 at 14:43
  • @add line `where.not` isn't quite required, if all the belongs to field are filled – Малъ Скрылевъ May 23 '16 at 14:45
  • Okay, no problem. Just tested it. Same error, see below: `SELECT MAX(net_sale) FROM `product_sales` NoMethodError: ProductSale Load (0.4ms) SELECT `product_sales`.* FROM `product_sales` WHERE `product_sales`.`net_sale` = 41130.0 undefined method `and' for #` – add May 23 '16 at 14:55
  • Thanks for your help, I've accepted an answer from @lusketeer. However, it would of been really nice if your approach worked, as it would of been much cleaner. – add May 23 '16 at 15:03