Django GenerateSeries return same row multiple times
I've an appointment model with starts_at, repeat_duration
I want to annotate the repeating values returned from a generated series the sum duration field till the end date
so if date of appointment on 14-07-2024, end_date is 25-07-2024 and duration is 3 days it return 14, 17, 20, 23 as an array annotation
but what I'm getting is the same object, returned multiple times and each time it has one value of the series result
for the example it'll be returned four times one for each value and repeat_days will be this value
object_1.repeat_days = 14 object_2.repeat_days = 17
how to make it return only one object and the values as an array
tried arrayagg to wrap the GenerateSeries but I'm getting
aggregate function calls cannot contain set-returning function calls
class AppointmentQuerySet(models.QuerySet):
def annotate_repeated_days(self, end_date):
return self.annotate(
repeat_dates=ArrayAgg(
GenerateSeries(
models.F("starts_at"), models.F("repeat_duration"), end_date
)
)
)
function:
from django.db import models
class GenerateSeries(models.Func):
function = 'generate_series'
output_field = models.DateTimeField()
def __init__(self, start, step, stop, **extra):
expressions = [
start,
stop,
step
]
super().__init__(*expressions, **extra)
any ideas?
That results in an illegal SRF-in-aggregate construct pointed out by the error
array_agg(generate_series(starts_at,end_date,repeat_duration)) as repeat_dates
But it's easy to side-step the limitation by moving the set-returning function into a subquery and letting the aggregate function pull rows out of that:
(select array_agg(n) as repeat_dates
from(select generate_series(starts_at,end_date,repeat_duration)) as g(n))
You can get Django to use the subquery syntax by setting subquery=True
:
from django.db import models
class GenerateSeries(models.Func):
subquery = True ###################### here
function = 'generate_series'
output_field = models.DateTimeField()
def __init__(self, start, step, stop, **extra):
expressions = [#side-note: why reorder Postgres' (stOp,stEp) to (stEp,stOp)?
start,
stop,
step
]
super().__init__(*expressions, **extra)
Django checks that here and it influences the decision here:
# Decide if we need to use a subquery. # # Existing aggregations would cause incorrect results as # get_aggregation() must produce just one result and thus must not use # GROUP BY. # # If the query has limit or distinct, or uses set operations, then # those operations must be done in a subquery so that the query # aggregates on the limit and/or distinct results instead of applying # the distinct and limit after the aggregation.