Django - prefetch query not working on related mapping tables

Question

I am trying to prefetch and select related some foreign key models whilst traversing multiple mapping tables. however I am still stuck with 500+ queries, I managed to get it down from 1000, but it doesn't look like the subnet field is getting fetched.

I have tried the below:

circuits = SiteCircuits.objects.all() \
                            .exclude(circuit__decommissioned=True) \
                            .select_related('site') \
                            .select_related('circuit') \
                            .prefetch_related(
                                Prefetch(
                                    'circuit__devicecircuitsubnets_set',
                                    queryset=DeviceCircuitSubnets.objects.all() \
                                    .select_related('subnet')
                                    )
                             ) \
                            .prefetch_related('circuit__circuitnotes_set') \
                            .prefetch_related('circuit__circuit_type') \
                            .prefetch_related('circuit__circuitfile') \
                            .prefetch_related('circuit__service_provider') \
                            .prefetch_related('circuit__circuit_type')

and this way also:

circuits = SiteCircuits.objects.all() \
                            .exclude(circuit__decommissioned=True) \
                            .select_related('site') \
                            .select_related('circuit') \
                            .prefetch_related('circuit__devicecircuitsubnets_set') \
                            .prefetch_related('circuit__devicecircuitsubnets_set__subnet') \
                            .prefetch_related('circuit__circuitnotes_set') \
                            .prefetch_related('circuit__circuit_type') \
                            .prefetch_related('circuit__circuitfile') \
                            .prefetch_related('circuit__service_provider') \
                            .prefetch_related('circuit__circuit_type')

but I have 500+ queries of below, which I thought I would of got by selecting related on subnet:

SELECT "config_devicecircuitsubnets"."id", "config_devicecircuitsubnets"."device_id", "config_devicecircuitsubnets"."circuit_id", "config_devicecircuitsubnets"."subnet_id", "circuits_circuit"."id", "circuits_circuit"."name", "circuits_circuit"."order_no", "circuits_circuit"."ref_no", "circuits_circuit"."expected_install_date", "circuits_circuit"."install_date", "circuits_circuit"."circuit_type_id", "circuits_circuit"."preference", "circuits_circuit"."service_provider_id", "circuits_circuit"."username", "circuits_circuit"."password", "circuits_circuit"."tel_no", "circuits_circuit"."cost_per_month", "circuits_circuit"."contract_length", "circuits_circuit"."speed_down", "circuits_circuit"."speed_up", "circuits_circuit"."rssi", "circuits_circuit"."bearer", "circuits_circuit"."decommissioned", "config_subnet"."id", "config_subnet"."subnet", "config_subnet"."mask", "config_subnet"."subnet_type_id" FROM "config_devicecircuitsubnets" INNER JOIN "circuits_circuit" ON ("config_devicecircuitsubnets"."circuit_id" = "circuits_circuit"."id") INNER JOIN "config_subnet" ON ("config_devicecircuitsubnets"."subnet_id" = "config_subnet"."id") WHERE "config_devicecircuitsubnets"."circuit_id" = '1' ORDER BY "config_devicecircuitsubnets"."id" ASC LIMIT 1
  Duplicated 526 times.

Line error:

{{ item.circuit.devicecircuitsubnets_set.first.subnet }}{{ item.circuit.devicecircuitsubnets_set.first.mask }}

High level models:

-Site
-Circuit
-Device 
-Subnet
--SiteCircuits (mapping table)
--DeviceCircuitSubnets (mapping table)

EDIT:

ive attempted a subquery that didn't throw any errors but im not sure if its right or how to access the field?

subquery example:

circuit_subnet = DeviceCircuitSubnets.objects.filter(circuit=OuterRef('pk'))
circuits = SiteCircuits.objects.all() \
                        .exclude(circuit__decommissioned=True) \
                        .select_related('site') \
                        .select_related('circuit') \
                        .prefetch_related('circuit__devicecircuitsubnets_set') \
                        .prefetch_related('circuit__devicecircuitsubnets_set__subnet') \
                        .prefetch_related('circuit__circuitnotes_set') \
                        .prefetch_related('circuit__circuit_type') \
                        .prefetch_related('circuit__circuitfile') \
                        .prefetch_related('circuit__service_provider') \
                        .prefetch_related('circuit__circuit_type') \
                        .annotate(circuit__devicecircuitsubnets_set=Subquery(circuit_subnet.values('subnet')[:1]))

score 1 · Accepted Answer · answered Mar 20 '18 at 15:07

1

Using .first might be causing additional queries. Try using .0 instead.

{{ item.circuit.devicecircuitsubnets_set.first.subnet }}{{ item.circuit.devicecircuitsubnets_set.first.mask }}

If you only need to prefetch the first related item, then you might be able to use a subquery.

answered Mar 20 '18 at 15:07

Alasdair

298,606
55
578
516

1

changing to .0 worked, i;ve attempted a subquery and added it to the question also – AlexW Mar 20 '18 at 15:33
my actual query will only return 1 value, but I cant use a get with a prefetch can I? – AlexW Mar 20 '18 at 15:37
I don't understand your question. Perhaps [this answer](https://stackoverflow.com/questions/12973929/why-does-djangos-prefetch-related-only-work-with-all-and-not-filter) will help. – Alasdair Mar 20 '18 at 15:41

Django - prefetch query not working on related mapping tables

1 Answers1