Level 104
Please format your code - it is very difficult to understand that word salad
Nov 23, 2023
13
Level 1
Laravel sorting the alphanumeric data
Hi i am trying to sort an alphanumeric data in laravel but i have not been able to figure out how to do so
0 => "HMO Rent Guarantee Insurance - 4 Bedroom (£0 - £500pcm)"
1 => "HMO Rent Guarantee Insurance - 4 Bedroom (£501 - £3,000pcm)"
2 => "HMO Rent Guarantee Insurance - 6 Bedroom (£0 - £500pcm)"
3 => "HMO Rent Guarantee Insurance - 6 Bedroom (£501 - £3,000pcm)"
4 => "Rent Guarantee Insurance (£0 - £500pcm)"
5 => "Rent Guarantee Insurance (£3,001 - £8,000pcm)"
6 => "Rent Guarantee Insurance (£501 - £3,000pcm)"
I tried doing it this way
$data = $Product::orderByRaw("CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(name, '£', -1), ' ', 1), 'pcm', 1) AS SIGNED)") ->orderBy('name') ->get()
this is the kind of data i have and i want it to show like
0 => "HMO Rent Guarantee Insurance - 4 Bedroom (£0 - £500pcm)"
1 => "HMO Rent Guarantee Insurance - 4 Bedroom (£501 - £3,000pcm)"
2 => "HMO Rent Guarantee Insurance - 6 Bedroom (£0 - £500pcm)"
3 => "HMO Rent Guarantee Insurance - 6 Bedroom (£501 - £3,000pcm)"
4 => "Rent Guarantee Insurance (£0 - £500pcm)"
5 => "Rent Guarantee Insurance (£501 - £3,000pcm)"
6 => "Rent Guarantee Insurance (£3,001 - £8,000pcm)"
i want them to be sorted in the alphabetical order as well as the numbers that are showing in the string if i try to sort it out by extracting the numbers part and with alphabetically, the alphabetic sorting comes up wrong, i am stuck here, can anyone help me figure it out thanks
Level 1
@tykus sorry about that, did it now
Level 75
@waleed_lara_1 try to query the data and use ORDER BY.
Level 122
Add a column that you can order by.
Level 1
@Snapey I did do that, i tried putting those numeric values in another column and then added an order by for both name column and that column but then the alphabbetic order gets all messed up
Level 122
@waleed_lara_1 you could give each a sortable code like
- HMORG40000
- HMORG40500
- HMORG60000
- HMORG60500
- RENTG0000
- RENTG0500
- RENTG3000
which would then be naturally sortable
But if there are only 7 rows, why are they even in the database?
Level 1
@Snapey ok but i have like 150 of these products, some of them have these numbers in their name and some of them don't, how would i give each one these sortable code, and i would have to do them automatically each time a new product is added as this is one of my client's website, they would not want to give it a sortable code manually
Level 122
@waleed_lara_1 Well, hmm let me think...
a) if you have more complex data than in your question then how does anyone else know?
b) How do you expect to sort random strings into some predictable order when the user can type whatever they like
Level 1
@Snapey to answer first part of your question
a) These products are insurance products like some of them can be divided into more then one product like Rent Guarantee Insurance (£0 - £500pcm) this can range from 0 to 500 and then another which can range from 500 to 1000 but it's not necessary that all products have these ranges some of them can be simple products like Rent Guarantee Insurance, so they want to show these products whom have range in their correct order
b) They can't type in anything they want, if a string will have number they will only be in this format (£0 - £500pcm)
Level 122
@waleed_lara_1 so they could key a range as from and to into distinct fields?
You could sort by description and the from field, and when displaying you can format the description, the from and to (if set) into a display string using an accessor
Level 1
@Snapey I did try making another column in the database where i stored the to value like if the range was 0 to 500, i saved 500 and then tried to sort with the name and this to column but then the alphabetic order was getting all mixed up
Level 75
@waleed_lara_1 try chaining the ORDER BY. You need two.
Make this part a column HMO Rent Guarantee Insurance - 4 Bedroom
And make another column £0 - £500pcm
Level 1
@jlrdw i was doing by both then, First by name and then by that another column i had added where all the values were, but they did not remain in alphabetic order anymore then
Please or to participate in this conversation.