Django, how to create ForeignKey field when .annotate?

Question

I wanted to create a ForeignKey field in Django using .annotate, but I couldn't find any option for it, maybe it doesn't exist. I just want to LEFT JOIN a specific model with a specific condition. But now I have to do it like this:

queyrset = Invoice.objects.annotate(provided_amount=Subquery(InvoicePayment.objects.filter(invoice_id=OuterRef("id"), date=timezone.now().date()).values("provided_amount")))

queryset.first().provided_amount

But I want it like this:

queryset = Invoice.objects.annotate(invoice_payment=...)

queryset.first().invoice_payment.provided_amount

I could do it using property, but I don't want to do it like that, I would like to do everything in one query.

Maybe there is a library for this?

Django - 4.2.4 Python - 3.10

I tried creating a SQL VIEW and binding it to the model with managed = False, but it's not very convenient to do that every time.

Answer 1

Please don't use related_name='+': it means querying in reverse is now a lot harder. We can name the relation payments instead:

class InvoicePayment(models.Model):
    invoice = models.ForeignKey(
        Invoice, on_delete=models.CASCADE, related_name='payments'
    )
    date = models.DateField()
    provided_amount = models.DecimalField(
        max_digits=64, decimal_places=24, null=True
    )

    class Meta:
        unique_together = ['invoice', 'date']


class Invoice(models.Model):
    pass

Then, we can work with a FilteredRelation ^[Django-doc]:

from django.db.models import FilteredRelation, Q

qs = Invoice.objects.annotate(
    todays_payment=FilteredRelation(
        'payments', condition=Q(payments__date=timezone.now().date())
    ),
).select_related('todays_payment')

and for example:

qs.first().todays_payment.provided_amount