Ruby ActiveRecord查询按自定义字段求和

Question

我有一个名为Answer的模型，它与has_one Question关联。Question有dimension_id, possible_answers，我有以下查询

Answer.joins(:question).select(:dimension_id,"(CAST(answers.answer AS FLOAT) / (length(possible_answers))) AS satisfaction").where(:created_at => date_from..date_to)

我希望通过dimension_id对结果进行分组，并根据满意度对结果求和，以便获得每个维度的满意度，但是如果我执行.group(:dimension_id).sum(:satisfaction)，则会得到错误PG::UndefinedColumn: ERROR: column "satisfaction" does not exist

我应该如何计算满意度才能得到想要的结果？谢谢

Answer 1

尝试这样做：

Answer.where(:created_at => date_from..date_to).joins(:question).select("answers.dimension_id, sum(CAST(answers.answer AS FLOAT) / (length(possible_answers))) AS satisfaction").group('answers.dimension_id')

我假设您的dimension_id位于answers中，如果不是，请将answers.dimension_id更改为questions.dimension_id