Replace annotate_depth() method on Prefix manager #3684

New Issue

adam · 2025-12-29T18:30:35+01:00

adam commented

2025-12-29 18:30:35 +01:00

Originally created by @jeremystretch on GitHub (May 13, 2020).

Originally assigned to: @jeremystretch on GitHub.

Proposed Changes

Currently, the manager for the Prefix model provides an annotate_depth() method which annotates the relative depth of each prefix within a queryset. This is used for rendering a hierarchy when viewing the list of prefixes in the NetBox UI. The proposal here is to replace this convoluted manager method with a model method.

Justification

The current approach relies on directly assigning the depth attribute to each object in the query. This is both inefficient and subject to being lost when successive operations are performed on the queryset.

Originally created by @jeremystretch on GitHub (May 13, 2020). Originally assigned to: @jeremystretch on GitHub. ### Proposed Changes Currently, the manager for the Prefix model provides an `annotate_depth()` method which annotates the relative depth of each prefix within a queryset. This is used for rendering a hierarchy when viewing the list of prefixes in the NetBox UI. The proposal here is to replace this convoluted manager method with a model method. ### Justification The current approach relies on directly assigning the `depth` attribute to each object in the query. This is both inefficient and subject to being lost when successive operations are performed on the queryset.

adam added the status: under review type: housekeeping labels 2025-12-29 18:30:35 +01:00

adam closed this issue

2025-12-29 18:30:35 +01:00

adam commented

2025-12-29 18:30:36 +01:00

@jeremystretch commented on GitHub (Jul 28, 2020):

I've been experimenting with different approaches to this, and the best I've found thus far is to use .extra() with raw SQL to annotate depth and children on the Prefix QuerySet:

    def annotate_depth(self):
        return self.extra(
            select={
                'depth': 'SELECT COUNT(U0."prefix") AS "c" '
                         'FROM "ipam_prefix" U0 '
                         'WHERE (U0."prefix" >> "ipam_prefix"."prefix" '
                         'AND COALESCE(U0."vrf_id", 0) = COALESCE("ipam_prefix"."vrf_id", 0))',
                'children': 'SELECT COUNT(U1."prefix") AS "c" '
                            'FROM "ipam_prefix" U1 '
                            'WHERE (U1."prefix" << "ipam_prefix"."prefix" '
                            'AND COALESCE(U1."vrf_id", 0) = COALESCE("ipam_prefix"."vrf_id", 0))',
            }
        )

Performance seems adequate, and in any case is a great improvement over the current implementation at scale. While we could mostly replicate this using the native ORM, it becomes far less obvious to the reader what is happening, and we'd need to figure out how to properly match on NULL VRF assignments (handled above by casting to an integer).

Unfortunately, this approach precludes us from filtering prefixes on either annotation at query time: They can be used to illustrate the hierarchy by indenting child prefixes beneath their parents in the prefix list, but this would no longer support the depth filter that we use today for collapsing the hierarchy to show only top-level prefixes in a query. Happy to take feedback on whether it would be acceptable to remove that ability.

@jeremystretch commented on GitHub (Jul 28, 2020): I've been experimenting with different approaches to this, and the best I've found thus far is to use `.extra()` with raw SQL to annotate `depth` and `children` on the Prefix QuerySet: ```python def annotate_depth(self): return self.extra( select={ 'depth': 'SELECT COUNT(U0."prefix") AS "c" ' 'FROM "ipam_prefix" U0 ' 'WHERE (U0."prefix" >> "ipam_prefix"."prefix" ' 'AND COALESCE(U0."vrf_id", 0) = COALESCE("ipam_prefix"."vrf_id", 0))', 'children': 'SELECT COUNT(U1."prefix") AS "c" ' 'FROM "ipam_prefix" U1 ' 'WHERE (U1."prefix" << "ipam_prefix"."prefix" ' 'AND COALESCE(U1."vrf_id", 0) = COALESCE("ipam_prefix"."vrf_id", 0))', } ) ``` Performance seems adequate, and in any case is a great improvement over the current implementation at scale. While we _could_ mostly replicate this using the native ORM, it becomes far less obvious to the reader what is happening, and we'd need to figure out how to properly match on NULL VRF assignments (handled above by casting to an integer). Unfortunately, this approach precludes us from filtering prefixes on either annotation at query time: They can be used to illustrate the hierarchy by indenting child prefixes beneath their parents in the prefix list, but this would no longer support the `depth` filter that we use today for collapsing the hierarchy to show only top-level prefixes in a query. Happy to take feedback on whether it would be acceptable to remove that ability.

adam referenced this issue

2025-12-29 22:22:39 +01:00

[PR #3684] [MERGED] Docs: Remove obsolete P3P policy #12614

Sign in to join this conversation.

Branches Tags

main

update-changelog-comments-docs

feature-removal-issue-type

20911-dropdown

20239-plugin-menu-classes-mutable-state

21097-graphql-id-lookups

feature

fix_module_substitution

20923-dcim-templates

20044-elevation-stuck-lightmode

feature-ip-prefix-link

v4.5-beta1-release

20068-import-moduletype-attrs

20766-fix-german-translation-code-literals

20378-del-script

7604-filter-modifiers-v3

circuit-swap

12318-case-insensitive-uniqueness

20637-improve-device-q-filter

20660-script-load

19724-graphql

20614-update-ruff

14884-script

02496-max-page

19720-macaddress-interface-generic-relation

19408-circuit-terminations-export-templates

20203-openapi-check

fix-19669-api-image-download

7604-filter-modifiers

19275-fixes-interface-bulk-edit

fix-17794-get_field_value_return_list

11507-show-aggregate-and-rir-on-api

9583-add_column_specific_search_field_to_tables

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: starred/netbox#3684