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.
Вернуться на верх