如何优化基于嵌套关联的STI模型查询？获取指定城市贷款

Great question! Your current approach works, but we can refactor it to return an ActiveRecord Relation directly (no need for find_by_sql or manual UNIONs) while keeping the query efficient and idiomatic to Rails.

The Core Problem with Your Current Approach #

Your code uses a UNION to fetch IDs first, then queries Loans by those IDs. This works, but it:

• Doesn't return a pure Relation until the final where(id: ...) call (which still runs a subquery)
• Feels clunky compared to leveraging ActiveRecord's query methods directly
• Can be simplified into a single, readable SQL query

Simple & Direct Approach: Left Joins + OR Condition #

Since BusinessLoan uses STI (shares the loans table with Loan/HousingLoan), we can directly join the businesses table using the business_id column on loans, then join the associated business address with a table alias to avoid conflicts with the borrower's address.

cities = ["New York", "Washington"]

filtered_loans = Loan.left_joins(borrower: :address)
                     # Join businesses only for BusinessLoan records (others will have NULL business_id)
                     .left_joins("LEFT JOIN businesses ON businesses.id = loans.business_id")
                     # Alias the address table for businesses to avoid name collision with borrower addresses
                     .left_joins("LEFT JOIN addresses business_addresses ON business_addresses.addressable_id = businesses.id AND business_addresses.addressable_type = 'Business'")
                     # Match either borrower's city OR business's city
                     .where("addresses.city IN (?) OR business_addresses.city IN (?)", cities, cities)
                     # Avoid duplicate records if a loan somehow matches both conditions
                     .distinct

Key Notes on This Approach #

• left_joins ensures we don't filter out loans that don't have a business (like HousingLoan records)
• The table alias business_addresses prevents ambiguity between the borrower's address and the business's address (both use the addresses table)
• distinct is a safety net to avoid duplicate rows (unlikely in most cases, but good practice)

Even More Robust: Use Arel for Type-Safe Queries #

If you want to avoid raw SQL strings entirely (for better maintainability and type safety), use Arel (Rails' underlying query builder):

cities = ["New York", "Washington"]

# Define table references and aliases
addresses = Address.arel_table
business_addresses = Address.arel_table.alias('business_addresses')
businesses = Business.arel_table
loans = Loan.arel_table

# Build the borrower address condition
borrower_condition = Loan.joins(borrower: :address)
                         .where(addresses[:city].in(cities))

# Build the business address condition (only applies to BusinessLoan records)
business_condition = Loan.joins(
                         loans.join(businesses)
                           .on(loans[:business_id].eq(businesses[:id]))
                           .join_sources
                       )
                       .joins(
                         businesses.join(business_addresses)
                           .on(
                             business_addresses[:addressable_id].eq(businesses[:id])
                             .and(business_addresses[:addressable_type].eq('Business'))
                           )
                           .join_sources
                       )
                       .where(business_addresses[:city].in(cities))

# Combine conditions and deduplicate
filtered_loans = borrower_condition.or(business_condition).distinct

Reusable Scope for Clean Code #

To make this query reusable across your app, wrap it in a scope in the Loan model:

class Loan < ApplicationRecord
  belongs_to :borrower

  scope :with_borrower_or_business_in_cities, ->(cities) {
    left_joins(borrower: :address)
      .left_joins("LEFT JOIN businesses ON businesses.id = loans.business_id")
      .left_joins("LEFT JOIN addresses business_addresses ON business_addresses.addressable_id = businesses.id AND business_addresses.addressable_type = 'Business'")
      .where("addresses.city IN (?) OR business_addresses.city IN (?)", cities, cities)
      .distinct
  }
end

Now you can call it with a single line:

Loan.with_borrower_or_business_in_cities(cities)

Why This Is Better Than Your Original Code #

1. Pure ActiveRecord Relation: You get a fully functional Relation that supports chaining methods like order, limit, includes, or preload.
2. Single Optimized Query: Runs one SQL query instead of two (the UNION + ID lookup), which is more efficient.
3. Idiomatic Rails: Uses Rails' query methods instead of manual SQL strings (or minimizes them), making the code easier to read and maintain.
4. Flexible: Works with all loan types (HousingLoan, BusinessLoan) without additional conditionals.

内容的提问来源于stack exchange，提问作者aravind