Django - Query using .0. does not work whilst using .first(). creates duplicate queries

Question

I have a need to only get one record from a queryset_set. (as it only returns 1. so I have used .0 or .first() normally.

However at the moment when I use .0 I do not get any data. when I use .first() I get duplicated queries (despite the prefetch)

I have tried to use a sub query but from the samples ive seen I am unable to interpret it to suit my needs.

The query which generates duplicate when using first but nothing when using 0

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')
                            ) \
                        ) \

in the template:

{% for item in circuits  %}
    {{ item.circuit.devicecircuitsubnets_set.0.subnet }}{{ item.circuit.devicecircuitsubnets_set.0.mask }}
    ...

models:

class Circuit(models.Model):
    name = models.CharField(max_length=200, verbose_name="Name")
    order_no = models.CharField(max_length=200, verbose_name="Order No")
    ref_no = models.CharField(max_length=200, verbose_name="Reference No")
    expected_install_date = models.DateField()
    install_date = models.DateField(blank=True, null=True)
    ...

class SiteCircuits(models.Model):
    site = models.ForeignKey(Site, on_delete=models.CASCADE)
    circuit = models.ForeignKey(Circuit, on_delete=models.CASCADE)
    site_count = models.IntegerField(verbose_name="How many sites circuit is used at?", blank=True, null=True)
    active_link = models.BooleanField(default=False, verbose_name="Active Link?")

class Device(models.Model):
    site = models.ForeignKey(Site, verbose_name="Site device belongs to", on_delete=models.PROTECT)
    hostname = models.CharField(max_length=200)

class Subnet(models.Model):     
    subnet = models.GenericIPAddressField(protocol='IPv4', \
                                        verbose_name="Subnet", blank=True, null=True)
    mask = models.CharField(max_length=4, verbose_name="Mask", \

class DeviceCircuitSubnets(models.Model):
    device = models.ForeignKey(Device, on_delete=models.CASCADE)
    circuit = models.ForeignKey(Circuit, on_delete=models.CASCADE, blank=True, null=True)
    subnet = models.ForeignKey(Subnet, on_delete=models.CASCADE)

sample output in console:

>>> circuits[100].circuit.devicecircuitsubnets_set.first().subnet.subnet
'10.10.10.4'
>>> circuits[100].circuit.devicecircuitsubnets_set.all()[0].subnet.subnet
'10.10.10.4'
>>>

Answer 1

EDIT: I'm not sure why your query is failing, but the docs say you need to be careful in the order in which prefetch is called. My next guess would be to try removing all prefetching apart from that which is absolutely necessary. Sorry it's not expert advice, I'm just muddling along myself. Also if you are using Django 1.11 onwards, you could re-write the query as below, as you are only interested in the first subnet/mask object per SiteCircuit.

from django.db.models import Subquery, OuterRef

subnet = Subquery(
    DeviceCircuitSubnets.objects.filter(circuit_id=OuterRef(
        'circuit_id')).values('subnet__subnet')[:1])
mask = Subquery(
    DeviceCircuitSubnets.objects.filter(circuit_id=OuterRef(
        'circuit_id')).values('subnet__mask')[:1])

circuits = SiteCircuits.objects.filter(
    circuit__decommissioned=False
).annotate(
    circuit_subnet=subnet,
    cicuit_mask=mask
).select_related(
    'site'
).select_related(
    'circuit'
)

You could then access this by:

{% for item in circuits  %}
    {{ item.circuit_subnet }}{{ item.circuit_mask }}
...

You shouldn't need to call prefetch_related on relationships which aren't many-to-many or one-to-many. For many-to-one and one-to-one relationships you should use select_related instead. E.g. you should use .select_related('circuit__circuit_type') rather than .prefetch_related('circuit__circuit_type') assuming that circuit_type is not a many to many field.

== From OP == Debug toolbar output:

SELECT "config_devicecircuitsubnets"."id", "config_devicecircuitsubnets"."device_id", "config_devicecircuitsubnets"."circuit_id", "config_devicecircuitsubnets"."subnet_id", "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 (NOT ("circuits_circuit"."decommissioned" = 'True' AND "circuits_circuit"."decommissioned" IS NOT NULL) AND "config_devicecircuitsubnets"."circuit_id" = '339') ORDER BY "config_devicecircuitsubnets"."id" ASC LIMIT 1
  Duplicated 526 times.

this line is highlighted in debug toolbar:

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

Answer 2

I'm not very familiar with the internals, but I suspect that first() generates extra queries for similar reasons that filter() does. See this question for more info.

For the second part of your question about why does .0 not return any results - your models and code are so complicated that I can't understand them. You might get a better response if you provided a simpler example (including the models) which reproduced the problem.