Laravel Eloquent sortiert nach Datum und speichert Einträge auch nach Rangliste

Question

Ich erstelle ein Projekt mit Laravel und habe eine Tabelle mit allen meinen Produkten. In dieser Tabelle werden täglich Produkte hinzugefügt und ich zeige alle Produkte auf der Seite sortiert nach created_at an. Dies kann einfach mit Laravel Eloquent und ->orderBy('created_at', 'DESC') durchgeführt werden.

Allerdings möchte ich die Möglichkeit haben, bestimmte Produkte an einen bestimmten Ort zu „pinnen“/zu „pinnen“. Dazu habe ich die Spalte rank_index erstellt, die die Nummer enthält, die das Produkt in der zurückgegebenen Abfragesammlung haben soll.

Das ist meine aktuelle Tabelle:

title rank_index erstellt_at
Ein tolles Produkt 2023-01-01 10:04:00
Ein weiteres Produkt 4 2023-01-01 10:00:00
Babyauto 2023-01-01 10:05:00
Grüner Teppich 2 01.01.2023 10:08:00
Spielzeug 2023-01-01 10:07:00

Die folgende Tabelle zeigt die Sammlung, die die Abfrage zurückgeben soll:

title rank_index erstellt_at
Spielzeug 2023-01-01 10:07:00
Grüner Teppich 2 01.01.2023 10:08:00
Babyauto 2023-01-01 10:05:00
Ein weiteres Produkt 4 2023-01-01 10:00:00
Ein tolles Produkt 2023-01-01 10:04:00

Ich hoffe, es gibt eine Lösung, um eine solche Tabelle direkt aus der Datenbank zurückzugeben. Auf diese Weise muss ich die Sammlung nicht aufteilen, was die Anfrage viel langsamer macht! Andernfalls muss ich PHP-Funktionen verwenden, um die Sammlung neu anzuordnen, aufzuteilen und aufzuteilen.

Ich würde mich über jede Hilfe freuen!

Mit freundlichen Grüßen

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
)

这是一个可以玩的dbfiddle。

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