Solidus中基于最低变体价格排序产品的ActiveRecord查询求助

Hey there! Let's fix this Solidus sorting issue while keeping your cache intact—no more array conversions breaking things.

The Solution: Use an ActiveRecord Scope with Aggregation #

Since we need to keep the result as an ActiveRecord::Relation (to preserve the cache_key_for_products functionality), we'll leverage SQL aggregation to calculate each product's lowest variant price directly in the database.

Add this scope to your app/models/spree/product.rb file:

scope :sorted_by_lowest_variant_price, -> {
  joins(:variants)
    # Ensure we only consider variants with valid prices (optional but recommended)
    .where("spree_variants.price IS NOT NULL")
    # Select all product fields + compute the lowest variant price for each product
    .select("spree_products.*, MIN(spree_variants.price) AS lowest_price")
    # Group results by product to avoid duplicate product rows
    .group("spree_products.id")
    # Sort ascending by the computed lowest price
    .order("lowest_price ASC")
}

How This Works #

• joins(:variants) links each product to its associated variants (including the master variant, which is part of the variants collection in Solidus).
• MIN(spree_variants.price) calculates the lowest price across all variants for each product, aliased as lowest_price.
• group("spree_products.id") ensures we get one row per product, even if it has multiple variants.
• The result stays an ActiveRecord::Relation, so cache_key_for_products can still generate a valid cache key based on the query and product update timestamps.

Using the Scope in Your Controller #

Replace your array-based sorting in home#index with:

@products = Spree::Product.sorted_by_lowest_variant_price

For your example products:

• Product c (variants priced 4 and 1) will have lowest_price = 1
• Product a (master variant priced 2) will have lowest_price = 2
• Product b (master variant priced 3) will have lowest_price = 3

This gives you the desired sort order: c → a → b, without breaking your cache.

Optional: Handling Null Prices #

If you have variants without a price (unlikely in Solidus, but possible), the where("spree_variants.price IS NOT NULL") clause ensures those don't skew the min calculation. You can remove it if you're confident all variants have valid prices.

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