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
];