Question 15538 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel Eloquent sort by date but also by rank index to hold up entries

I am building a project with Laravel and I have a table which contains all my products. On that table, products get added daily, and I show all products on my page sorted by created_at. That’s a pretty easy job to do with Laravel Eloquent and ->orderBy('created_at', 'DESC').

However, I want to have the possibility to "hold up"/"pin" certain products to a certain place. For that, I have created the column rank_index which contains the number the product should have in the returned query collection.

This is my current table:

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

And the following table shows the collection I want my query to return:

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

Preferably, I would like to have a solution which directly returns me the table like this from the database. This way I don’t have to split and slice the collection, which makes the request much slower! Otherwise, I have to rearrange, split and slice the collection with PHP functions.

I am happy about any help!

Kind regards

Tags:

2

Answers


  1. 0

    I encountered this years back and its quite complicated to do the sorting directly in the query, you can refer to this question MySQL result set ordered by fixed positions if you want to dig in to its complexity.

    What I did before was a bit simpler and done with two queries,

    • First is querying the pinned items that is between the range of the current pagination.
    • Then the second query is the standard pagination query ordered by date,
    • Then pushing the pinned items to the pagination query with index based on its column value.

    Heres an example you can refer

    $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
];
    Login or Signup to reply.
  2. 0

    I would almost certainly go with silver’s solution

    Your statement:

    This way I don’t have to split and slice the collection, which makes the request much slower!

    makes little sense. Splitting/slicing/splicing the two result sets together is unlikely to have a measurable impact on performance. It certainly won’t make the "request much slower"!

    Here is a SQL solution to your scenario, but it will almost certainly be slower than splicing the two result sets together, depending on the size of the tables involved.

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

    If you have more than 1000 toys the recursive cte will hit the default cte_max_recursion_depth, as explained here.

    You could run the following before the above query to remove the limit:

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

    or change the RECURSIVE CTE to a non-recursive one with ROW_NUMBER() on the full toys table:

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

    Here is a db<>fiddle to play with.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search