如何在Rails中使用Scope实现原生SQL查询的链式调用？

Hey there! Let's break down your Rails SQL questions and refactor that raw query into a chainable, Rails-friendly scope.

1. How to implement chainable raw SQL queries using Scopes in Rails? #

The key here is that Rails scopes (and most ActiveRecord query methods) return an ActiveRecord::Relation object—this is what lets you chain calls like .where(), .order(), or .limit() even when working with raw SQL snippets.

To make raw SQL play nice with chaining:

• Use ActiveRecord methods like select(), joins(), group(), or from() to wrap your raw SQL fragments instead of using ActiveRecord::Base.connection.execute() (which returns a static result set instead of a chainable Relation).
• Encapsulate reusable logic into scopes (or class methods, which are functionally equivalent) that return a Relation.

For example, a simple scope with raw SQL that stays chainable:

class User < ApplicationRecord
  scope :active_over_30_days, -> {
    where("created_at < ?", 30.days.ago)
      .select("id, email, DATE(created_at) as joined_date")
  }
end

# You can chain this with other methods:
User.active_over_30_days.order(joined_date: :desc).limit(10)

2. Refactoring your raw SQL into a chainable scope #

Your query calculates risk severity counts by aggregating user answers, then formats the result into a string. Let's convert this into a chainable, maintainable scope/class method in Rails.

First, let's break down your query into nested ActiveRecord subqueries (all returning Relations, so they stay chainable):

Step 1: Define the inner subquery (max weight per user)

We can wrap this into a reusable scope on FormsUserAnswer:

class FormsUserAnswer < ApplicationRecord
  belongs_to :forms_user
  belongs_to :form_answer

  scope :max_weight_per_user, ->(form_id) {
    joins(:forms_user)
      .left_joins(:form_answer)
      .where(forms_users: { form_id: form_id })
      .group(:forms_user_id)
      .select("forms_user_answers.forms_user_id, max(form_answers.weight) as max_weight")
  }
end

Step 2: Build the aggregation subquery

Next, we'll calculate the counts for each severity level using the subquery above:

def calculate_severity_counts(subquery)
  subquery.from(subquery, :subq)
          .select(
            "sum(case when max_weight = 1 THEN 1 else 0 end) as Clear",
            "sum(case when max_weight = 2 THEN 1 else 0 end) as Minor",
            "sum(case when max_weight = 3 THEN 1 else 0 end) as Moderate",
            "sum(case when max_weight = 22 THEN 1 else 0 end) as Major",
            "sum(case when max_weight = 160 THEN 1 else 0 end) as Critical",
            "count(*) as number_of_answers"
          )
end

Step 3: Format the final result

Finally, we'll format the counts into a structured result. I'll use PostgreSQL's json_build_object instead of string concatenation for a cleaner, parsable JSON output:

class Form < ApplicationRecord
  def risk_summary
    # Get the inner subquery (max weight per user)
    user_max_weights = FormsUserAnswer.max_weight_per_user(self.id)
    
    # Aggregate severity counts
    severity_counts = calculate_severity_counts(user_max_weights)
    
    # Format into a JSON object (chainable!)
    severity_counts.from(severity_counts, :subq1)
                   .select("json_build_object(
                     'Critical', Critical,
                     'Major', Major,
                     'Moderate', Moderate,
                     'Minor', Minor,
                     'Clear', Clear
                   ) as result")
  end

  private

  def calculate_severity_counts(subquery)
    subquery.from(subquery, :subq)
            .select(
              "sum(case when max_weight = 1 THEN 1 else 0 end) as Clear",
              "sum(case when max_weight = 2 THEN 1 else 0 end) as Minor",
              "sum(case when max_weight = 3 THEN 1 else 0 end) as Moderate",
              "sum(case when max_weight = 22 THEN 1 else 0 end) as Major",
              "sum(case when max_weight = 160 THEN 1 else 0 end) as Critical",
              "count(*) as number_of_answers"
            )
  end
end

Using the chainable scope

Now you can use this method and chain additional query methods if needed:

# Get the risk summary as a JSON string
Form.find(123).risk_summary.pluck(:result).first

# Chain with other methods (e.g., add a filter to the inner query)
user_max_weights = FormsUserAnswer.max_weight_per_user(123)
                                   .where("forms_users.created_at > ?", 1.month.ago)
severity_counts = calculate_severity_counts(user_max_weights)

This approach keeps all your queries as ActiveRecord::Relation objects, so you can chain any additional ActiveRecord methods (like .where(), .order()) at any step.

内容的提问来源于stack exchange，提问作者Muhammad Ateq Ejaz