Natural Sort In Cgridview

Hi All,

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,,, …,

Using default sort on CGridView will result in 1.1.10 comes after 1.1.2 before 1.1.2, also comes after before 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.

Thank you in advance.


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,

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.

Ok, this is a way to reliably count the number of dots. But I think it’ll bypass all indices:

LENGTH(`versionfield`) - LENGTH(REPLACE(`versionfield`, '.', ''))

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 ::)

Hi Da:Sourcerer,

Thank you for your continuous and quick response.

I did not explain in clearly in the beginning. Your INET_ATON() is actually great since it already sorting the 1.1.10 after 1.1.9 not after 1.1.1

The thing is I am still not solving my problem. Now the sorting become










What I need is













Thank you a lot in advance.

Probably field sortOrder is the most easiet way in solving this. But, I do not like the user to maintain the order …;(