技术问询：为商品详情API添加按规则排序的相似商品及SQL实现

Alright, let's walk through how to build this related products feature for your product detail API, including the exact SQL logic to handle the sorting rules you laid out.

核心规则拆解 #

First, let's clarify the two-tiered sorting/filtering logic you need:

• Top priority: Show products in the same category as the target item, and whose price falls within ±$10 of the target's price.
• Secondary priority: For remaining same-category products (outside the ±$10 range), sort them by how close their price is to the target—smallest price difference first.

Using your example to make this concrete:

Target product ID #30, price $30, category "beer"

1. First display all beer products priced between $20 and $40
2. Then display the remaining beer products sorted by absolute price difference from $30: $42 (diff $12), $17 (diff $13), $45 (diff $15), $10 (diff $20), etc.

SQL Implementation #

Assume your product table is named products, with columns id, category, price. We'll exclude the target product itself (you don't want to recommend the item the user is already viewing) and use parameterized values for the target's price, category, and ID.

Full Query #

SELECT
    id,
    category,
    price,
    ABS(price - :target_price) AS price_difference
FROM products
WHERE
    category = :target_category
    AND id != :target_product_id -- Exclude the target product itself
ORDER BY
    -- Group in-range products first
    CASE WHEN price BETWEEN :target_price - 10 AND :target_price + 10 THEN 0 ELSE 1 END,
    -- Sort by closest price (smallest difference first)
    price_difference ASC;

How the Sorting Works #

• The CASE statement tags in-range products with a 0 and out-of-range ones with a 1. Since we sort ascending, all in-range items appear first.
• The price_difference (absolute value of the price gap) ensures both in-range and out-of-range products are ordered by how close they are to the target price—exactly what you need.

API Layer Tips #

• If you need to limit the number of related products (e.g., show only 8), add LIMIT 8 to the end of the SQL query.
• For pagination (if you ever need it), you can add OFFSET alongside LIMIT, but keep in mind offset can be slow for large datasets—keyset pagination is better for scalability.

Optimization Notes #

• If your product table is large, add a composite index to speed up the query:

  CREATE INDEX idx_category_price ON products(category, price);
  

• To make the price range flexible (e.g., adjust to ±$15 later), store the range value in a config file instead of hardcoding it in SQL.

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