Django Query - Prefetch filter based on query data?

Question

Im trying to filter a prefetch based on its parent query object, is this possible? hopefully the below example explains.

live link type from SiteData and circuit type from Circuits use the same child model.

so for example if the live link type is 'Fibre' I want to prefetch only the circuit that has the circuit_type of 'Fibre'. each site can have many circuits but I only want the live one at this moment

This is the query:

conn_stats = SiteData.objects.all()
    .exclude(site_type__site_type='Factory') \
    .exclude(site_type__site_type='Data Centre') \
    .Prefetch(
        'circuits_set',
        queryset=Circuits.objects.filter(SiteData.objects.live_link_type.circuit_type)
        )
)

these are the models:

class CircuitTypes(models.Model):
    circuit_type = models.CharField(max_length=50)
    monitor_priority = models.IntegerField(verbose_name="Monitoring Priority", blank=True, null=True)
    class Meta:
        verbose_name = "Circuit Types"
        verbose_name_plural = "Circuit Types"
    def __str__(self):
        return self.circuit_type

class SiteData(models.Model):
    location = models.CharField(max_length=50)
    site_type = models.ForeignKey(SiteTypes, verbose_name="Site Type", \
                on_delete=models.PROTECT)
    is_live = models.BooleanField(default=False, verbose_name="Is this a live site?")
    live_link_type = models.ForeignKey(CircuitTypes, verbose_name="Link Type", \
                on_delete=models.PROTECT, default=1)
    live_link_preference = models.CharField(max_length=200, blank=True, null=True)
    live_link_query_timestamp = models.DateTimeField(auto_now_add=True, blank=True, null=True)

class Circuits(models.Model):
    site_data = models.ForeignKey(SiteData, verbose_name="Site", on_delete=models.PROTECT)
    order_no = models.CharField(max_length=200, verbose_name="Order No")
    expected_install_date = models.DateField()
    install_date = models.DateField(blank=True, null=True)
    circuit_type = models.ForeignKey(CircuitTypes, verbose_name="Circuit Type", on_delete=models.PROTECT)
    ...

dukebody dukebody · Accepted Answer · 2018-01-21T12:29:59

As far as I know you cannot do what you ask with a single queryset.

If the number of CircuitTypes is small, you can consider constructing a queryset for each type of circuit:

circuit_types = CircuitType.object.all()
for circuit_type in circuit_types:
    prefetch = Prefetch('circuits_set',
                        queryset=Circuits.objects.filter(
                            circuit_type=circuit_type)
    conn_stats = SiteData.objects.filter(live_link_type=circuit_type) \
        .exclude(site_type__site_type='Factory') \
        .exclude(site_type__site_type='Data Centre') \
        .prefetch_related(prefetch)

If there are many circuit types and this is prohibitive then you can perhaps add a boolean active field to Circuits and update its value when changing each SiteData.live_link_type. That way you would be able to query only for active Circuits directly:

prefetch = Prefetch('circuits_set',
                    queryset=Circuits.objects.filter(active=True)
conn_stats = SiteData.objects.all() \
    .exclude(site_type__site_type='Factory') \
    .exclude(site_type__site_type='Data Centre') \
    .prefetch_related(prefetch)

Django Query - Prefetch filter based on query data?

1 Answers