Extend the use of natural ordering to additional models

[JohannesKreuzer]

NetBox version

v3.4.1

Feature type

Data model extension

Proposed functionality

Extend Virtualisation Cluster model to use natualsorting by the cluster name.

Use case

Better sorting of the clusters without padding the numbers in the name.

Database changes

No response

External dependencies

No response

[jeremystretch]

IMO it doesn't make sense to do this only for clusters as a discrete goal. For instance, #10644 proposes also implementing natural ordering for circuits.

We use natural ordering for a few models in NetBox currently; namely sites, racks, devices, device components, VMs, and VM interfaces. This is accomplished by including a "hidden" _name field which stores a naturalized representation of the object's name suitable for database ordering.

While I'm not opposed to enabling natural ordering on other models, if we decide to do so, we should:

1. Re-evaluate the current implementation to determine if a better approach exists
2. Identify all suitable models and implement the change on all
3. Ensure that the implementation is scalable (e.g. via a mixin class vs. explicit field definitions as we do currently)

[kkthxbye-code]

• Re-evaluate the current implementation to determine if a better approach exists

Maybe we can use postgres collation as there is support for natural ordering.

We would have to create the collation in a migration with raw sql I assume, like:

CREATE COLLATION natural_sort (provider = icu, locale = 'en@colNumeric=yes');

And then the collation can be specified on the field like:

name = models.CharField(
    max_length=100,
    db_collation='numeric'
)

It also seems possible to specify it at query time.

[github-actions[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. NetBox is governed by a small group of core maintainers which means not all opened issues may receive direct feedback. Do not attempt to circumvent this process by "bumping" the issue; doing so will result in its immediate closure and you may be barred from participating in any future discussions. Please see our contributing guide.

[jeremystretch]

Collation definitely seems like the path forward, if it proves feasible. I recall going down this road a bit in the past but ran into limitations on older PostgreSQL versions. Django 4.2 drops support for PostgreSQL 11 (see #12237), so maybe that will free us up some?