Laravel Eloquent mengisih mengikut tarikh dan juga menyimpan entri mengikut indeks kedudukan

Question

Saya sedang membina projek menggunakan Laravel dan saya mempunyai jadual dengan semua produk saya. Pada jadual ini, produk ditambah setiap hari dan saya memaparkan semua produk pada halaman yang diisih mengikut created_at. Ini boleh dilakukan dengan mudah menggunakan Laravel Eloquent dan ->orderBy('created_at', 'DESC').

Walau bagaimanapun, saya ingin dapat "pin"/"pin" produk tertentu ke lokasi tertentu. Untuk melakukan ini, saya mencipta lajur rank_index yang mengandungi nombor yang sepatutnya ada pada produk dalam koleksi pertanyaan yang dikembalikan.

Ini ialah jadual semasa saya:

title rank_index created_at
Produk hebat 2023-01-01 10:04:00
Produk lain 4 2023-01-01 10:00:00
Kereta bayi 2023-01-01 10:05:00
Permaidani hijau 2 2023-01-01 10:08:00
Mainan 2023-01-01 10:07:00</pra>
<p>Jadual berikut menunjukkan koleksi yang saya mahu pertanyaan dikembalikan: </p>
<pre class="brush:php;toolbar:false;">title rank_index created_at
Mainan 2023-01-01 10:07:00
Permaidani hijau 2 2023-01-01 10:08:00
Kereta bayi 2023-01-01 10:05:00
Produk lain 4 2023-01-01 10:00:00
Produk hebat 2023-01-01 10:04:00

Saya harap ada penyelesaian untuk mengembalikan jadual sedemikian terus daripada pangkalan data. Dengan cara ini saya tidak perlu membelah dan menghiris koleksi yang menjadikan permintaan lebih perlahan! Jika tidak, saya perlu menggunakan fungsi PHP untuk menyusun semula, memisahkan dan menghiris koleksi.

Saya berbesar hati untuk sebarang bantuan!

Sekian

Answer 1

Saya hampir pasti akan memilih penyelesaian perak

Kenyataan Anda:

Tidak masuk akal. Membelah/menghiris/mencantum kedua-dua set hasil bersama-sama tidak mungkin mempunyai kesan yang boleh diukur pada prestasi. Ia pastinya tidak akan membuat "permintaan lambat"!

Ini ialah penyelesaian SQL untuk senario anda, tetapi hampir pasti akan lebih perlahan daripada mencantumkan dua set hasil bersama-sama, bergantung pada saiz jadual yang terlibat.

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

Jika anda mempunyai lebih daripada 1000 mainan, cte rekursi akan mencapai nilai lalai cte_max_recursion_depth, seperti yang dijelaskan di sini.

Anda boleh mengalih keluar had dengan menjalankan arahan berikut sebelum pertanyaan di atas:

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

atau tukar rekursif CTE kepada CTE bukan rekursif dengan "nofollow noreferrer">ROW_NUMBER() pada jadual mainan:

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

Ini adalah db<>fiddle yang boleh anda mainkan.

Answer 2

Saya telah menemuinya tahun ini Menyusun terus dalam pertanyaan adalah lebih rumit Anda boleh merujuk kepada soalan ini Set hasil MySQL diisih mengikut kedudukan tetapJika anda ingin memahami kerumitannya secara mendalam.

Apa yang saya lakukan sebelum ini agak mudah dan diselesaikan melalui dua pertanyaan,

• Yang pertama adalah untuk menanyakan item tetap dalam julat halaman semasa.
• Kemudian pertanyaan kedua ialah pertanyaan paging standard yang diisih mengikut tarikh,
• Kemudian tolak item tetap ke pertanyaan bernombor dan gunakan indeks berdasarkan nilai lajurnya.

Berikut adalah contoh yang boleh anda rujuk

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