请求协助：将多关联MySQL查询转换为Ruby on Rails Active Record语句

Hey there! Let's convert that MySQL query into clean, maintainable Rails Active Record code. I'll break it down so you can follow along easily.

First, let's handle the two subqueries from your original SQL—one for incoming stock from purchases, and one for outgoing stock from transaction details:

# Subquery to calculate total incoming quantity per product
product_in_subquery = Purchase.select('product_id, SUM(quantity) AS quantity')
                              .group(:product_id)

# Subquery to calculate total outgoing quantity per product
product_out_subquery = TransactionDetail.select('product_id, SUM(quantity) AS quantity')
                                        .group(:product_id)

Next, we'll build the main query using these subqueries with left joins, and calculate the final stock quantity using COALESCE (which is the ANSI SQL equivalent of MySQL's IFNULL, making it more portable across databases):

stock_query = Product.select(
  :id,
  :product_name,
  :price,
  'COALESCE(products.quantity, 0) + COALESCE(product_in.quantity, 0) - COALESCE(product_out.quantity, 0) AS quantity'
)
.left_joins("LEFT JOIN (#{product_in_subquery.to_sql}) product_in ON products.id = product_in.product_id")
.left_joins("LEFT JOIN (#{product_out_subquery.to_sql}) product_out ON products.id = product_out.product_id")

If you want to reuse this logic easily, you can wrap it in a scope directly in your Product model:

# app/models/product.rb
class Product < ApplicationRecord
  def self.with_current_stock
    product_in_subquery = Purchase.select('product_id, SUM(quantity) AS quantity')
                                  .group(:product_id)
    product_out_subquery = TransactionDetail.select('product_id, SUM(quantity) AS quantity')
                                            .group(:product_id)

    select(
      :id,
      :product_name,
      :price,
      'COALESCE(products.quantity, 0) + COALESCE(product_in.quantity, 0) - COALESCE(product_out.quantity, 0) AS current_stock'
    )
    .left_joins("LEFT JOIN (#{product_in_subquery.to_sql}) product_in ON products.id = product_in.product_id")
    .left_joins("LEFT JOIN (#{product_out_subquery.to_sql}) product_out ON products.id = product_out.product_id")
  end
end

Now you can just call Product.with_current_stock to get your products with their calculated current stock quantity—super clean!

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