How To Annotate Modified Related-Object Table In Django

Question

I have a small business app which can be simplified as following models:

class Client(..):
    name = CharField(...)

class Sale(..):
    client = ForeignKey(Client, ...)
    item = CharField(...)
    time = DateTimeField(...)
    value = DecimalField(...)

class Receive(..):
    client = ForeignKey(Client, ...)
    time = DateTimeField(...)
    value = DecimalField(...)

Now I need a client-list-view displaying all clients with total sales, payments, receivable value and earliest date of sales unpaid ("edsu") that payment cannot cover.

E.g. Client A

• 2022-01-01: bought Item X for $10,
• 2022-02-15: bought Item Y for $15,
• 2022-02-25: bought Item X for $10, and
• 2022-03-10: paid $12.

Then Client A has a receivable of $23 and edsu of 2022-02-15.

So far I use raw sql (as following) to do the query, and it works well.

def client_list_view(request):
    ...
    clients = Client.objects.raw(
        raw_query = '''WITH app_balance AS (
            SELECT id, client_id, item, time, val,
                SUM(sale) OVER wCN0 - SUM(receive) OVER wClt AS unpaid
            FROM (
                SELECT id, client_id, item, time, val AS sale, 0 AS receive
                    FROM app_sale
                UNION SELECT id, client_id, '' AS item, time, 0 AS sale, val AS receive
                    FROM app_receive
            ) app_balance
            WHERE time < %(te)s::TIMESTAMPTZ
            WINDOW wClt (PARTITION BY client_id),
               wCN0 (wClt ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
            ORDER BY time
        )
        SELECT id, name, sales, receive, receivable, edsu
        FROM app_client LEFT JOIN (
            SELECT client_id, 
                SUM(sale) FILTER (WHERE time > %(te)s::TIMESTAMPTZ) AS sales,
                SUM(receive) FILTER (WHERE time > %(te)s::TIMESTAMPTZ) AS receive,
                SUM(sale-receive) AS receivable, 
                MIN(time) FILTER (WHERE unpaid > 0) AS edsu
            FROM app_balance
            GROUP BY client_id
        ) client_balance ON client_balance.client_id = app_client.id;''', 
        params = {'ts': time_start.isoformat(), 'te': time_end.isoformat()}
    )
    return render(request, 'app/clients.html', {'clients': clients})     

However as the app grows, it becomes harder to maintain and pushes me to make the decision to drop raw sql and turn to Django-styled QuerySet, starting with following SQL view

CREATE VIEW app_balance_view AS 
    SELECT
        id, 1 AS cat, client_id, item, time, val AS sale, 0 AS rcv
        FROM app_sale
    UNION SELECT
        id, 2 AS cat, client_id, '' AS item, time, 0 AS sale, val AS rcv
        FROM app_receive;

and un-managed model

class Balance(..):
    cat = IntegerField(choices=CATS, ...)
    client = ForeignKey(Client, ...)
    item = CharField(...)
    time = DateTimeField(...)
    sale = DecimalField(...)
    rcv = DecimalField(...)
    class Meta:
        managed = False
        db_table = 'app_balance_view'

I stuck here finding no way to annotate client with a custom balance QuerySet possibly like following:

balance = Balance.objects.filter(time__lt=time_end).annotate(
    total_sale = Window(Sum('sale'), partition_by=F('client_id')),
    cur_paid = Window(Sum('rcv'), partition_by=F('client_id'), frame=RowRange(None,0))
).annotate(unpaid = F('total_sale') - F('cur_paid'))

such that I can do

clients = Client.objects.annotate(
    edsu = Min('balance__time', filter=Q(balance__unpaid__gt=0)),
)

If above is not possible (or not recommended), what would be an alternative to achieve that?

Environment:

• python == 3.10.2
• Django == 4.0.1
• PostgreSQL == 14

Answer 1

I find a solution by using following view function:

CREATE VIEW app_bal_view AS
    SELECT 
        id, cat, client_id, time, item, sale, rcv,
        SUM(sale) OVER wcn0 - SUM(rcv) OVER wclt AS unpaid
        FROM (
            SELECT
                id, 1 AS cat, client_id, time, item, value AS sale, 0 AS rcv
                FROM app_sale
            UNION SELECT
                id, 2 AS cat, client_id, time, ‘’ AS item, 0 AS sale, value AS rcv
                FROM app_receive
        ) app_balance_tmp
        WINDOW wclt AS (PARTITION BY client_id),
            wcn0 AS (wclt ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

adding a field unpaid to Balance model

class Balance(..):
    ..
    unpaid = DecimalField(..)

and using the same clients’ QuerySet as in Question.