Rails3/ActiveRecord:将现有查询更改为按月分组

Question

model entry.rb

def self.calculate(year, month, id)
     where(':id = entries.user_id', {    
          :id => id
     }).
     where('entries.date <= :last_day', { 
          :last_day => Date.new(year, month, 1).at_end_of_month
     }).
     select('sum(case when joint = "f" then amount_calc else 0 end) as sum_single,' + 
            'sum(case when joint = "t" then amount_calc else 0 end) as sum_joint,' + 
            'sum(case when compensation = "t" then amount_calc else 0 end) as sum_compensation')
  end

查询为特定用户和截至给定月份的所有条目提供三个总和。到目前为止运行良好。

接下来我真正需要的是完全相同的，但每个月都有一个值(不同年份的月份必须在不同的组中)。该代码需要同时与SQLite和PostgreSQL工作

{其他问题:是否有可能在一个查询中像我的原始代码一样获得分组和总体总和?我认为这是不可能的……}

Answer 1

SQLite

def self.calculate(year, month, id)
 where(':id = entries.user_id', {    
      :id => id
 }).
 where('entries.date <= :last_day', { 
      :last_day => Date.new(year, month, 1).at_end_of_month
 }).
 select('sum(case when joint = "f" then amount_calc else 0 end) as sum_single,' + 
        'sum(case when joint = "t" then amount_calc else 0 end) as sum_joint,' + 
        'sum(case when compensation = "t" then amount_calc else 0 end) as sum_compensation').
 group("strftime('%Y-%m', created_at)")
end

Postgre之类的东西(我从来没有使用过这个db)

def self.calculate(year, month, id)
 where(':id = entries.user_id', {    
      :id => id
 }).
 where('entries.date <= :last_day', { 
      :last_day => Date.new(year, month, 1).at_end_of_month
 }).
 select('sum(case when joint = "f" then amount_calc else 0 end) as sum_single,' + 
        'sum(case when joint = "t" then amount_calc else 0 end) as sum_joint,' + 
        'sum(case when compensation = "t" then amount_calc else 0 end) as sum_compensation').
 group("EXTRACT(YEAR FROM TIMESTAMP created_at)||EXTRACT(MONTH FROM TIMESTAMP created_at)")
end

所以你应该首先检查使用的是什么适配器然后使用其中一个查询