Laravel Eloquent实现商品表parent_link相邻不重复交替排序

Got it, let's figure out how to rearrange your goods table records so that consecutive rows never have the same parent_link, cycling through each parent group like you described (e.g., ffff→dddd→eeee→zzzz→ffff→dddd→zzzz...).

Core Idea #

The key trick here is to assign a sequential row number to each record within its parent_link group, then sort first by this row number, then by parent_link. This way, we'll pull one record from each parent group in the first "cycle", then one from each group that has remaining records in the second cycle, and so on.

Implementation with Laravel Eloquent #

We'll use SQL window functions (supported in MySQL 8.0+, PostgreSQL, SQL Server, etc.) to generate the row numbers directly in the query—this is the cleanest and most efficient approach:

use App\Models\Good;
use Illuminate\Support\Facades\DB;

$orderedGoods = Good::select([
    'good_link',
    'parent_link',
    'name',
    // Assign a unique row number to each record in its parent group
    DB::raw('ROW_NUMBER() OVER (PARTITION BY parent_link ORDER BY name) AS row_num')
])
// First sort by row number to cycle through parent groups
->orderBy('row_num')
// Then sort by parent_link to keep consistent group order within each cycle
->orderBy('parent_link')
->get();

Breakdown of the Query #

• PARTITION BY parent_link: Groups all records by their parent_link value.
• ROW_NUMBER() OVER (...): Assigns a unique number (starting at 1) to each record in its group. We used ORDER BY name here to ensure a stable sort within each group, but you can replace this with good_link or any other field since you mentioned A-Z order doesn't matter.
• orderBy('row_num'): Ensures we take the first record from each group first, then the second from each group, etc.
• orderBy('parent_link'): Keeps the parent groups in a predictable order during each cycle (adjust this if you want a different cycle sequence).

For Older Databases (No Window Functions) #

If you're working with a database that doesn't support window functions (e.g., MySQL < 8.0), you'll need to handle grouping and ordering manually. Here's a practical approach:

// Fetch all unique parent_link values
$parentGroups = Good::select('parent_link')->distinct()->pluck('parent_link');

// Load records grouped by parent_link and initialize pointers for each group
$groupedRecords = [];
$pointers = [];
foreach ($parentGroups as $parent) {
    $groupedRecords[$parent] = Good::where('parent_link', $parent)->get()->toArray();
    $pointers[$parent] = 0;
}

$orderedGoods = [];
$hasRemainingRecords = true;

// Cycle through each group, pulling one record at a time until all are used
while ($hasRemainingRecords) {
    $hasRemainingRecords = false;
    foreach ($parentGroups as $parent) {
        if ($pointers[$parent] < count($groupedRecords[$parent])) {
            $orderedGoods[] = $groupedRecords[$parent][$pointers[$parent]];
            $pointers[$parent]++;
            $hasRemainingRecords = true;
        }
    }
}

// Convert back to Eloquent models if needed
$orderedGoods = Good::hydrate($orderedGoods);

This manual method works but is less efficient for large datasets compared to the window function approach.

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