在ActiveRecord查询中合并自定义SELECT子句 [英] Incorporating Custom SELECT clause in ActiveRecord query

问题描述

我有一个很大的评估表，该表中有几个带有数字分数的整数列。我正在尝试删除一些N + 1迭代来加快应用程序的速度。

I have a large table of assessments, in that table there are several integer columns with numerical scores. I am trying to remove some N+1 iteration to speed up my application.

我可以在原始SQL中生成此查询以收集所有分数

I can generate this query in raw SQL to gather all of the scores

query = 
  'SELECT ' + 
  Assessment.rating_attributes.collect{|attribute|
    1.upto(5).collect do |i|
      %Q{SUM(CASE WHEN #{attribute.to_s} = #{i} then 1 else 0 end) as #{attribute}_score_#{i}}
    end
    }.join(', ') + ' FROM assessments'

当我执行以下查询时：

  $> ActiveRecord::Base.connection.execute(query)
 => #<PG::Result:0x007fc9e7e541e8 status=PGRES_TUPLES_OK ntuples=1 nfields=55 cmd_tuples=1>

我得到正确的结果：

 ActiveRecord::Base.connection.execute(query).first.values
 => ["2400", "458", "3343", "1021", "93", "2352", "455", "3119", "1174", "150", "2378", "357", "2976", "1357", "181", "2042", "258", "3024", "1646", "280", "1274", "193", "3907", "1704", "172", "799", "93", "4593", "1670", "95", "1759", "361", "2542", "947", "69", "21", "100", "81", "42", "38", "8", "32", "51", "78", "112", "19", "119", "72", "43", "29", "0", "0", "0", "0", "0"]

我想执行相同的查询，但不对整个表进行查询。我希望能够将其链接到一个ActiveRecord查询中，例如：

I would like to perform this same query, but not across the entire table. I'd like to be able to chain it into an activerecord query, e.g.:

Assessment.where(selection_attribute: 1038).select(query_without_select_keyword_or_from_clause)

ActiveRecord::StatementInvalid: PG::GroupingError: ERROR:  column "assessments.id" must appear in the GROUP BY clause or be used in an aggregate function

并给我同样的结果，只是子集上。我的幼稚尝试（例如，示例中的.select（query）调用）失败了，我什至不知道该如何处理。

and have it give me the same kind of result, but on the subset. My naive efforts (e.g. the .select(query) call in my example) have failed, and I'm not even sure how to approach this.

一堵墙，我将不胜感激，即使在如何寻找解决方案方面也能提供任何指导。谢谢。

I've hit a wall, and I'd appreciate any guidance, even on how to craft a search for a solution. Thank you.

推荐答案

这很简单。问题是使用 .first 。我想这是因为查询没有返回任何模型记录。如果我直接解决结果的0索引，我将得到所有分数。

Well, this was simple. Using .first was the issue. I suppose this is because there are no model records returned by the query. If I address the 0 index of the result directly, I get all of my scores.

因此此代码有效：

 query = 
      Assessment.rating_attributes.collect{|attribute|
        1.upto(5).collect do |i|
        %Q{sum(CASE WHEN #{attribute.to_s} = #{i} then 1 else 0 end) as #{attribute}_score_#{i}}
      end
    }.join(', ')

Assessment.where(selection_attribute: 1038).select(query)[0]
=> #<Assessment id: nil>

Assessment.where(selection_attribute: 1038).select(query)[0].first_attribute_score_1
=> 3

这篇关于在ActiveRecord查询中合并自定义SELECT子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！