1573: Database natural sorting - Composr CMS feature tracker

ID	Project	Category	View Status	Date Submitted	Last Update

1573	Composr	core	public	2014-02-16 14:06	2019-07-31 19:26

Reporter	Chris Graham	Assigned To	Guest
Priority	normal	Severity	feature
Status	new	Resolution	open

Summary	1573: 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
Attach Tags	(Separate by ",")

Time estimation (hours)	100
Sponsorship open

Date Added	Member	Amount Sponsored

Chris Graham 2014-02-16 14:07 administrator ~2047	NB: Simple workaround is for webmaster to put leading zeros in when they add content.

Chris Graham 2018-03-13 21:16 administrator ~5582 Last edited: 2018-03-13 21:18	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 ~5583	Instead of implementing this, implementing 786 and related issues (manual reordering) may be fine.

View Status	Private (for security issues or disclosing of private information; if you submit as a guest, you will not be able to see your submission)
Note
Upload Files Maximum size: 32,768 KiB	Attach files by dragging & dropping, selecting or pasting them.
You are not logged in	You are not logged in. This means you will not get any e-mail notifications. And if you reply, we will not know for sure you are the original poster of the issue.

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 786
2018-03-13 21:17	Chris Graham	Note Added: 0005583
2018-03-13 21:18	Chris Graham	Note Edited: 0005582
2019-07-31 19:25	Chris Graham	Relationship added	related to 3848
2019-07-31 19:25	Chris Graham	Relationship added	related to 3288
2019-07-31 19:26	Chris Graham	Relationship deleted	related to 786