Access prefetch_related objects

Using Django Using the ORM
1 
latest_update = BillHistory.objects.filter(
            bill=OuterRef('id')).order_by('-occurred_datetime')
        user_bills = Bill.objects.only(
            "bill_number", "short_title").filter(session='2023-2024').filter(
                Q(users__archived=False)
                & Q(users__user=user)).annotate(
                    priority=F('users__billuserpriority__priority'),
                    position=F('users__billuserposition__position'),
                    latest_update_on=Max('histories__occurred_datetime'),
                    committee_names=Subquery(
                        latest_update.values('committee_names__name')[:1]),
                    latest_update_status=Subquery(
                        latest_update.values('status')[:1])).prefetch_related(
                            'users__clients__client',
                            'users__pillars__pillar').order_by(
                                Func(F('bill_number'), function='alphanum'))

prefetch_related works wonders here. What is the more performant query to access the prefetched objects? At the moment, I am accessing a client object like so in the template: bill.users.all → users.clients.all → client. Although I am able to filter first by user and then by clients for that user, this is resulting in duplicate and similar queries. Thank you.

2

Which query is duplicated exactly?

Did you try the Prefetch object with its to_attr= option to specify an attribute on the QuerySet model to store the prefetched object in a list in?

If you use Prefetch(..., to_attr="clients"), you can access them via bill.clients in your template.

# This is your code snippet formatted with Black (https://black.readthedocs.io)
# This way it's easier for others to see what your queryset actually does.

latest_update = BillHistory.objects.filter(bill=OuterRef("id")).order_by(
    "-occurred_datetime"
)

user_bills = (
    Bill.objects.only("bill_number", "short_title")
    .filter(session="2023-2024")
    .filter(Q(users__archived=False) & Q(users__user=user))
    .annotate(
        priority=F("users__billuserpriority__priority"),
        position=F("users__billuserposition__position"),
        latest_update_on=Max("histories__occurred_datetime"),
        committee_names=Subquery(latest_update.values("committee_names__name")[:1]),
        latest_update_status=Subquery(latest_update.values("status")[:1]),
    )
    .prefetch_related("users__clients__client", "users__pillars__pillar")
    .order_by(Func(F("bill_number"), function="alphanum"))
)
3

Thank you for your response.

I use yapf for formatting. Black works well too.

I have tried using Prefetch with the to_attr attribute set.

.prefetch_related(Prefetch("users__clients", queryset=BillUserClient.objects.filter(billuser=user), to_attr="bill_clients"))

I’d like to access clients attached to a bill by an authenticated user, in my template passing a Bill instance to the context. I’d like to do this without looping through all users and all clients. I am trying to work out why I can’t seem to use bill.bill_clients to access the clients attached to the bill by a particular authenticated user using prefetch_related with Prefetch.

There are through-tables between Bill, User, and Client, without any explicit many-to-many field set in the Bill model or the Client model (see below)

class BillUser(TimestampedModel):
    user = models.ForeignKey(settings.AUTH_USER_MODEL,
                             related_name='bills',
                             on_delete=models.RESTRICT)
    bill = models.ForeignKey(Bill,
                             related_name='users',
                             on_delete=models.RESTRICT)

    archived = models.BooleanField("Archived", default=False)

    class Meta:
        # unique_together = ('user', 'bill')
        constraints = [
            models.UniqueConstraint(fields=['user', 'bill'],
                                    name='bill_user_unique')
        ]
        indexes = [
            models.Index(fields=['user', 'bill'], name='bill_user_idx'),
            models.Index(fields=['bill'], name='bill_user_bill_idx'),
            models.Index(fields=['user'], name='bill_user_user_idx'),
            models.Index(fields=['archived'], name='bill_user_archived_idx'),
        ]

    def __str__(self):
        return f"{self.user} - {self.bill} - archived: {self.archived}"

class BillUserClient(TimestampedModel):
    """
    Many-to-Many through table for BillUser and Client Models
    """
    billuser = models.ForeignKey(BillUser,
                                 related_name='clients',
                                 on_delete=models.RESTRICT)
    client = models.ForeignKey(KslegbtrClient,
                               related_name='billusers',
                               on_delete=models.RESTRICT)

    class Meta:
        # unique_together = ('billuser', 'client')
        constraints = [
            models.UniqueConstraint(fields=['billuser', 'client'],
                                    name='billuser_client_unique')
        ]
        ordering = ['client']

    def __str__(self):
        return f"{self.billuser} - {self.client.short_name}"