How to design Custom database function for LAG()

Using Django Using the ORM
1

Hi everyone,

I am using clickhouse as backend django database with django-clickhouse-backend as api bridging between django and clickhouse DB.

As ClickHouse Lag function is named as lagInFrame() instead of Lag(), an error occurred when the following orm query script was executed:

.
.
.
.annotate(lag_id=Window(expression=Lag(F(MemberID)), order_by=[F('MemberID'),F('LastPurchaseDate')])
.
.

I was wondering if the issue can be fixed by creating a custom database class function? or simply inherenting from LAG()?

Here is the class I made but it doesn’t work. Is there anyone know what goes wrong?

class LagInFrame(Func):
    function = 'lagInFrame'
    template = '%(function)s(%(expressions)s,%(offset)s,%(default)s) OVER (PARTITION BY %(partition_by)s, ORDER BY %(order_by)s)'

    def __init__(self, expression, default, partition_by, order_by, offset=1, **extra):
        super().__init__(
            expression=expression,
            offset=offset,
            default=default,
            partition_by=partition_by,
            order_by=order_by,
            **extra
        )

Thanks.

Kelvin

2

Hey @kelvinho8,

As ClickHouse Lag function is named as lagInFrame() instead of Lag()

If that’s the only difference between the two then simply import Lag and change the function name.

from django.db.models.functions import Lag

class LagInFrame(Lag):
    function = 'lagInFrame`

It’s impossible to determine if anything else needs adjustments as you haven’t provided the error message nor documentation describing the difference between the two.

4

Hi @charettes

It works, thanks charettes.

Kelvin