我想要一个优化的AR(ActiveRecord(查询,因为我当前的AR查询多次查询数据库
假设模型巧克力具有列created_at。我的控制器中有以下 AR 查询:
for i in 1..4
@myHash[i] = Chocolate.where(created_at: (Time.now - i.hours)..(Time.now - (i-1).hours)).count
end
上述查询从 db 检索 4 次(假设当前时间为 12:00(
Select COUNT(*) from Chocolate where created_at BETWEEN 11:00 AND 12:00
Select COUNT(*) from Chocolate where created_at BETWEEN 10:00 AND 11:00
Select COUNT(*) from Chocolate where created_at BETWEEN 09:00 AND 10:00
Select COUNT(*) from Chocolate where created_at BETWEEN 08:00 AND 09:00
如何更改查询,使其仅从数据库检索一次?
您可以使用如下查询:
Chocolate.where(Chocolate.arel_table[:created_at].gteq(4.hours.ago))
.group("DATE_TRUNC('hour', created_at)")
.count
它将返回一个哈希值,其中包含按小时分组的记录计数。
{"2019-02-12 00:00:00"=>152, "2019-02-12 01:00:00"=>57, "2019-02-12 02:00:00"=>132, "2019-02-12 03:00:00"=>132}
希望这有帮助。
尝试纯字符串条件:
Chocolate.where('created_at BETWEEN ? AND ?', (Time.now - i.hours), (Time.now - (i-1).hours))