View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0001573||Composr||core||public||2014-02-16 14:06||2019-07-31 19:26|
|Reporter||Chris Graham||Assigned To|
|Fixed in Version|
|Summary||0001573: Database natural sorting|
|Description||Data comes out in string order, not natural order. This is throughout Composr, but also pretty much any webapp.|
Unfortunately MySQL does not even support natural ordering natively, so there's no easy fix.
A workaround would be to do an ORDER BY such that any numbers are padded out to say 30 characters. E.g. 123 becomes 000000000000000000000000000123. This effectively normalises it.
A big downside is this would break the ability for MySQL to use an index when paginating, so would work very poorly on large amounts of data.
A better approach is to have a custom field automatically maintained, containing these normalised values, probably filled in via a CRON maintenance script.
|Tags||Type: Cross-cutting feature|
|Time estimation (hours)||100|
||NB: Simple workaround is for webmaster to put leading zeros in when they add content.|
Another solution would be to do it inefficiently within the Composr database API. Load in all rows and do sorting in memory (sort_maps_by can now do natural sorting). This may actually be fine, as people will typically not need natural sorting when they have very large numbers of rows, it's for smaller more intuitive data sets.
||Instead of implementing this, implementing 0000786 and related issues (manual reordering) may be fine.|
|2016-12-08 16:19||Chris Graham||Tag Attached: Type: Cross-cutting feature|
|2018-03-13 21:16||Chris Graham||Note Added: 0005582|
|2018-03-13 21:17||Chris Graham||Relationship added||related to 0000786|
|2018-03-13 21:17||Chris Graham||Note Added: 0005583|
|2018-03-13 21:18||Chris Graham||Note Edited: 0005582||View Revisions|
|2019-07-31 19:25||Chris Graham||Relationship added||related to 0003848|
|2019-07-31 19:25||Chris Graham||Relationship added||related to 0003288|
|2019-07-31 19:26||Chris Graham||Relationship deleted||related to 0000786|