ruby on rails - ActiveRecord/Postgres: PGError必须出现在GROUP BY子

Question

在我的模型中，我有几个查询可以一个接一个地使用(和重用)。其中一个应该是总金额。这在SQLite上运行良好，但在Postgres上抛出错误:

ActiveRecord::StatementInvalid (PGError: ERROR:  column "entries.date" must appear in the GROUP BY clause or be used in an aggregate function
: SELECT sum(case when joint = false then amount else amount / 2 end) as total, sum(case when joint = false then amount else 0 end) as sum_personal, sum(case when joint = true and user_id = 1 then amount / 2 else 0 end) as sum_user_joint, sum(case when joint = true and user_id = 2 then amount / 2 else 0 end) as sum_partner_joint  FROM "entries" WHERE (1 = entries.user_id OR (2 = entries.user_id AND entries.joint = 't')) AND ('2011-04-01' <= entries.date AND entries.date <= '2011-04-30') AND (amount_calc > 0 AND compensation = 'f') ORDER BY date asc)

Model.rb相关部分

  # all entries of one month
  def self.all_entries_month(year, month, user_id, partner_id)
    mydate = Date.new(year, month, 1)
    where(':user_id = entries.user_id OR (:partner_id = entries.user_id AND entries.joint = :true)', {
        :user_id => user_id,
        :partner_id => partner_id,
        :true => true
    }).
    where(':first_day <= entries.date AND entries.date <= :last_day', { 
        :first_day => mydate,
        :last_day => mydate.at_end_of_month
    })
  end
  def self.income
    where('amount_calc > 0 AND compensation = ?', false)
  end
  def self.cost
    where('amount_calc <= 0 AND compensation = ?', false)
  end
  def self.order_by_date
    order('date asc')
  end
  # group by tag and build sum of groups named group_sum
  def self.group_by_tag(order)
    group('tag').
    select('tag, ' +
           'sum(case when joint = "f" then amount else amount / 2 end) as tag_sum'
          ).
    order('tag_sum ' + order)
  end
  def self.multiple_sums(user_id, partner_id)
    case ActiveRecord::Base.connection.adapter_name  
      when 'SQLite'
        select('sum(case when joint = "f" then amount else amount / 2 end) as total, ' +
               'sum(case when joint = "f" then amount else 0 end) as sum_personal, ' + 
               'sum(case when joint = "t" and user_id = ' + user_id.to_s + ' then amount / 2 else 0 end) as sum_user_joint, ' + 
               'sum(case when joint = "t" and user_id = ' + partner_id.to_s + ' then amount / 2 else 0 end) as sum_partner_joint ' 
        )
      when 'PostgreSQL'
        select('sum(case when joint = false then amount else amount / 2 end) as total, ' +
               'sum(case when joint = false then amount else 0 end) as sum_personal, ' + 
               'sum(case when joint = true and user_id = ' + user_id.to_s + ' then amount / 2 else 0 end) as sum_user_joint, ' + 
               'sum(case when joint = true and user_id = ' + partner_id.to_s + ' then amount / 2 else 0 end) as sum_partner_joint ' 
        )
    else
      raise 'Query not implemented for this DB adapter'
    end  
  end

控制器

# get all entries of given month
@cost = Entry.all_entries_month(@year, @month, current_user.id, current_partner.id).cost
# group cost by categories
@group_cost = @cost.group_by_tag('asc')
# still need to sort by date
@cost = @cost.order_by_date
@calc_cost = @cost.multiple_sums(current_user.id, current_partner.id)[0]  

我如何改变我的查询multiple_sum而不破坏其他查询?或者我需要从地面实现multiple_sum而不使用现有的?

Answer 1

删除order by子句，据我所见，这是无用的，因为您分组为单行。

请重新格式化你的查询，使他们将可见和可读。