Django - How to compare different models with same foreign key

I'm really stuck in how to do this and appreciate the help. I have three models, two of them shares the same foreign key field:

class AccountsPlan (models.Model):
    code = models.CharField(max_length=7, unique=True,)
    name = models.CharField(max_length=100, unique=True,)

class Planning (models.Model):
    accountplan = models.ForeignKey(AccountsPlan, on_delete=models.PROTECT)
    month = models.DateField()
    amount = models.DecimalField(max_digits=14, decimal_places=2,)

class Revenue (models.Model):
    accountplan = models.ForeignKey(AccountsPlan, on_delete=models.PROTECT)
    receipt_date = models.DateField()
    amount = models.DecimalField(max_digits=14, decimal_places=2,)

And I have this view that annotates the sum for each model by the foreign key name with a form that filters by date:

def proj_planning(request):
    form = ProjectionFilterForm(request.POST or None)

    # some definitions for the form

    planned = Planning.objects.values('accountplan__name').annotate(Sum('amount')).order_by('accountplan__code').filter(
        month__range=[start_date, planned_end_date])

    done = Revenue.objects.values('accountplan__name').annotate(Sum('amount')).filter(
        receipt_date__range=[start_date, end_date],).order_by('accountplan__code')

    if request.method == 'POST':
        planned = Planning.objects.values('accountplan__name').annotate(Sum('amount')).order_by(
            'accountplan__code').filter(month__range=[start_date, planned_end_date],
                                        accountplan__name__icontains=form['accountplan'].value())

        done = Revenue.objects.values('accountplan__name').annotate(
            Sum('amount')).filter(receipt_date__range=[start_date, end_date],
                                  accountplan__name__icontains=form['accountplan'].value())
    
    comp_zippedlist = zip(planned, done)

    return render(request, 'confi/pages/planning/proj_planning.html', context={
        'form': form,
        'list': comp_zippedlist,
    })

The code kinda works (it doesn't throw any errors), but the thing is, I will never have an exact amount of data for each model.

For example: if I have 6 different records in the Planning model, but only 4 records in the Revenues model, the zipped list will only show the 4 records in the Revenues and not the other 2 records in the Planning model, which makes sense.

But what I need is to show all 6 records from the Planning model with the corresponding 4 records from the Revenue model and the other 2, blank or something like that. Ultimately, I will need to compare the two values and show some kind of status based on the two amount field values, that's why I have to show all of them.

The result I expect is something like this:

Accountplan      | Planned | Done
__________________________________

Accountplan A    | 500     | 250
Accountplan B    | 1000    | 860
Accountplan C    | 800     | None
Accountplan D    | 150     | 350
Accountplan E    | 300     | None
Accountplan F    | 700     | 900

I tried to use prefetch_related to do this, using this code:

done = AccountsPlan.objects.prefetch_related('revenue_set').values(
        'name').annotate(Sum('revenue__amount')).order_by('code')

And while it shows exactly what I want, if I apply a filter in the receipt_date field of the Revenue model, it will have the same result as before, witch again, make sense.

I thought of looping through every revenue record with no accountplan and apply a temporary value 0 to them, but the accountplan is required, so that idea made no sense.

I also looked at the multi-table inheritance but since the relation with the child models is one-to-one, it does not apply in this case (unless I misread something).

Is there a way to have the behavior described and possible with the prefetch_related code, but keeping all foreign key fiels after a filter? Thanks!

Back to Top