Level 122
You can sort by date then rank, or by rank then date .... one or the other...
Mar 10, 2023
7
Level 1
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
Level 1
Sadly, your answer does not help me. You suggest something like this:
$products = DB::table('products')
->orderByDesc('rank_index')
->orderByDesc('created_at')
->get();
This will give me a list of all products ranked by created_at, yes. BUT all other products with a rank_index are listed first in the list. That is not my goal. My goal is to have a product at a specific place. For example, the rank_index is five, then the product should be the fifth product in my collection.
Level 122
Its a strange setup. I can only suggest one query to get unranked items and then another to get ranked, then iterate over the ranked items and insert them into the list at the rank position
Level 1
@Snapey Do you think this will also work with a large-scale database with 100000 products and a paginated result?
Level 122
@FrazeColder i assume ranked products might be limited to page 1
In which case your issue is how many ranked items there are
Level 10
Honestly, the desired output you showed doesn't makes sense to me.
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
The products with a rank_index don't like they're pinned.
If you want to "pin" "Green Carpet" and "Another product" first you have make an order so they will always come up first, no matter you order them (asc or desc), or how you order the other columns for that matter
select
*
from
products
order by
case when rank_index is null then 0 else 1 end desc
, rank_index desc /* or asc , whatever you need */
, created_at asc
Whether rank_index is null is one option. Maybe you to pin products with rank_index of 4 or above. You can work that out in the first order by expression.
Level 1
@psrz Your answer also shows all products with a rank_index on top and then all other products. That's not my goal.
The rank_index represents the position in the list the product should get. If the rank_index is 7 then the product should be the seventh's product in the list. That is my goal. Does that make sense?
Please or to participate in this conversation.