Custom name for key in queryset

Question

I have an Invoice query set. The invoice has an appointment object, that has a patient object that has a name attribute. I am trying to annotate so I can get the name of the patient as well. The rest of the fields return data okay. But the patient name doesn't. I have tried - F("appointment__patient__name") and also "appointment__patient__name". What am I missing here ?

def get_corporate_invoices(
    start_date: date, end_date: date, corporate_uuid: str
) -> QuerySet[Invoice]:
    return (
        Invoice.objects.annotate(
            entry_date=F("appointment_date"),
            payment_method=Value("--", output_field=models.TextField()),
            debit_amount=Round("amount", precision=2),
            running_balance=Value(0.00, output_field=models.FloatField()),
            patient_name=F("appointment__patient__name"),
        )
        .filter(
            corporate_uuid=corporate_uuid,
            appointment_date__gte=start_date,
            appointment_date__lte=end_date,
            isdeleted=False,
            status="Cleared",
            corporate_uuid__isnull=False,
        )
        .order_by("appointment_date", "concierge_rct_id")
        .values(
            "entry_date",
            "payment_method",
            "debit_amount",
            "running_balance",
            "patient_name"

        )
    )

models.py

class Invoice(models.Model):

    transaction = models.ForeignKey(
        Transaction,
        on_delete=models.SET_NULL,
        related_name="transaction_invoice",
        blank=True,
        null=True,
    )
    status = models.CharField(
        max_length=25, choices=invoice_status_choices, default="Pending"
    )

    concierge_reference = models.TextField(blank=False, null=False)
    concierge_rct_id = models.IntegerField(blank=False, null=True, unique=True)
    appointment = models.JSONField(blank=False, null=True)
    appointment_date = models.DateField(blank=False, null=True)
    amount = models.FloatField(null=True, blank=True, default=0.00)
    payment_mode = models.JSONField(blank=False, null=True)

`appointment` is a JSONField, so the way you're current trying to aggregate it won't work. See this answer for an approach that should work: https://stackoverflow.com/questions/55058554/django-annotate-count-in-jsonfield-with-postgres — solarissmoke, Mar 24 '22 at 04:44
Why do you make it a `JSONField` if the data should (always) contain a `patient_name`? In that case it is "stronger"/"richer" to define a field itself for that. — Willem Van Onsem, Mar 24 '22 at 07:19

score 0 · Answer 1 · answered Mar 24 '22 at 07:45

0

This is what worked:

     patient_name=Func(
                    F('appointment'), Value('patient'), Value('name'),
                    function='jsonb_extract_path_text'),
                        )

answered Mar 24 '22 at 07:45

siderra

131
1
11

Custom name for key in queryset

1 Answers1