Help with querying data from Rails

Question

I have three tables Lot, Sale and Company. I've attached a digram below.

I need to fetch a result set of 10 items from these tables. I'm looking for the the following fields — company_name, the average quantity, the maximum price, the minimum price the average price, the number of sales. I've managed to query 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 the average price-per-unit grouped by the company and the week number. (I need this as a comma-separated way so that i can pass it to the Google Chart API. Since one cant use SUM inside a GROUP_CONCAT in SQLite, I had to use this fugly 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;

Coming from an SQL background, I find a little hard to use the ORM model to fetch the data. Could someone show me how I can fetch this data using the Rails ORM way?

I've tried to be as verbose as possible. My apologies for the omissions, if any.

Thanks

Found a way to join the 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 by using the find_by_sql method. It seemed more manageable than anything else.

Here's 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 to follow this path:

1. Create a model for any existing table
2. Define the associations between the models
3. Query as you like