CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
spirito_libero
spirito_libero

Reputation: 1332

Rails order by sum of two association's columns

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

Upvotes: 0

Views: 853

Answers (2)

JohnP
JohnP

Reputation: 1309

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)

Upvotes: 0

spirito_libero
spirito_libero

Reputation: 1332

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'))

Upvotes: 2

Related Questions