按两个关联列的总和排序

Question

class Question < ApplicationRecord
has_many :question_articles
has_many :articles, through: :question_articles
end

class Article < ApplicationRecord
has_many :question_articles
has_many :questions, through: :question_articles
end

class QuestionArticle < ApplicationRecord
belongs_to :article
belongs_to :question
end

question_articles表包含2列:user_usages_count和admin_usages_count。

我需要检索question_articles.user_usages_count+question_articles.admin_usages_count排序的questions

我试过:

Question  
.joins(:articles)
.includes(:question_articles)
.order('question_articles.admin_usages_count + question_articles.user_usages_count DESC')

但是我得到了'disallow_raw_sql!': Query method called with non-attribute argument

Answer 1

要解决'disallow_raw_sql!': Query method called with non-attribute argument的问题，我们需要使用内置Arel.sql方法

所以有效解是

Question  
.joins(:articles)
.includes(:question_articles)
.order(Arel.sql('question_articles.admin_usages_count + question_articles.user_usages_count DESC'))

Answer 2

您应该能够完全避免引用SQL，并通过在Question模型中创建一个方法来保持您的ruby逻辑，该方法看起来像这样(假设您需要来自所有相关QuestionArticle记录的这两个字段的总和)。

def self.by_usage
all.sort_by do |_question|
_question.question_articles.reduce(0) { |_sum, _qa| _sum += (_qa.user_usages_count + _qa.admin_usages_count) }
end
end

然后您应该能够使用@questions.by_usage并根据每个问题的相关QuestionArticle记录中所有使用数字的总和排序得到问题记录。

假设这满足了您的需要，您可以通过向QuestionArticles模型添加一个方法来进一步整理它，该方法返回这两个字段的总和:

def total_usage
user_usages_count + admin_usages_count
end

reduce行将简化为:

_question.question_articles.reduce(0) { |_sum, _qa| _sum += _qa.total_usage }

其本身简化为:

_question.question_articles.reduce(&:total_usage)