自定义电商分类排序：新增与修改时同步更新sorting_order字段

I get that you want to handle custom sorting for your webshop_categories table (using the sorting_order INT field) consistently when creating or updating categories. Let's break down a reusable approach that works for both scenarios, including handling edge cases like inserting into a specific position or reordering existing categories.

Core Logic Overview #

The key idea is that both create and update operations need to:

• Ensure the desired sorting_order is valid (no duplicates, within reasonable range)
• Adjust existing records' sorting values if needed to make space (for inserts) or shift positions (for updates)
• Apply the new sorting value to the target category

Step 1: Reusable SQL Adjustment Logic #

First, let's create a reusable SQL snippet that handles shifting existing sorting_order values. This works for both create and update scenarios.

Scenario A: Inserting a New Category at Position N #

When adding a new category and setting its sorting_order to a specific position (e.g., 3), we need to increment all categories with sorting_order >= 3 by 1 to make space:

-- Before inserting the new category
UPDATE webshop_categories 
SET sorting_order = sorting_order + 1 
WHERE sorting_order >= :desired_sort_position;

-- Then insert the new category
INSERT INTO webshop_categories (name, sorting_order)
VALUES ('New Category', :desired_sort_position);

Scenario B: Updating an Existing Category's Sorting Position #

If you're changing a category's sorting_order from an old position to a new one, you need to shift other records accordingly:

• If new position < old position: Increment all categories where sorting_order >= new_pos AND sorting_order < old_pos by 1
• If new position > old position: Decrement all categories where sorting_order > old_pos AND sorting_order <= new_pos by 1

Here's the combined parameterized SQL logic you can reuse safely:

-- First, adjust existing records based on position change
IF :new_sort_pos < :old_sort_pos THEN
    UPDATE webshop_categories 
    SET sorting_order = sorting_order + 1 
    WHERE sorting_order >= :new_sort_pos AND sorting_order < :old_sort_pos;
ELSEIF :new_sort_pos > :old_sort_pos THEN
    UPDATE webshop_categories 
    SET sorting_order = sorting_order - 1 
    WHERE sorting_order > :old_sort_pos AND sorting_order <= :new_sort_pos;
END IF;

-- Then update the target category's sorting position
UPDATE webshop_categories 
SET sorting_order = :new_sort_pos 
WHERE id = :category_id;

Step 2: Reusable Code Function (Example in PHP) #

To keep the logic consistent across create and update, wrap it in a helper function. Here's an example using PHP and PDO:

function adjustCategorySorting($pdo, $targetId = null, $desiredSortPos) {
    // Handle existing category update (target ID provided)
    if ($targetId !== null) {
        // Get current sorting position of the category
        $stmt = $pdo->prepare("SELECT sorting_order FROM webshop_categories WHERE id = ?");
        $stmt->execute([$targetId]);
        $oldSortPos = $stmt->fetchColumn();
        
        if ($oldSortPos == $desiredSortPos) {
            return; // No change needed—exit early
        }
        
        // Adjust other categories based on position shift
        if ($desiredSortPos < $oldSortPos) {
            $stmt = $pdo->prepare("UPDATE webshop_categories SET sorting_order = sorting_order + 1 WHERE sorting_order >= ? AND sorting_order < ?");
            $stmt->execute([$desiredSortPos, $oldSortPos]);
        } else {
            $stmt = $pdo->prepare("UPDATE webshop_categories SET sorting_order = sorting_order - 1 WHERE sorting_order > ? AND sorting_order <= ?");
            $stmt->execute([$oldSortPos, $desiredSortPos]);
        }
        
        // Update the target category's sorting position
        $stmt = $pdo->prepare("UPDATE webshop_categories SET sorting_order = ? WHERE id = ?");
        $stmt->execute([$desiredSortPos, $targetId]);
    } 
    // Handle new category creation (no target ID)
    else {
        // Make space for the new category
        $stmt = $pdo->prepare("UPDATE webshop_categories SET sorting_order = sorting_order + 1 WHERE sorting_order >= ?");
        $stmt->execute([$desiredSortPos]);
        
        // Insert the new category with the desired sort position
        $stmt = $pdo->prepare("INSERT INTO webshop_categories (name, sorting_order) VALUES (?, ?)");
        $stmt->execute(['New Category Name', $desiredSortPos]);
    }
}

Step 3: Edge Cases to Consider #

• Position higher than current max: If you set desired_sort_pos to 10 when the current max is 5, no adjustments are needed—just insert/update the category. The logic handles this automatically since the WHERE clause won't match any records.
• Avoiding duplicates: By adjusting existing records before setting the new value, you eliminate duplicate sorting_order values entirely.
• Deleting categories: If you delete a category, you may want to decrement all categories with sorting_order > deleted_category_sort_pos to fill the gap—add this logic to your delete function if needed.

Example Workflow #

Using your sample table:

id | name   | so
--------------------
1 | Shoes  | 1
2 | Hats   | 2
3 | Bags   | 3
4 | Coats  | 4
5 | Rings  | 5

If we add a new category Gloves at position 3:

1. Run the UPDATE to increment so >=3: Bags (3→4), Coats(4→5), Rings(5→6)
2. Insert Gloves with so=3. Resulting table:

id | name   | so
--------------------
1 | Shoes  | 1
2 | Hats   | 2
6 | Gloves | 3
3 | Bags   | 4
4 | Coats  | 5
5 | Rings  | 6

If we update Rings (id=5) from so=6 to so=2:

1. Increment so >=2 and <6: Hats(2→3), Gloves(3→4), Bags(4→5), Coats(5→6)
2. Set Rings' so to 2. Resulting table:

id | name   | so
--------------------
1 | Shoes  | 1
5 | Rings  | 2
2 | Hats   | 3
6 | Gloves | 4
3 | Bags   | 5
4 | Coats  | 6

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