ruby-on-rails-3 – 帮助查询Rails中的数据

我需要从这些表中获取10个项目的结果集.我正在寻找以下字段 – company_name,平均数量,最高价格,最低价格
平均价格,销售数量.我设法像这样查询它们：

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;

我还需要按公司分组的平均每单位价格和周数. (我需要这个以逗号分隔的方式,以便我可以将它传递给Google Chart API.由于一个人不能在SQLite中的GROUP_CONCAT中使用SUM,我不得不使用这个非常简单的内联视图.)

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;

来自SQL背景,我发现有点难以使用ORM模型来获取数据.有人能告诉我如何使用Rails ORM方式获取这些数据吗？

我试图尽可能地冗长.对于遗漏,我是道歉,如果有的话.

谢谢

找到了加入这两个查询的方法.

SELECT lots.company_id,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,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;