I have a problem with displaying Chart of Accounts of my client. They used decimal notation for the code such as, 1.1.1, 1.1.2 … , 1.1.10, 1.1.11, 1.1.12 and so on sometimes the decimal happened in the middle, 2.2.1.1, 2.3.1.1, … 2.10.1.1, 2.11.1.1
Using default sort on CGridView will result in 1.1.10 comes after 1.1.2 before 1.1.2, also 2.10.1.1 comes after 2.1.1.1 before 2.2.1.1. It become unsorted…
In PHP I learn that natsort can be used to solve this problem, but I don’t know how to implement this using yii way in my gridview.
Assuming you are on MySQL, you might try this. Were the codes inserted by natural order? If so, an alternative might be to simply sort by the primary key
Yes, I am on MySql. Which one from the link to be tried? I just confused when reading it.
What I did was, to compose a CArrayDataProvider by first retrieved the data then sort it in php using natsort then compose the array. To me it was not so efficient. especially since I need to make one sql call just to create the order in addition to compose the order array result.
sort by primary key did not work since COA can be added in the future so it becomes invalid.
using order field will be the most easiest way to do but I do not want to put a lot of effort just for sorting the result ;(
I were referring to the second answer (the one with 41 pts, un der the “accepted” answer. The hyperlink anchor should’ve actually pointed you there). This is actually pretty much the definition of a lexicographic (i.e. “natural”) order being brought to MySQL.
Are you using active records at any point? Using the solution from SO, you could at least use a CSqlDataProvider.
The one with 41 pts is easiest but still not solved my problem. The thing is that the code field can be any length. For example, I have 1, 2, 3, 1.1, … 1.10, 1.11,1.1.1,1.1.2,… 1.1.10, 1.1.1.1
The level depth add the complication, probably i need to adapt the length() into something that calculate the number of "."(dots). Any help on how to do it on mysql?
"." is used since the number of dots will represent the level of account.
There is actually a faster solution: You can sort by the output of INET_ATON(). You just need to make sure that no number in the version string exceeds 255
