Rails order by sum of two association's columns-CodePudding

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 table contains 2 columns: user_usages_count and admin_usages_count.

I need to retrieve questions ordered by question_articles.user_usages_count question_articles.admin_usages_count

I've tried:

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

but I am getting 'disallow_raw_sql!': Query method called with non-attribute argument

CodePudding user response：

to fix the issue with 'disallow_raw_sql!': Query method called with non-attribute argument we need to use build in Arel.sql method

So the working solution would be

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

CodePudding user response：

You should be able to avoid referring to SQL at all and keep your logic in ruby by creating a method in your Question model that looks something like this (assuming that you need the total of those two fields from all related QuestionArticle records).

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

You should then be able to use @questions.by_usage and get back the Question records sorted by the total of all usage numbers in the related QuestionArticle records for each Question.

Assuming this does what you need, you could neaten it up further by adding a method to the QuestionArticles model that returns the total of these two fields:

def total_usage
  user_usages_count   admin_usages_count
end

The reduce line would then simplify to:

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

Which itself simplifies to:

_question.question_articles.reduce(&:total_usage)