View Issue Details

IDProjectCategoryView StatusLast Update
0001573Composrcorepublic2019-07-31 19:26
ReporterChris GrahamAssigned To 
Severityfeature 
Status non-assignedResolutionopen 
Product Version 
Fixed in Version 
Summary0001573: Database natural sorting
DescriptionData 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.
TagsType: Cross-cutting feature
Attach Tags
Time estimation (hours)100
Sponsorship open

Relationships

related to 0003848 non-assigned Metaphonic search indexing 
related to 0003288 non-assigned Major overhaul of fulltext search support 

Activities

Chris Graham

2014-02-16 14:07

administrator   ~0002047

NB: Simple workaround is for webmaster to put leading zeros in when they add content.

Chris Graham

2018-03-13 21:16

administrator   ~0005582

Last edited: 2018-03-13 21:18

View 2 revisions

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.

Chris Graham

2018-03-13 21:17

administrator   ~0005583

Instead of implementing this, implementing 0000786 and related issues (manual reordering) may be fine.

Issue History

Date Modified Username Field Change
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