Laravel Eloquent trie par date et enregistre également les entrées par index classé

Question

Je construis un projet en utilisant Laravel et j'ai un tableau avec tous mes produits. Sur ce tableau, les produits sont ajoutés quotidiennement et j'affiche tous les produits de la page triés par created_at. Cela peut être facilement fait en utilisant Laravel Eloquent et ->orderBy('created_at', 'DESC').

Cependant, j'aimerais pouvoir "épingler"/"épingler" certains produits à un certain emplacement. Pour ce faire, j'ai créé la colonne rank_index, qui contient le numéro que le produit doit avoir dans la collection de requêtes renvoyée.

Voici ma table actuelle :

titre Rank_index créé_at
Un produit génial 2023-01-01 10:04:00
Un autre produit 4 2023-01-01 10:00:00
Voiture bébé 2023-01-01 10:05:00
Tapis vert 2 2023-01-01 10:08:00
Jouet 2023-01-01 10:07:00

Le tableau suivant montre la collection que je souhaite que la requête renvoie :

titre Rank_index créé_at
Jouet 2023-01-01 10:07:00
Tapis vert 2 2023-01-01 10:08:00
Voiture bébé 2023-01-01 10:05:00
Un autre produit 4 2023-01-01 10:00:00
Un produit génial 2023-01-01 10:04:00

J'espère qu'il existe une solution pour renvoyer une telle table directement depuis la base de données. De cette façon, je n'ai pas besoin de diviser et de découper la collection, ce qui rend la demande beaucoup plus lente ! Sinon, je dois utiliser les fonctions PHP pour réorganiser, diviser et découper la collection.

Je serais ravi de recevoir de l'aide !

Cordialement

Answer 1

Je choisirai presque certainement la solution argent

Votre déclaration :

Cela n'a aucun sens. Il est peu probable que le fractionnement/découpage/assemblage des deux ensembles de résultats ait un impact mesurable sur les performances. Cela ne ralentira certainement pas les "demandes" !

Il s'agit d'une solution SQL adaptée à votre scénario, mais elle sera presque certainement plus lente que l'assemblage de deux ensembles de résultats, en fonction de la taille des tables impliquées.

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

Si vous possédez plus de 1000 jouets, le cte de récursion atteindra la valeur par défaut cte_max_recursion_degree, comme expliqué ici.

Vous pouvez supprimer la limite en exécutant la commande suivante avant la requête ci-dessus :

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

ou changez le CTE récursif en CTE non récursif avec "nofollow noreferrer">ROW_NUMBER() sur la table toys :

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

C'est un db<>fiddle avec lequel vous pouvez jouer.

Answer 2

Je l'ai rencontré cette année. Le tri directement dans la requête est plus compliqué. Vous pouvez vous référer à cette question Le jeu de résultats MySQL est trié par position fixeSi vous souhaitez comprendre sa complexité en profondeur.

Ce que je faisais auparavant était relativement simple et était complété par deux requêtes,

• La première consiste à interroger les éléments corrigés dans la plage de pagination actuelle.
• Ensuite, la deuxième requête est une requête de pagination standard triée par date,
• Ensuite, poussez l'élément fixe vers la requête paginée et utilisez un index basé sur la valeur de sa colonne.

Voici un exemple auquel vous pouvez vous référer

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