Interface Ordering doesn't order SubInterfaces correctly

[zevlag]

Issue type: bug report

Python version: 2.7.9
NetBox version: v2.0-beta2

I have created VLAN subInterfaces on my Cisco IOS-XE gear and represent them in NetBox. They don't sort as expected. I use the Alphabetic sort order.

Here is an example:

GigabitEthernet0/0/0		Not connected 
GigabitEthernet0/0/1		Not connected 
GigabitEthernet0/0/2 		Not connected 
GigabitEthernet0/0/3		Brocade NetIron CER 2024C	ethernet 1/24   
GigabitEthernet0		Not connected 
GigabitEthernet0/0/2.2		Virtual interface
GigabitEthernet0/0/0.3 		Virtual interface
GigabitEthernet0/0/2.4		Virtual interface
GigabitEthernet0/0/2.9		Virtual interface
GigabitEthernet0/0/2.10		Virtual interface
GigabitEthernet0/0/2.25		Virtual interface
GigabitEthernet0/0/2.26		Virtual interface
GigabitEthernet0/0/2.27		Virtual interface
GigabitEthernet0/0/0.110		Virtual interface
GigabitEthernet0/0/0.117		Virtual interface
GigabitEthernet0/0/1.117		Virtual interface
GigabitEthernet0/0/0.120		Virtual interface
GigabitEthernet0/0/0.130		Virtual interface
GigabitEthernet0/0/0.200		Virtual interface
GigabitEthernet0/0/0.210		Virtual interface
GigabitEthernet0/0/0.220		Virtual interface
Loopback0		Virtual interface
Loopback1 		Virtual interface
Loopback100 		Virtual interface
Loopback999 		Virtual interface
Tunnel1 		Virtual interface
Tunnel10		Virtual interface
Tunnel11		Virtual interface
Tunnel12		Virtual interface
Tunnel13		Virtual interface
Tunnel100 		Virtual interface
Tunnel110 		Virtual interface
Tunnel4030 		Virtual interface

My specific concerns are:

• GigabitEthernet0/0/2.2 is not before GigabitEthernet0/0/3
• GigabitEthernet0/0/1.117 is before GigabitEthernet0/0/0.120
• GigabitEthernet0 is not before GigabitEthernet0/0/0

There are two changes that I believe would solve these problems without negatively affecting other cases.

1. Modify the query to seperate on periods and comma's. ie ':([0-9]+)$' becomes '[.:]([0-9]+)$'
   https://github.com/digitalocean/netbox/blob/v2-develop/netbox/dcim/models.py#L809

--- a/netbox/dcim/models.py
+++ b/netbox/dcim/models.py
@@ -806,10 +806,10 @@ class InterfaceManager(models.Manager):
         }[method]
         return queryset.extra(select={
             '_name': "SUBSTRING({} FROM '^([^0-9]+)')".format(sql_col),
-            '_slot': "CAST(SUBSTRING({} FROM '([0-9]+)\/[0-9]+\/[0-9]+(:[0-9]+)?$') AS integer)".format(sql_col),
-            '_subslot': "CAST(SUBSTRING({} FROM '([0-9]+)\/[0-9]+(:[0-9]+)?$') AS integer)".format(sql_col),
-            '_position': "CAST(SUBSTRING({} FROM '([0-9]+)(:[0-9]+)?$') AS integer)".format(sql_col),
-            '_channel': "CAST(SUBSTRING({} FROM ':([0-9]+)$') AS integer)".format(sql_col),
+            '_slot': "CAST(SUBSTRING({} FROM '([0-9]+)\/[0-9]+\/[0-9]+([.:][0-9]+)?$') AS integer)".format(sql_col),
+            '_subslot': "CAST(SUBSTRING({} FROM '([0-9]+)\/[0-9]+([.:][0-9]+)?$') AS integer)".format(sql_col),
+            '_position': "CAST(SUBSTRING({} FROM '([0-9]+)([.:][0-9]+)?$') AS integer)".format(sql_col),
+            '_channel': "CAST(SUBSTRING({} FROM '[.:]([0-9]+)$') AS integer)".format(sql_col),
         }).order_by(*ordering)

2. Modify the query to order by nulls first for each component. I don't know enough python/django to do this, but the feature is there in django and postgres.

[InsaneSplash]

I have the same representation as above and would be great to group them together.

[jeremystretch]

The above fixes most of the ordering problems. It doesn't address e.g. GigabitEthernet0 being ordered after GigabitEthernet0/0/0, but that's likely going to require rewriting the entire ordering scheme. It looks like Django 1.11 might make this easier than in the past, but it's not a priority right now.