[MySQL] Natural Sort?

[BastiaanN]

Hallo Iedereen,

ik ben momenteel bezig met het ontwikkelen van een webshop in php en MySQL. nu loop ik tegen een nogal vervelend probleem aan, en heb het niet kunnen oplossen zonder de query enorm te vertragen of via een omweg.

Het Probleem; de productlijst moet gesorteerd worden op "menselijke" logica, dat zou dan betekenen dat iets op de volgende manier gesorteerd wordt:

Memorycard 256MB
Memorycard 512MB
Memorycard 1024MB
Memorycard 2048MB

echter behandeld MySQL het geheel als text ( logisch, omdat het een textveld is ) en sorteert het op deze manier:

Memorycard 1024MB
Memorycard 256MB
Memorycard 2048MB
Memorycard 512MB

ik zocht dus eigenlijk een manier om het op de "menselijke" manier te sorteren in MySQL. er is een mogelijkheid om het in PHP te doen met natcasesort() maar om nou eerst de hele database naar binnen te trekken, en daarna met array's te gaan werken is een bottleneck voor de snelheid en ook niet echt efficiënt.

Nu las ik een tutorial dat het volgende de oplossing bied:
SELECT * FROM `database` ORDER BY `text_field` + 0

dit schopt echter de querytijd om hoog van 0.13 naar 4.75 seconden per query voor een database met 10.000 producten, dus daar zit ik ook niet echt op te wachten.

Misschien heeft iemand ooit een vergelijkbaar probleem gehad, en een oplossing voor mij? Zou zeer gewaardeerd worden!

[P_de_B]

Een extra veld 'volgorde' toevoegen, en dan daar op sorteren? Ik denk niet dat je een ander antwoord krijgt dat universeel toepasbaar is.

[RobIII]

bassjuh schreef op dinsdag 08 mei 2007 @ 15:26:
Nu las ik een tutorial dat het volgende de oplossing bied:
SELECT * FROM `database` ORDER BY `text_field` + 0

dit schopt echter de querytijd om hoog van 0.13 naar 4.75 seconden per query voor een database met 10.000 producten, dus daar zit ik ook niet echt op te wachten.

Ik neem aan dat je niet meteen alle 10.000 producten wil hebben maar een selectie daarvan (met een WHERE clause dus), niet?

[P.O. Box]

je zou het eerste getal uit de string eruit kunnen vissen en casten/converten naar een int....
alhoewel dat je querytijd dan waarschijnlijk ook wel omhoog zal schoppen...

[ Voor 29% gewijzigd door P.O. Box op 08-05-2007 15:32 ]

[BastiaanN]

RobIII schreef op dinsdag 08 mei 2007 @ 15:28:
[...]

Ik neem aan dat je niet meteen alle 10.000 producten wil hebben maar een selectie daarvan (met een WHERE clause dus), niet?

Ja, uiteraad alleen de producten die voor die categorie toepasbaar zijn. maar het probleem is dan dat ik wel ALLE producten naar binnen moet halen die in die categorie staan en mysql moet wel eerst op alle velden die +0 toepassen voor dat het geheel naar php wordt verzonden ( anders zou het geheel niet goed sorteren, niet? ). qua snelheid maakt dat dus niet geweldig veel uit

Het voordeel van alles op database niveau doen is dat ik met een LIMIT kan werken (per pagina), wat dus een hoop performance scheelt.

[Verwijderd]

Kun je die productnaam (of het producttype) splitsen in "MemoryCard" en "1024" en (misschien) "MB"?
Dan kan die 1024 gewoon een integer worden. Met "Concat" kun je het dan weer aan elkaar "Plakken"

[BastiaanN]

Verwijderd schreef op dinsdag 08 mei 2007 @ 15:33:
Kun je die productnaam (of het producttype) splitsen in "MemoryCard" en "1024" en (misschien) "MB"?
Dan kan die 1024 gewoon een integer worden. Met "Concat" kun je het dan weer aan elkaar "Plakken"

Ik denk dat ik hier dan een probleem krijg met de veelal verschillende producten die in de database staan. het ene product bestaat uit meerdere woorden dan het andere product. En het ene product heeft de numerieke waarde vooraan staan ipv achteraan, dit zou dan (denk ik?) betekenen dat het geheel nog niet 100% naar wens sorteert.

[TERW_DAN]

Verwijderd schreef op dinsdag 08 mei 2007 @ 15:33:
Kun je die productnaam (of het producttype) splitsen in "MemoryCard" en "1024" en (misschien) "MB"?
Dan kan die 1024 gewoon een integer worden. Met "Concat" kun je het dan weer aan elkaar "Plakken"

Dan zou ik er persoonlijk voor kiezen om 1 veld toe te voegen met daarin de grootte (in MB of GB) zodat je de er neer kan zetten, dan kun je gewoon daarop sorteren (en als je het goed invoert kom je ook niet het probleem tegen dat 1GB kleiner is dan 1024MB). Dus de tabel laat je lekker zo als hij is, je voegt er alleen 1 dingetje aan toe dat je kunt gebruiken om te sorteren.

[BastiaanN]

Terw_Dan schreef op dinsdag 08 mei 2007 @ 15:37:
[...]


Dan zou ik er persoonlijk voor kiezen om 1 veld toe te voegen met daarin de grootte (in MB of GB) zodat je de er neer kan zetten, dan kun je gewoon daarop sorteren (en als je het goed invoert kom je ook niet het probleem tegen dat 1GB kleiner is dan 1024MB). Dus de tabel laat je lekker zo als hij is, je voegt er alleen 1 dingetje aan toe dat je kunt gebruiken om te sorteren.

Dit lijkt in eerste instantie een goed idee, maar dan kom ik weer met het probleem dat de webshop niet alleen memory cards bevat maar ook telefoons,processoren etc. als ik dus een numerieke waarde heb die los staat van bijv. een geheugen groote maar bijv. het type nummer aangeeft, dan hang ik wederom

[mjax]

De gangbare oplossing is inderdaad om een apart veld op te nemen dat de sortering aangeeft. Dit kan een numerieke waarde zijn, die je via een (bijvoorbeeld) drag-and-drop interface laat beheren, of een alphanumeriek waarde. Voor 'Memorycard 1024MB' zet je in het sorteer veld dan bijvoorbeeld '1024MB Memorycard'. Het invullen van dit veld zou dan een handmatige handeling bij het opvoeren/bewerken van producten zijn.

[Verwijderd]

Ik denk dan toch dat misschien je table design beter moet. Raar dat je specs niet in aparte kolommen staan, maar in een textveld.

[Spotmatic]

Ik ben al jaren gewoon om dit soort tabellen op dezelfde manier te ontwerpen en gebruik altijd twee velden: omschrijving en zoeksleutel. De omschrijving bevat de artikeltekst die op de website komt, de zoeksleutel is het veld waarop gesorteerd wordt. Heel simpel en doeltreffend!

[BastiaanN]

Verwijderd schreef op dinsdag 08 mei 2007 @ 15:42:
Ik denk dan toch dat misschien je table design beter moet. Raar dat je specs niet in aparte kolommen staan, maar in een textveld.

de specs,omschrijving en productnaam zijn gescheiden. het gaat hier enkel om de productnaam. bijv. als ik 3 producten heb namelijk:

CT300 Phone
CT3200 Phone
CT400 Phone

dan heeft dat in mijn ogen niks met het database ontwerp te maken, maar wel met een mens-onlogische manier van sorteren.

[P_de_B]

Je hebt het steeds over 'mens'-logisch sorteren. Dat betekent echt dat een mens de sortering (via een extra kolom) aan zal moeten brengen.

[Verwijderd]

ik zou inderdaad ook een apart veld opnemen met groottes, snelheden e.d. en een veld met de eenheid ervan, dan krijg je dus:

3 en ghz
1024 en MB
1 en stuks

lijkt me toepasbaar voor veel dingen, indien je er echt niets in kan zetten laat je het veldje leeg.

[sjongenelen]

je zou kunnen tellen (*ik deed dit met vba) vanaf links naar de spatie (whitespace) en dan vanaf daar pas de cijfers gaan tellen?

geen idéé of dit mogelijk is met SQL/PHP

[BastiaanN]

Ik wijk inderdaad maar uit naar oplossing van aparte database velden voor numerieke gegevens. Wel jammer dat er geen natcasesort functie in MySQL zit, maar wie weet wat de toekomst brengt

Bedankt iedereen!

[curry684]

P_de_B schreef op dinsdag 08 mei 2007 @ 15:28:
Een extra veld 'volgorde' toevoegen, en dan daar op sorteren? Ik denk niet dat je een ander antwoord krijgt dat universeel toepasbaar is.

Jawel hoor. Metadata.

edit:
triest overigens dat ik de eerste ben die dat zegt

[ Voor 11% gewijzigd door curry684 op 08-05-2007 16:00 ]

[P_de_B]

curry684 schreef op dinsdag 08 mei 2007 @ 15:58:
[...]

Jawel hoor. Metadata.

True. Ik had moeten schrijven, een extra veld toevoegen ipv een extra veld 'volgorde'

Overigens is voor sorteren toch een expliciet 'sorteervolgordeveld' vaak wel handig. Soms wil je bijvoorbeeld oplopend op het veld 'grootte' sorteren, soms aflopend.

[curry684]

Hangt ervan af. Sort order is mooi voor semi-statische data. Als je met geheugenmodules e.d. werkt die dagelijks veranderen, verdwijnen en verschijnen kun je beter gewoon je metadata goed opzetten en zorgen dat je daar generiek op kunt sorteren. Gezien de varieteit aan veldtypes is het overigens ook vaak een aardig idee om de sortering buiten de database te trekken qua flexibiliteit en performance.

Enne, metadata is wel wat meer dan 'een extra veldje', maar dat had je geloof ik al door

[P_de_B]

curry684 schreef op dinsdag 08 mei 2007 @ 16:19:
Enne, metadata is wel wat meer dan 'een extra veldje', maar dat had je geloof ik al door

jaja

[Verwijderd]

Je kan ook gewoon op prijs sorteren komen de producten ook meest wel goed

[tw_eek]

Een hele snelle oplossing: zorg ervoor dat in je tekst velden de getallen evenveel cijfers hebben, in dit geval vier. Dan loopt de sortering automatisch goed. Dit werkt natuurlijk alleen bij 1 dimensie.

[Noork]

Verwijderd schreef op vrijdag 08 juni 2007 @ 14:22:
Je kan ook gewoon op prijs sorteren komen de producten ook meest wel goed

Idd, ik zie het probleem ook niet zo. Eventueel nog op capaciteit. Heeft de TS niet bij andere webshops gekeken hoe ze dat daar hebben gedaan? Check b.v. Azerty.

[Verwijderd]

Noork schreef op vrijdag 08 juni 2007 @ 14:30:
[...]

Idd, ik zie het probleem ook niet zo. Eventueel nog op capaciteit. Heeft de TS niet bij andere webshops gekeken hoe ze dat daar hebben gedaan? Check b.v. Azerty.

Ik ben heel toevallig de programmeur bij Azerty...

In de database hebben we aparte tabellen voor specificaties staan waar op gesorteerd kan worden.
De waardes worden zonder eenheden opgeslagen, waardoor je op de juiste manier kan sorteren.
De tabel heeft wel 1 kolom met varchar waardes en 1 kolom met float waardes. Anders kun je nog niet sorteren op de getallen.

[sopsop]

Verwijderd schreef op vrijdag 08 juni 2007 @ 15:04:
[...]


Ik ben heel toevallig de programmeur bij Azerty...

In de database hebben we aparte tabellen voor specificaties staan waar op gesorteerd kan worden.
De waardes worden zonder eenheden opgeslagen, waardoor je op de juiste manier kan sorteren.
De tabel heeft wel 1 kolom met varchar waardes en 1 kolom met float waardes. Anders kun je nog niet sorteren op de getallen.

Wel als je voorloopnullen gebruikt
0256
0512
1024

[Verwijderd]

moet je van te voren wel weten wat de langste waarde zal worden.
Niet echt praktisch naar mijn mening.

[Janoz]

Heel leuk sopsop, maar toen hadden we ineens de 10240. Wil je dan in de DB alles gaan updaten door er een extra 0 voor te plakken voordat je de nieuwe waarde invoert?

Leuk bedacht, maar onwerkbaar..

[P.O. Box]

Janoz schreef op vrijdag 08 juni 2007 @ 15:14:
Heel leuk sopsop, maar toen hadden we ineens de 10240. Wil je dan in de DB alles gaan updaten door er een extra 0 voor te plakken voordat je de nieuwe waarde invoert?

Leuk bedacht, maar onwerkbaar..

nou onwerkbaar valt ook wel weer mee... 1 simpel update statement (het is tenslotte een sorteerveld, niet een PK of FK ofzo), en je kan weer tijden verder....

mijn voorkeur heeft het overigens ook niet...

[Eskimootje]

Je kan toch op het moment dat je de spullen insert met een regex de eerste reeks cijfers ophalen en daarna de tekst reeks totdat moment opslaan.

het volgende dus:

code:

Naam|Cijfer|EerstLetters
Geheugen 256SD|256|Geheugen
1024MB Geheugen|1024|NULL

Dan sorteer je dus eerst op EersteLetters dan op Cijfer dan op naam

[Verwijderd]

Eskimootje schreef op vrijdag 08 juni 2007 @ 15:21:
Je kan toch op het moment dat je de spullen insert met een regex de eerste reeks cijfers ophalen en daarna de tekst reeks totdat moment opslaan.

het volgende dus:

code:

Naam|Cijfer|EerstLetters
Geheugen 256SD|256|Geheugen
1024MB Geheugen|1024|NULL

Dan sorteer je dus eerst op EersteLetters dan op Cijfer dan op naam

De laatste kolom kan je weglaten, je kan gewoon sorteren op cijfer, naam.

[Eskimootje]

Als je dat doet, zul je dus de volgende dingen verkeerd sorteren:

HD 200 GB
Geheugen 256 SD
HD 350 GB
Geheugen 512 MB


wat moet zijn:

Geheugen 256 SD
Geheugen 512 MB SD
HD 200 GB
HD 350 GB

[.oisyn]

Schutje285: Dat schiet niet op

A2, A4, A34, B8, B28, B34

sorteert op jouw manier:

A2, A4, B8, B28, B34, A34

_{.edit: wat Eskimootje zegt dus}

[ Voor 16% gewijzigd door .oisyn op 08-06-2007 16:42 ]

[drm]

Voeg een tabel toe met properties. Elke productcategorie heeft een n:m relatie met properties die aangeeft wat de properties zijn die toepasbaar zijn voor een product dat in die categorie staat. Dan een n:m relatie tussen product en property waarin de waarde van de property staat. Tot slot een flag per property waar in staat of je er op kunt sorteren of niet. Het selecteren van resultaten gesorteerd op een property levert dus een 1:1 inner join met de koppeltabel tussen property en product op, dus kies vooral handige indexes.

Ik ben benieuwd wat dat voor performanceresultaten heeft, maar zo zou ik het in eerste instantie doen.

[Verwijderd]

Je kunt met reguliere expressies kijken of het om MB of GB gaat, de getallen er vervolgens uithalen, GBx1024 en je vars op grootte sorteren. Maar dat werkt alleen in je eigen voorbeelden. Met productnamen waarin (extra) getallen zitten zoals A1 512 MB DDR 2 etc wordt het wat lastiger.

[ Voor 104% gewijzigd door Verwijderd op 09-06-2007 01:37 ]

[drm]

funksngrooves:
Je kunt met reguliere expressies kijken of het om MB of GB gaat, de getallen er vervolgens uithalen, (...)

Dat zijn m.i. dingen die je eenmalig moet doen, niet elke keer dat er op gezocht wordt weer opnieuw ...

[Noork]

Eskimootje schreef op vrijdag 08 juni 2007 @ 16:40:
Als je dat doet, zul je dus de volgende dingen verkeerd sorteren:

HD 200 GB
Geheugen 256 SD
HD 350 GB
Geheugen 512 MB


wat moet zijn:

Geheugen 256 SD
Geheugen 512 MB SD
HD 200 GB
HD 350 GB

Ik neem aan dat je dit wel oplost door deze in sub-categorieen te plaatsen. Ik zie het probleem nog steeds niet, of ligt het aan mij.

[The Fox NL]

Ik heb dit probleem ook wel eens gehad op mijn stage. Mijn oplossing was de volgende:

Maak een Stored procedure die een string teruggeeft en de te sorteren string en NumberOfZeros als parameters heeft. Deze procedure zoekt de string door naar getallen en pad deze met nullen (met NumberOfZeros als maximale lengte). De gepadde string geeft ie dan weer terug.
input:
Memorycard 256MB
Memorycard 512MB
Memorycard 1024MB
Memorycard 2048MB
output (met number of zeroes 5):
Memorycard 00256MB
Memorycard 00512MB
Memorycard 01024MB
Memorycard 02048MB

Om te sorteren gebruik je de procedure als volgt:

select pi.* from productinformatie pi order by NaturalSort(pi.omschrijving, 5);

Nu is dit natuurlijk een algemene procedure voor Natural Sort op strings en wordt er geen rekening gehouden met eenheden. Wil je fatsoenlijke sortering op eenheden dan zal je toch met metadata moeten gaan werken.

[Verwijderd]

offtopic:
Excuses voor mijn offtopic, ik heb (denk ik) niets toe te voegen aan dit topic.
Ik ben zeer benieuwd naar meta-data.
Ik heb vaak gelezen over meta-data aan files, of kan een DB deze data ookbevatten?
Hamvragen zijn denk ik ook hoe kan je meta-data van informatiebronnen als files uitlezen en manipuleren?

[drm]

fl!pulI:

offtopic:
Excuses voor mijn offtopic, ik heb (denk ik) niets toe te voegen aan dit topic.
Ik ben zeer benieuwd naar meta-data.
Ik heb vaak gelezen over meta-data aan files, of kan een DB deze data ookbevatten?
Hamvragen zijn denk ik ook hoe kan je meta-data van informatiebronnen als files uitlezen en manipuleren?

Mijn post bevat een omschrijving van een vorm van metadata.

[curry684]

drm schreef op zaterdag 09 juni 2007 @ 01:04:
Voeg een tabel toe met properties. Elke productcategorie heeft een n:m relatie met properties die aangeeft wat de properties zijn die toepasbaar zijn voor een product dat in die categorie staat. Dan een n:m relatie tussen product en property waarin de waarde van de property staat. Tot slot een flag per property waar in staat of je er op kunt sorteren of niet. Het selecteren van resultaten gesorteerd op een property levert dus een 1:1 inner join met de koppeltabel tussen property en product op, dus kies vooral handige indexes.

Ik ben benieuwd wat dat voor performanceresultaten heeft, maar zo zou ik het in eerste instantie doen.

Metadata dus Ik hield het mysterie nog een beetje hoog