How to add a ComputationField that sums up data from a field in a ForeignKey (via Related_name)

[smseidl]

I'm making some progress on django-slick-reporting, but have hit a snag. I'm trying to sum up a field that is not in the direct model, but is available via the Foreign Key (using related_name). I've gotten a result, but it doesn't seem to add up correctly. I haven't been able to figure out how to reference the specific field in the Foreign module. Can you provide any details?

Foreign Model:

class PaymentDetail(models.Model):
    """Partial Payments from a payment for each specific visit/payment amount"""
    parentPayment=models.ForeignKey(Payment, on_delete=models.CASCADE, null=False,verbose_name="Payment",related_name="payment_detail")
    visit = models.ForeignKey(Visit, on_delete=models.CASCADE, null=False,related_name="payment_detail",verbose_name="Visit")
    dtlAmount = models.DecimalField(max_digits=9,decimal_places=2,verbose_name="Detail/Sub Payment Amount",)

Report View:

class ReportPageView(ReportView):
    report_title = "Visits by Patient"
    report_model = Visit
    date_field = "date"
    group_by = "patient"
    excluded_fields =["documents",]


    columns = [
        "name",
        ComputationField.create(
            Count, "id",verbose_name="Count of Visits",name="sum__value"
        ),
        ComputationField.create(
            Sum, "charge", name="sum__charge", verbose_name="Total Charged $"
        ),
        #Sum up related payment amounts that are linked by the "payment_detail" related_name/Foreignkey
        ComputationField.create(
            Sum, "payment_detail", name="sum__paid", verbose_name="Total Paid $"
        ),        
    ]

Sorry for creating an issue for help, but I haven't found anything else.

[RamezIssac]

I think your best bet is to create a db view (materialized or not) -> have a Django model for it -> generate a report from that model.

The db view can look something like this:

select date, patient_id, charge, 0 as paid from visits
union all 
select date patient_id, 0 as charge, dtlAmount as paid from payment details 

Other then that, you may can do it via custom ComputationField, but wont be performative.
Generally, Slick reporting expects the data it works on to be on the report_model

[smseidl]

I was looking at the custom ComputationField but couldn't find a good example of how to do it. I got as far as finding the queryset, but it wasn't pre-filtered so wasn't quite show how to iterate through it.

I did find/fix my issues. I'm not sure if it was part of the issue or not but I had the same "related_name" on two Foreign keys on the same model. After fixing that I found that the 'annotation' value in extract_data method wasn't matching what was in prepared_results. Once I adjusted my model field value to be lower case it started working.

I'm now stuck trying to get 'base_q_filters' working to filter the data more. I can get it to work with overriding the get_queryset method but not using the field to pass the value into.

[RamezIssac]

Check this for reference on how to use Custom Computation field
https://django-slick-reporting.readthedocs.io/en/latest/topics/computation_field.html
Hope it helps..

[smseidl]

@RamezIssac - are there any additional examples? I have read that page multiple times but can't really figure out where to go next.

[RamezIssac]

Hello,
i'm afraid currently not.

I would really suggest the db view route...
it would be much easier i would say.

[smseidl]

OK. I will look into that. First time hearing of a DB View .