Laravel Eloquent 依日期排序，也依排名索引來儲存條目

Question

我正在使用 Laravel 建立一個項目，並且我有一個包含我所有產品的表。在該表上，產品每天都會添加，並且我會在頁面上顯示按 created_at 排序的所有產品。使用 Laravel Eloquent 和 ->orderBy('created_at', 'DESC') 可以輕鬆完成這項工作。

但是，我希望能夠將某些產品「固定」/「固定」到某個位置。為此，我建立了 rank_index 列，其中包含產品在傳回的查詢集合中應具有的編號。

這是我目前的表格：

title rank_index created_at
An awesome product 2023-01-01 10:04:00
Another product 4 2023-01-01 10:00:00
Baby car 2023-01-01 10:05:00
Green carpet 2 2023-01-01 10:08:00
Toy 2023-01-01 10:07:00

下表顯示了我希望查詢傳回的集合：

title rank_index created_at
Toy 2023-01-01 10:07:00
Green carpet 2 2023-01-01 10:08:00
Baby car 2023-01-01 10:05:00
Another product 4 2023-01-01 10:00:00
An awesome product 2023-01-01 10:04:00

我希望有一個解決方案，可以直接從資料庫傳回這樣的表。這樣我就不必對集合進行分割和切片，這使得請求速度慢得多！否則，我必須使用 PHP 函數重新排列、分割和切片集合。

我很高興能得到任何幫助！

親切的問候

Answer 1

我幾乎肯定會選擇銀的解

您的聲明：

沒有什麼意義。將兩個結果集拆分/切片/拼接在一起不太可能對效能產生可衡量的影響。它肯定不會使“請求變慢”！

這是針對您的場景的 SQL 解決方案，但幾乎肯定會比將兩個結果集拼接在一起要慢，具體取決於所涉及的表的大小。

-- this cte just gives is a contiguous sequence from 1 to number of toys
WITH RECURSIVE seq (n) AS (
    SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < (SELECT COUNT(*) FROM toys)
)
SELECT title, rank_index, created_at, n
FROM (
    -- we now add row_number to the seq after removing the seq numbers
    -- already used by rank_index
    SELECT seq.n, ROW_NUMBER() OVER (ORDER BY seq.n) AS rn
    FROM seq
    WHERE NOT EXISTS (SELECT 1 FROM toys WHERE rank_index = seq.n)
) x
JOIN (
    -- get toys without rank_index and add row_number for join to prev subquery
    SELECT *, ROW_NUMBER() OVER (ORDER BY created_at DESC) rn 
    FROM toys
    WHERE rank_index IS NULL
) y USING (rn)

UNION ALL

SELECT title, rank_index, created_at, rank_index
FROM toys
WHERE rank_index IS NOT NULL

-- applies to the result of UNION
ORDER BY n;

如果你有超過 1000 個玩具，遞歸 cte 將達到預設值cte_max_recursion_depth，如 這裡解釋。

您可以在上述查詢之前執行以下命令來刪除限制：

SET SESSION cte_max_recursion_depth = 10000; -- permit 10,000 iterations
SET SESSION cte_max_recursion_depth = 0;     -- unlimited iterations

或更改 遞歸 CTE 到非遞歸 CTE，其中 toys 表上的「nofollow noreferrer">ROW_NUMBER()：

WITH seq (n) AS (
    SELECT ROW_NUMBER() OVER (ORDER BY id) FROM toys
)

這是一個可以玩的db<>fiddle。

Answer 2

我今年就遇到過，直接在查詢中排序比較複雜，可以參考這個問題MySQL 結果集按固定位置排序如果你想深入了解它的複雜性。

我之前做的比較簡單，透過兩個查詢完成，

• 首先是查詢目前分頁範圍內的固定項目。
• 那麼第二個查詢就是依照日期排序的標準分頁查詢，
• 然後將固定項目推送到分頁查詢，並使用基於其列值的索引。

這是一個您可以參考的範例

$perPage = 10; 
$page = request('page') ?? 1;

$start = ($page - 1) * $perPage + (1); // get the start number of the pagination
$end = $perPage * $page; // get the end number of the pagination

//query the pinned items with fixed position between start and end of the current pagination
$pinned = Model::select('title','rank_index','created_at')->whereBetween('rank_index', [$start, $end])->get();

//standard pagination query, exclude the pinned items (if rank_index has value)
//you can also subtract the pinned result count on pagination if you want i.e. ->paginate( $perPage - $pinned->count() )
//but I prefer to leave it and modify the limit on the collection as to not messed the pagination per_page value which could potentially messed-up the front-end
$result = Model::select('title','rank_index','created_at')->whereNull('rank_index')->orderBy('created_at', 'DESC')->paginate( $perPage );

// insert the pinned items to the pagination data  with index based on rank_index value
$pinned->sortBy('rank_index')->each(function ($item) use (&$result) {
    $index = $item['rank_index'] - 1;
    $result->splice($index < 0 ? 0 : $index, 0, [$item]);
});

//making sure to only take the total of perPage incase there is a pinned item inserted on the paginated data
$result->setCollection($result->take($perPage));

return [
    'start' => $start,
    'end' => $end,
    'pinned' => $pinned,
    'result' => $result
];