How to use postgres database function with django queryset?

I have a django project which stores real-time data of a few devices. For this manner I have used timescaledb which is suitable for time series and it's based on postgres. Timescale offers a few hyperfunctions which I need to use (specifically lttb which is used for downsampling data).

For example, this is one of the queries that I am looking to achieve:

SELECT time as timestamp, value as value, %s as device_id 
FROM unnest((SELECT lttb(timestamp, value, %s) 
FROM core_devicedata where device_id=%s and timestamp between %s and %s))

I can get result of this query as a raw query set by:

for data in DeviceData.objects.raw(query):
    ...

I have already tried raw sql queries using django. They work. The thing is they offer no filtering and ordering functionality since they do not return actual queryset. Instead, they return a raw query set. What I am trying to achieve is to run a query like below using only power of djagno orm.

SELECT time as timestamp, value as value, %s as device_id 
FROM unnest((SELECT lttb(timestamp, value, %s)

Any suggestions? If there is no way to do this with django orm itself, would please help me write a modular manager method for this manner? Like I should be able to filter or order only if I want.

Solution

My first advice is that the ORM will only get you so far. When things start getting complicate it's best to go the raw query route, otherwise you'll spend more time fighting the ORM than solving your real business problems.

You can add the filtering and ordering to the raw query with a WHERE clause and ORDER BY clause.

You can write custom ORM SQL functions with Func() expresions. You can see some examples of timescaledb functions written for Django, in this case time_bucket, in the django-timescale library repo.

You could define the function something like (this doesn't work):

class LTTB(Func):
    arity = 3
    function = "lttb"
    template = "(%(function)s(%(expressions)s)->unnest()).*"

The problem is that custom functions require an output_field, since unnest returns 2 extra columns, I don't know if the Django ORM supports that.