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_sums？或者我是否需要从地面实现 multiple_sums 而不使用现有的？

Answer 1

删除 order by 子句，据我所知，无论如何，这都是无用的，因为您将分组为单行。

请重新格式化您的查询，以便它们可见且可读。