I have a field which contains alpha numeric text. Often these will contain things like:
Day 1
Day 2
Day 3
…
Day 10
Day 11
The problem is when this field is sorted they come out as
Day 1
Day 10
Day 11
Day 3
etc.
I found a solution using ORDER BY LENGTH(alphanumeric), alphanumeric however it’s a complex query that breaks when I try to convert it to a custom query.
Any ideas on how I can achieve the natural order sort?
Thanks
Hi Heather, one possible solution can be to get the numeric value from the alphanumeric value and save it as a separate field, say sort_order in the database and then sort the records by this field. Below is an example of the code to get the numeric value:
<input id="day" name="day" type="text" class="form-control">
<input id="sort_order" name="sort_order" type="text" class="form-control" dmx-bind:value="day.value.substr(4, 2).trim()">
Thanks. Good idea but as the numbers aren’t always in the same place though so that wouldn’t work. For instance, it could be something like Paris Day 1, Paris Day 2, London Day 1, London Day 2 etc.
So you found a solution and it works as you intend? But it breaks when you try to convert to a custom query?
You may use numbering like day 01, day 02 etc.
I’m pretty sure you can’t convert a query that uses arrays and .split into a custom query.
This is what it looks like after converting…
SELECT DISTINCT tbl_albums.album_id, tbl_albums.trip_id, tbl_albums.user_id, tbl_albums.album_name, tbl_albums.album_url, tbl_albums.cloudinary_version, tbl_albums.viewable_id
FROM tbl_albums
LEFT JOIN tbl_album_triptakers ON tbl_album_triptakers.album_id = tbl_albums.album_id LEFT JOIN view_album_tripbits ON view_album_tripbits.album_id = tbl_albums.album_id
WHERE tbl_albums.trip_id = :P1 /* {{$_GET.trip_id}} */ AND tbl_album_triptakers.triptaker_id IN ({{$_GET.triptakers.split(’,’)}}) AND view_album_tripbits.tripbit_id IN ({{$_GET.tripbits.split(’,’)}}) AND view_album_tripbits.category_id IN ({{$_GET.cats.split(’,’)}}) AND view_album_tripbits.country IN ({{$_GET.countries.split(’,’)}}) AND view_album_tripbits.city IN ({{$_GET.cities.split(’,’)}})
ORDER BY tbl_albums.album_name ASC
MySQL can’t use those $_GET values.
Not sure if there’s a way around that?
That would definitely work, however getting users to use that convention would be difficult. Thanks for the idea though.
Assuming an example from your query:
IN ({{$_GET.cities.split(’,’)}})
The .split causes the string $_GET.cities to become an array. You guessed right you can’t spat a JavaScript array into a string.
However, looking at a MySQL IN documentation, we see something like:
SELECT * FROM Customers
WHERE City IN ('Sydney', 'Paris', 'London');
So, how can you convert $_GET.cities into something usable here?
How can you convert 'Sydney,Paris,London' into 'Sydney','Paris','London', so it can be directly injected into the SQL expression?
There are multiple choices here, such as writing a custom formatter, or piecing together some Wappler steps:
Repeat $_GET.cities.split(',')
Set Value city = {{ "'" + $value + "'" }}
Set Value cities = repeat.flatten('city')
Caution: City names must be filtered to only allow az-AZ-09-space characters, to avoid a SQL Injection security vulnerability.
Thanks for that. I’ll give it a go.
LOL! I should have given more thought to how the solution I thought would work works because it really only works if the field has all the same length, except for the number. So it works for
Day 1
Day 2
Day 3
…
Day 10
But not for
Paris Day 1
Paris Day 2
London Day 1
London Day 2
…
London Day 10
In any case, I have found a fairly nice solution using FIND_IN_SET for the issue of arrays in custom SQL which might be handy for others going forward.
SELECT DISTINCT tbl_albums.album_id, tbl_albums.trip_id, tbl_albums.user_id, tbl_albums.album_name, tbl_albums.album_url, tbl_albums.cloudinary_version, tbl_albums.viewable_id
FROM tbl_albums
LEFT JOIN tbl_album_triptakers ON tbl_album_triptakers.album_id = tbl_albums.album_id LEFT JOIN view_album_tripbits ON view_album_tripbits.album_id = tbl_albums.album_id
WHERE tbl_albums.trip_id = :P1
AND IF(:P2, FIND_IN_SET(:P2, tbl_album_triptakers.triptaker_id), 1=1)
AND IF(:P3, FIND_IN_SET(:P3, view_album_tripbits.tripbit_id), 1=1)
AND IF(:P4, FIND_IN_SET(:P4, view_album_tripbits.category_id), 1=1)
AND IF(:P5, FIND_IN_SET(:P5, view_album_tripbits.country), 1=1)
AND IF(:P6, FIND_IN_SET(:P6, view_album_tripbits.city), 1=1)
ORDER BY LENGTH(tbl_albums.album_name), tbl_albums.album_name
I could probably write a custom formatter for that, but it would be nice if that was included in Wappler as it would save using .split(’,’) which I’ve always found a bit inelegant and not intuitive.
Back to the drawing board.
FYI - the 1=1 is there because MySQL requires an ELSE when using IF. I tried using CASE WHEN but couldn’t get it to work.
It ended up just being too complicated so I added a Date field to use for sorting. That had its own little challenge as I needed the records with dates to show before the ones without. So my custom query ended up being useful after all!
SELECT DISTINCT tbl_albums.album_id, tbl_albums.trip_id, tbl_albums.user_id, tbl_albums.album_name, tbl_albums.album_url, tbl_albums.cloudinary_version, tbl_albums.viewable_id
FROM tbl_albums
LEFT JOIN tbl_album_triptakers ON tbl_album_triptakers.album_id = tbl_albums.album_id LEFT JOIN view_album_tripbits ON view_album_tripbits.album_id = tbl_albums.album_id
WHERE tbl_albums.trip_id = :P1
AND IF(:P2, FIND_IN_SET(:P2, tbl_album_triptakers.triptaker_id), 1=1)
AND IF(:P3, FIND_IN_SET(:P3, view_album_tripbits.tripbit_id), 1=1)
AND IF(:P4, FIND_IN_SET(:P4, view_album_tripbits.category_id), 1=1)
AND IF(:P5, FIND_IN_SET(:P5, view_album_tripbits.country), 1=1)
AND IF(:P6, FIND_IN_SET(:P6, view_album_tripbits.city), 1=1)
ORDER BY (tbl_albums.album_date IS NULL), tbl_albums.album_date ASC
There’s almost always something after the number like…
Paris Day 1 - The Louvre and Tuilleries
Paris Day 2 - Versaille
Paris Day 3 - etc.
But if there weren’t that would be a great solution. Thx.
Maybe add a separate database column - “day” or “sort order”, which is number and just sort the query by it.
I haven’t got much experience but let me ask you something @Heather_Mann…
Can’t you just clear this up on client-side, inside your form (or however you handle these inputs) and set them in manageable format before you send them server-side?
I haven’t even understand how those inputs are handled or where the “Paris…” or the “Day 1/2/3” come from but let me say it even if it goes to the garbage…
For example:
group them in selects someway
*(“Paris”, “London”, “Athens”…) ,
on update of the input just change the “day 1” to “day 01”
Do you have the ability to work on some of the above mentioned ways?
I ended up adding a date field and sorted it on that. Thx.
Interesting idea. I ended up just adding a date field to sort on which is working well but thanks for the idea anyway.
