Help requesting data from Rails

Question

I have three tables Lot , Sale and Company . I added the digram below.

I need to get a result set of 10 elements from these tables. I am looking for the following fields - company_name, average quantity, maximum price, minimum price average price, number of sales. I managed to request them like this:

 SELECT company_id , AVG(quantity) , MAX(price) , MIN(price) , AVG(price) , COUNT(sale_id) FROM lots GROUP BY company_id ORDER BY AVG(quantity) ASC LIMIT 10; 

I also needed an average unit price, grouped by company and week number. (I need this as a comma-separated way so I can pass it to the Google chart API. Since one of the possible ones did not use SUM inside GROUP_CONCAT in SQLite, I had to use this awkward inline view.)

 SELECT company_id , GROUP_CONCAT(price_per_unit) FROM ( SELECT company_id , sales.week , SUM(price * quantity) / SUM(quantity) AS price_per_unit FROM lots JOIN sales ON lots.sale_id = sales.id GROUP BY company_id , sales.week ORDER BY company_id ASC , sales.week ASC ) GROUP BY company_id; 

Based on the SQL background, I find it a little difficult to use the ORM model to retrieve data. Can someone show me how I can get this data using the Rails ORM method?

I tried to be as detailed as possible. We apologize for the omissions, if any.

thanks

Found a way to combine two queries.

 SELECT lots.company_id , AVG(quantity) , MAX(price) , MIN(price) , AVG(price) , COUNT(sale_id) , x.price_per_unit FROM lots JOIN ( SELECT company_id , GROUP_CONCAT(price_per_unit) AS price_per_unit FROM ( SELECT company_id , sales.week , SUM(price * quantity) / SUM(quantity) AS price_per_unit FROM lots JOIN sales ON lots.sale_id = sales.id GROUP BY company_id , sales.week ORDER BY sales.week ASC ) GROUP BY company_id ) x ON lots.company_id = x.company_id GROUP BY lots.company_id ORDER BY AVG(quantity) ASC LIMIT 10; 

Answer 1

I accomplished this with the find_by_sql method. It seemed more manageable than anything else.

Here is a snippet:

  sql = <<EOS SELECT lots.company_id , AVG(quantity) AS avg_quantity , MAX(price) AS max_price , MIN(price) AS min_prices , AVG(price) AS avg_price , COUNT(sale_id) AS cnt_sales , x.price_per_unit FROM lots JOIN ( SELECT company_id , GROUP_CONCAT(price_per_unit) AS price_per_unit FROM ( SELECT company_id , sales.week , SUM(price * quantity) / SUM(quantity) AS price_per_unit FROM lots JOIN sales ON lots.sale_id = sales.id GROUP BY company_id , sales.week ORDER BY sales.week ASC ) GROUP BY company_id ) x ON lots.company_id = x.company_id GROUP BY lots.company_id ORDER BY AVG(quantity) ASC LIMIT 10 EOS @items = Lot.find_by_sql(sql) 

Answer 2

I suggest following this path:

• Create a model for any existing table
• Identify associations between models
• Request as you like