Rails：可以重写此SQL查询以使用ActiveRecord吗？

The query works fine but as an exercise how would I convert it utilize ActiveRecord methods?

查询工作正常但作为练习我如何使用ActiveRecord方法转换它?

Model.find_by_sql("SELECT *, SUM(impressions) AS impressions, SUM(cost) AS cost
                   FROM display_stats
                   WHERE (created_at >= '#{(Date.today-2).to_time.strftime('%Y-%m-%d')}'
                          AND created_at <= '#{(Date.today-1).to_time.strftime('%Y-%m-%d')}')
                   GROUP BY banner_id
                   ORDER BY cost DESC")

Here is my model structure:

这是我的模型结构:

DisplayStat (id: integer,
             banner_id: integer,
             comments: string,
             impressions: integer,
             cost: integer,
             created_at: datetime,
             updated_at: datetime)

Is it possible to write the above query without using raw SQL?

是否可以在不使用原始SQL的情况下编写上述查询?

---

Running Rails 3.1 and MySQL 5.5

运行Rails 3.1和MySQL 5.5

1 个解决方案

#1

Try this:

DisplayStat.
  select("banner_id, SUM(impressions) AS impressions, SUM(cost) AS cost").
  where(:created_at => (2.days.ago.beginning_of_day..2.days.ago.end_of_day)).
  group(:banner_id).
  order("cost DESC")

The date range is from the start to the end of the day; 2 days ago. When you pass a date range to the conditions hash, rails generates a BETWEEN operator.

日期范围从一天的开始到结束; 2天前。将日期范围传递给条件哈希时,rails会生成BETWEEN运算符。