Laravel项目排序优化咨询：批量更新性能与重复请求问题

Great question—scaling this sort logic is critical as your project count grows. Let’s break down frontend and backend optimizations to fix performance bottlenecks and eliminate unnecessary requests, while keeping the onDrop trigger requirement.

Frontend: Only Send Changed Items to the Backend #

Right now, you’re sending the entire array of project IDs on every drop—even when a user drags an item back to its original position. Instead, compare the pre-drag and post-drag order to only send updates for items whose position actually changed.

Here’s how to implement this with Sortable.js:

// Store the initial order when your component loads
let currentOrderIds = [...yourProjectsArray.map(project => project.id)];

// In your Sortable onDrop callback
onDrop() {
  // Get the new sorted ID array using Sortable's toArray() method
  const newOrderIds = sortableInstance.toArray();
  
  // Calculate which items need their order updated
  const updates = [];
  newOrderIds.forEach((id, newIndex) => {
    const oldIndex = currentOrderIds.indexOf(id);
    // Only include items that moved
    if (oldIndex !== newIndex) {
      updates.push({
        id: parseInt(id),
        order: newIndex + 1 // Match your 1-based order numbering
      });
    }
  });

  // Skip the request if no changes were made (e.g., drag-and-drop back to original spot)
  if (updates.length === 0) return;

  // Send only the changed items to your backend
  fetch('/api/projects/update-order', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({ updates })
  })
  .then(response => response.json())
  .then(() => {
    // Update the stored order to match the new state
    currentOrderIds = [...newOrderIds];
  });
}

This cuts down on payload size drastically—for a single item drag, you’ll only send updates for that item plus any items that shifted positions to make space, instead of 600+ IDs.

Backend: Batch Update Instead of Individual Queries #

Your current loop runs a separate find() and update() for every ID, which would result in 600+ database queries for a full reorder. Switch to a single batch update using a SQL CASE statement to reduce this to one query, regardless of how many items need updating.

For Laravel, here’s the optimized code:

public function updateOrder(Request $request)
{
    $updates = $request->input('updates');
    if (empty($updates)) return response()->json(['success' => true]);

    // Build a CASE statement for batch updating
    $caseClause = '';
    $bindings = [];
    $affectedIds = [];

    foreach ($updates as $update) {
        $caseClause .= "WHEN id = ? THEN ? ";
        $bindings[] = $update['id'];
        $bindings[] = $update['order'];
        $affectedIds[] = $update['id'];
    }

    $caseClause = rtrim($caseClause);
    $idList = implode(',', $affectedIds);

    // Execute the single batch update
    DB::update(
        "UPDATE projects SET `order` = CASE $caseClause ELSE `order` END WHERE id IN ($idList)",
        $bindings
    );

    return response()->json(['success' => true]);
}

Additional Performance Boosts #

• Add an index to the order field: Since you’re querying and sorting by this column frequently, a database index will speed up both reads and writes. Run this migration:

  Schema::table('projects', function (Blueprint $table) {
      $table->index('order');
  });
  

• Validate frontend input: Add checks on the backend to ensure the updates array contains valid IDs and order values to prevent invalid database operations.

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