⚠ In case you've missed it, we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This is an archived forum. ⚠

  •     

profile picture

Changing alpha sort to numeric sort



JJBladester

JJBladester
  • profile picture
  • Member

Posted 19 September 2013 - 21:35 PM

Hello,

 

I just created my first grocery CRUD project to track IP addresses, host names, and location of network equipment where I work.  I found that IP addresses are sorted alphabetically and not numerically.  Can this behavior be changed?

 

For example,

 

172.20.1.158

172.20.1.159

172.20.1.16     <-------This should not be after 172.20.1.159

...

 

I would like all other columns to retain the default sort (alpha) but just this one column should be handled as if the numbers in the IP addresses were numbers and not chars.

 

Thanks for any pointers.

 


briggers

briggers
  • profile picture
  • Member

Posted 20 September 2013 - 08:32 AM

Hi,

This is not really a GC problem. A dotted decimal IP is not a number by definition because it has 3 decimal points in it; it is a string so you can only do a string sort on it.

 

However you could convert it to a decimal number on input, store it in the database as a decimal number, sort it and then convert it back to dot decimal for output using "before insert callback" on input and "callback column" on output.

 

See:

http://stackoverflow.com/questions/12130464/ip-address-conversion-to-decimal-and-vice-versa

for some example conversion scripts


JJBladester

JJBladester
  • profile picture
  • Member

Posted 25 September 2013 - 16:40 PM

Hi,

This is not really a GC problem. A dotted decimal IP is not a number by definition because it has 3 decimal points in it; it is a string so you can only do a string sort on it.

 

However you could convert it to a decimal number on input, store it in the database as a decimal number, sort it and then convert it back to dot decimal for output using "before insert callback" on input and "callback column" on output.

 

See:

http://stackoverflow.com/questions/12130464/ip-address-conversion-to-decimal-and-vice-versa

for some example conversion scripts

 

I am storing the IP addresses as varchars, not strings.  I found a MySQL function called INET_ATON that can be used in conjunction with ORDER BY in a SQL select statement:

mysql> select host_address from ipaddresses order by INET_ATON(host_address);

When I run the above query against the MySQL database, I get a properly-ordered list of IP addresses.

 

I need to somehow integrate this MySQL code into Grocery CRUD so when I click the Host Address column header, it uses this sort instead of its own sort.


briggers

briggers
  • profile picture
  • Member

Posted 25 September 2013 - 18:49 PM

You are right, that is another way of doing it.

 

The disadvantage of that is that you have to extend the basic CG database model with your own which includes this function. I think it's just easier to use a call back but make your own choice.

 

See set_model for more details

 

PS. the fieldtype varchars is a type of string field


davidoster

davidoster
  • profile picture
  • Member

Posted 26 September 2013 - 08:49 AM

Maybe on a future version we could have built in API for various sorts like the one you mention [member=jjbladester].

This needs to be theme independent and some times this might be the problem with quite a few interesting features of Grocery CRUD library as [member=web-johnny] might add to this.

We need to have abstract API so it binds nicely and easily with any theme we use and this believe me it is not as simple as it sounds!