Django repeats the Postgres generate_series() function call in the WHERE clause

Using Django Using the ORM
1

Hello everyone
I have written a query set to generate doctors’ appointment time slots using the Postgres generate_series() function like this:


generate_series_func = Func(
            F('date') + F('schedule__start_at'),
            F('date') + F('schedule__end_at'),
            F('doctor__appointments_gap'),
            function='generate_series',
            output_field=DateTimeField()
)

queryset = Appointment.objects.filter(pk=pk).only(
            'date',
            'doctor__appointments_gap',
            'schedule__start_at',
            'schedule__end_at',
            'unavailable_time_slot__from_time',
            'unavailable_time_slot__to_time',
            )\
            .annotate(time_slot=Cast(generate_series_func, output_field=TimeField()))\
            .alias(time_slot_alias=F('time_slot'))\
            .exclude(time_slot_alias__gt=F('unavailable_time_slot__from_time'))\
            .all()

Everything is ok except that when I try to filter and exclude unavailable time slots, I get this error:

set-returning functions are not allowed in WHERE
LINE 1: ...ERE ("appointment_appointment"."id" = 1 AND NOT ((generate_s...

This is because Django repeats the “generate_series()” function call in the evaluated SQL query like this:

SELECT "appointment_appointment"."id",
       "appointment_appointment"."doctor_id",
       "appointment_appointment"."date",
       (generate_series(("appointment_appointment"."date" + "appointment_appointmentschedule"."start_at"), ("appointment_appointment"."date" + "appointment_appointmentschedule"."end_at"), "account_doctor"."appointments_gap"))::time AS "time_slot"
  FROM "appointment_appointment"
  LEFT OUTER JOIN "appointment_appointmentschedule"
    ON ("appointment_appointment"."id" = "appointment_appointmentschedule"."appointment_id")
 INNER JOIN "account_doctor"
    ON ("appointment_appointment"."doctor_id" = "account_doctor"."user_id")
  LEFT OUTER JOIN "appointment_unavailabletimeslot"
    ON ("appointment_appointment"."id" = "appointment_unavailabletimeslot"."appointment_id")
 WHERE ("appointment_appointment"."id" = 1 AND NOT ((generate_series(("appointment_appointment"."date" + "appointment_appointmentschedule"."start_at"), ("appointment_appointment"."date" + "appointment_appointmentschedule"."end_at"), "account_doctor"."appointments_gap"))::time > ("appointment_unavailabletimeslot"."from_time")))
 ORDER BY "appointment_appointment"."date" DESC

Noticed? The “generate_series()” function gets added in the WHERE clause too.

I’ve tried to use the “.alias()” method to save the expression but it got removed from the SELECT statement and appeared in the WHERE clause and I got the same error.

I’ve also tried to use the “.anotate()” method alongside the “.alias()” method to add the time_slot in the query set result and to be able to filter it but again the “generate_series()” function got repeated in the SELECT statement as well as “WHERE” clause.

2

I face the same issue.
django-generate-series · PyPI could be a solution but it bothers me to add another dependency to my application

Did you manage to fix your problem ?

3

Yes, I finally solved it.
I realized that when we use the “.anotate()” method Django ORM sets an alias in the evaluated SQL query but since we can not use aliases in the WHERE clause, Django calls the Postgres “generate_series()” function in the WHERE clause as well and that causes an error.
In the end, I used the Django ORM to fetch the appointment details from the database and I used a raw SQL query to leverage the Postgres “generate_series()” function.

Here is the solution I came up with:

from celery import shared_task
from django.db import transaction, connection
from .models import Appointment, TimeSlot, TimeSlotStatus

@shared_task
def generate_appointment_time_slots_task(appointment_id):
    appointment = Appointment.objects.select_related('doctor', 'schedule', 'unavailable_time_slot').get(pk=appointment_id)

    with connection.cursor() as cursor:
        query_filter = ''

        params = {
            'appointment_date': appointment.date,
            'schedule_start_at': appointment.schedule.start_at,
            'schedule_end_at': appointment.schedule.end_at,
            'appointments_gap': appointment.doctor.appointments_gap
        }

        if appointment.unavailable_time_slot:
            query_filter = """
                WHERE NOT (
                    time_slot::time >= %(unavailable_from_time)s AND
                    time_slot::time < %(unavailable_to_time)s
                )
            """
            params['unavailable_from_time'] = appointment.unavailable_time_slot.from_time
            params['unavailable_to_time'] = appointment.unavailable_time_slot.to_time

        query = f"""
            SELECT time_slot::time FROM generate_series(
                (%(appointment_date)s + %(schedule_start_at)s),
                (%(appointment_date)s + %(schedule_end_at)s),
                %(appointments_gap)s
            ) time_slot {query_filter}
        """

        cursor.execute(query, params=params)

        time_slots = cursor.fetchall()

        with transaction.atomic():
            created_time_slots = TimeSlot.objects.bulk_create([
                TimeSlot(appointment_id=appointment.id, time=time_slot[0])
                for time_slot in time_slots
            ])

            TimeSlotStatus.objects.bulk_create([
                TimeSlotStatus(time_slot_id=time_slot.id)
                for time_slot in created_time_slots
            ])

It needs some refactoring and error handling but at the moment, I’m happy with it.
I hope it’ll help the others.

4

Ok, thanks for your reply.
I also ended up using a raw() manager method :

qs = Channel.objects.raw(
    """
    select id, period.day as day
    from quality_channel
    join (select generate_series(%s::date, %s, %s) as day) as period on True
    where
        end_date <= period.day
        and extract(year from end_date) = extract(year from period.day)
    """,
    [start, end, "1 day"],
)
1 Like