Django store actual availability and query the available schedule for new booking

Question

In the app, there are two types of user: Student and Mentor

1. As a mentor user type, i can set my weekly based availability
2. as a student user type, I can see a mentor availability.

==============================

So I am storing user availability based on week day in WeekDay and AvailableHour models

DAYS_OF_WEEK = (
    (0, 'Monday'),
    (1, 'Tuesday'),
    (2, 'Wednesday'),
    (3, 'Thursday'),
    (4, 'Friday'),
    (5, 'Saturday'),
    (6, 'Sunday'),
)
        
class WeekDay(models.Model):
    mentor = models.ForeignKey(
        User,
        on_delete=models.CASCADE,
        related_name="weekday"
    )
    weekday = models.IntegerField(choices=DAYS_OF_WEEK)
    

class AvailableHour(models.Model): #Based on weekday
    weekday = models.ForeignKey(
        WeekDay,
        on_delete=models.CASCADE,
        related_name="available_hour"
    )
    from_hour = models.TimeField()
    to_hour = models.TimeField()

3. As a user type student i can book a session only the in mentor availability hour.

So when a student books a session, we are storing the information there below models.

 class BookingSession(models.Model):
   student = models.ForeignKey(
        User,
        on_delete=models.CASCADE,
        related_name="student_booking"
    )
    mentor = models.ForeignKey(
        User,
        on_delete=models.CASCADE,
        related_name="mentor_booking"
    )
    date = models.DateField()
    
    from_hour = models.TimeField()
    to_hour = models.TimeField()

So what's the problem now?

As we have user AvailableHour so as soon as a student book a tutor session, then the specified time range is unavabale of tutor profile, right? so that another student can see a tutor actual availability.

So you know, if we get queryset from AvailableHour it doesn't mean tutor is available in that time if any specified time range is already booked and the booked time already stored in BookingSession models

I want to exclude AvailableHour's records if the start and end time are existing in the BookingSession in the specified weekday

I want to show the student, what is tutor's actual availability is.

Like AvailableHour can't be stored actual available if there is already a booking in BookingSession You know it !!

Let's say I have data in BookingSession Table

booked_sesson = [
    {'date': '2021-08-02','from_hour': '12:30', 'to_hour': '12:15' }, # monday
    {'date': '2021-08-04','from_hour': '02:30', 'to_hour': '03:15' }, # Wednesday
]

and in WeekDay and AvailableHour availability table data below

available_based_on_weekday = [
    {
        "weekday": 1, 'opening_hour_time': [ # monday
            {'from_hour': '12:30', 'to_hour': '12:15'},
            {'from_hour': '12:30', 'to_hour': '12:15'},
            {'from_hour': '12:30', 'to_hour': '12:15'}
        ]
    },
    {
        "weekday": 7, 'opening_hour_time': [ # Saturday
            {'from_hour': '13:30', 'to_hour': '15:15'},
            {'from_hour': '15:30', 'to_hour': '16:15'},
            {'from_hour': '19:30', 'to_hour': '20:15'},
            {'from_hour': '23:30', 'to_hour': '23:45'}
        ]
    },
    {
        "weekday": 3, 'opening_hour_time': [ # Wednesday
            {'from_hour': '02:30', 'to_hour': '03:30'},
            {'from_hour': '17:30', 'to_hour': '18:15'},
            {'from_hour': '19:30', 'to_hour': '20:15'},
            {'from_hour': '21:30', 'to_hour': '22:30'}
        ]
    }
]

What output result I am expecting? After the query, I am expecting these outputs and should be queryset month by month:

actual_availability = [
     {
         'date': '2021-08-01',
         'available': []
     },
     {
         'date': '2021-08-02',
         'available': [
            {'from_hour': '12:30', 'to_hour': '12:15'},
            {'from_hour': '12:30', 'to_hour': '12:15'}
         ]
     },
     {
         'date': '2021-08-03',
         'available': []
     },
     {
         'date': '2021-08-04',
         'available': [
            {'from_hour': '03:15', 'to_hour': '03:30'},
            {'from_hour': '17:30', 'to_hour': '18:15'},
            {'from_hour': '19:30', 'to_hour': '20:15'},
            {'from_hour': '21:30', 'to_hour': '22:30'}
         ]
     },
     {
         'date': '2021-08-05',
         'available': []
     },
     {
         'date': '2021-08-06',
         'available': []
     },
     {
         'date': '2021-08-07',
         'available': []
     },
     {
         'date': '2021-08-08',
         'available': []
     },
     {
         'date': '2021-08-09',
         'available': [
            {'from_hour': '12:30', 'to_hour': '12:15'},
            {'from_hour': '12:30', 'to_hour': '12:15'},
            {'from_hour': '12:30', 'to_hour': '12:15'}
         ]
     },
     # to be continued for a month by month
]

If you anyone have any alternative approach, i will welcome, please share your point of views here.

Here you go what I have tried and it's really stupid, doesn't work.

real_available_date_slot = []
for obj in BookingSession.objects.filter(mentor_id='A mentor ID'):
    weekday =  obj.date.isoweekday()
    real_available = []

    available_hour = AvailableHour.objects.filter(weekday__weekday=weekday, weekday__mentor=obj.mentor)
    availables = available_hour.exclude(
        from_hour__gt=obj.to_hour
    ).exclude(
        to_hour__lt=obj.to_hour
    )
    
    for available in availables:
        real_available.append(
            {'from_hour': available.from_hour, 'to_hour': available.to_hour}
        )

    real_available_date_slot.append({
        obj.date: real_available
    })

Also i have tried different other way but i couldn't come with any solution

Can anyone share your thought how we can solve my availability problem? What is your approach of querying this kind of thing? or i made the database design improperly for this case?

Answer 1

My idea is to use time slots to keep track of sessions. The booked sessions are unique for every student and for every mentor.

I tried to keep my proposal the most simple and self-explaining as possible, but please let me know if you need more details (for example more printed outputs, the settings file, the admin, or other snippets), as I already tested the solution in local with a Django app and it works pretty fine.

Here the models.py

from django.contrib.auth.models import User
from django.db import models

DAYS_OF_WEEK = (
    (0, 'Monday'),
    (1, 'Tuesday'),
    # ...
    (6, 'Sunday'),
)

TIME_SLOT_UNITS = (  # you can easily generate these values with a script basing on your requirements
    (0, '00:00-00:15'),
    (1, '00:15-00:30'),
    (2, '00:30-00:45'),
    (3, '00:45-01:00'),
    # ...
    (94, '23:30-23:45'),
    (95, '23:45-00:00'),
)

class BookingSession(models.Model):
    student = models.ForeignKey(User, on_delete=models.CASCADE, related_name='student_booking_sessions')
    mentor = models.ForeignKey(User, on_delete=models.CASCADE, related_name='mentor_booking_sessions')
    day = models.IntegerField(choices=DAYS_OF_WEEK, db_index=True)
    time = models.IntegerField(choices=TIME_SLOT_UNITS, db_index=True)

    class Meta:
        ordering = ('day', 'time')
        unique_together = (
            ('mentor', 'day', 'time'),  # This is needed to make sure that the persistence is consistent
        )

    def __str__(self):
        return f'Mentor {self.mentor} - Student {self.student} - {self.get_day_display()} {self.get_time_display()}'

And here the functions you can use in your views/serializers:

def group_availability_by_day(available_slots):
    grouped_availability = {}
    for slot in available_slots:
        slot_day, slot_time = slot[0], slot[1]
        grouped_availability[slot_day] = grouped_availability.get(slot_day, []) + [slot_time]
    return grouped_availability


def get_grouped_intervals(grouped_availability):
    grouped_intervals = {}
    for day, times in grouped_availability.items():
        day_intervals = []
        if times:
            curr_time = times[0]
            curr_interval = [curr_time]
            for time in times[1:]:
                if time == curr_time + 1:
                    curr_interval = [curr_interval[0], time]
                else:
                    day_intervals.append(curr_interval)
                    curr_interval = [time]
                curr_time = time
            day_intervals.append(curr_interval)
        grouped_intervals[day] = day_intervals
    return grouped_intervals


def get_all_bookable_sessions_in_a_day():
    return {(day, time) for time in dict(TIME_SLOT_UNITS).keys() for day in dict(DAYS_OF_WEEK).keys()}


def get_booked_sessions_by_student(student_id):
    return set(BookingSession.objects.filter(student_id=student_id).values_list('day', 'time'))


def get_booked_sessions_by_mentor(mentor_id):
    return set(BookingSession.objects.filter(mentor_id=mentor_id).values_list('day', 'time'))


def get_available_sessions_by_mentor(mentor_id):
    return sorted(get_all_bookable_sessions_in_a_day() - get_booked_sessions_by_mentor(mentor_id))


def serialize_intervals(input_intervals):
    grouped_availability = group_availability_by_day(input_intervals)  # Group by day
    grouped_intervals = get_grouped_intervals(grouped_availability)  # Find intervals
    printable_intervals = [{
        'weekday': day,
        'opening_hour_time': [{
            'from_hour': dict(TIME_SLOT_UNITS)[i[0]][:5],
            'to_hour': dict(TIME_SLOT_UNITS)[i[-1]][6:],
        } for i in intervals]
    } for day, intervals in grouped_intervals.items()]
    return printable_intervals

The output of the serialize_intervals function is formatted as your definition

 availability = get_available_sessions_by_mentor(your_mentor_id)
 print(serialize_intervals(availability))

here a sample output:

[{'opening_hour_time': [{'from_hour': '00:00', 'to_hour': '00:30'},
                        {'from_hour': '00:45', 'to_hour': '01:15'},
                        {'from_hour': '23:30', 'to_hour': '00:00'}],
  'weekday': 0},
 {'opening_hour_time': [{'from_hour': '00:00', 'to_hour': '01:15'},
                        {'from_hour': '23:30', 'to_hour': '00:00'}],
  'weekday': 1},
 {'opening_hour_time': [{'from_hour': '00:00', 'to_hour': '00:45'},
                        {'from_hour': '01:00', 'to_hour': '01:15'},
                        {'from_hour': '23:30', 'to_hour': '00:00'}],
  'weekday': 2},
 {'opening_hour_time': [{'from_hour': '00:00', 'to_hour': '01:15'},
                        {'from_hour': '23:30', 'to_hour': '00:00'}],
  'weekday': 3},
 {'opening_hour_time': [{'from_hour': '00:00', 'to_hour': '01:15'},
                        {'from_hour': '23:30', 'to_hour': '00:00'}],
  'weekday': 4},
 {'opening_hour_time': [{'from_hour': '00:00', 'to_hour': '01:15'},
                        {'from_hour': '23:30', 'to_hour': '00:00'}],
  'weekday': 5},
 {'opening_hour_time': [{'from_hour': '00:00', 'to_hour': '01:15'},
                        {'from_hour': '23:30', 'to_hour': '00:00'}],
  'weekday': 6}]

Possible improvements:

• Create dedicated models for Student and Mentor
• Use a Model for the TIME_SLOT_UNITS (so you can create more complex or custom logic)
• Cache values for each mentor
• Save a group of consecutive slots in bulk for the same student/mentor
• Make the mentor available only on certain time slots

Answer 2

This would be my approach (untested):

from django.db import models


class MentorUnavailable(Exception):
    pass


class Mentor(models.Model):
    def book(self, from_hour, to_hour):
        availability = self.availability.filter(from_hour__lte=from_hour, to_hour__gte=to_hour, status__in=[Availability.AVAILABLE])
        if availability.exists():
            availability = availability.first()
            return availability.split(from_hour, to_hour)  # returns an Availability with status booked
        else:
            raise MentorUnavailable


class Student(models.Model):
    bookings = models.ManyToManyField("mentor_bookings.Availability")

    def book_mentor(self, mentor: Mentor, from_hour, to_hour):
        booking = mentor.book(from_hour, to_hour)
        self.bookings.add(booking)


class Availability(models.Model):
    # status
    BOOKED = 0
    AVAILABLE = 1
    STATUS_CHOICES = [
        (BOOKED, "Booked"),
        (AVAILABLE, "Available")
    ]
    # fields
    mentor = models.ForeignKey(Mentor, on_delete=models.CASCADE, related_name="availability")
    from_hour = models.DateTimeField()
    to_hour = models.DateTimeField()
    status = models.IntegerField(choices=STATUS_CHOICES, default=AVAILABLE)

    def split(self, from_hour, to_hour):
        if not (self.from_hour <= from_hour and self.to_hour >= to_hour):
            raise Exception
        if from_hour - self.from_hour:
            new_instance = Availability.objects.get(pk=self.pk)
            new_instance.pk = None
            new_instance.to_hour = from_hour
            new_instance.save()
        if self.to_hour - to_hour:
            new_instance = Availability.objects.get(pk=self.pk)
            new_instance.pk = None
            new_instance.from_hour = to_hour
            new_instance.save()
        self.status = Availability.BOOKED
        self.save()
        return self

The idea is to let the mentor decide a time range when he is available, and when the student selects a time range within it, the availability is split, so the remaining time ranges remain available, and the booked time slot has the status booked. Then you can take that availability and add it to the student's bookings.

The advantage is that you have simpler queries and less models. from_time and to_time will always be datetime objects, since you need to compare them and make calculations. Watch out for timezones. I would also recommend to add some validators, like suggested in other answers, to not allow for bookings that end at unsuitable times. This will also reduce the amount of Availability objects that will be created when splitting occurs. So 15 minutes blocks would be fine. It also allows you to easily merge availabilities in case a student cancels a booking. The Availability.status field could also be a boolean, but I chose it to be an Integer field, in case you want to expand it with more options.

Serializing the availabilities of a mentor becomes hopefully easier now.

Answer 3

Another approach, and building up on the answer of @Jimmar with some variations:

Availability can hold the mentor information and the available schedule a mentor has. So a mentor can have many items on its availability relation which will have the weekday information as well as the time slot.

class Availability(models.Model):
    mentor = models.ForeignKey(
        User,
        on_delete=models.CASCADE,
        related_name="availability"
    )
    weekday = models.IntegerField(choices=DAYS_OF_WEEK)
    from_hour = models.TimeField()
    to_hour = models.TimeField()

For example, a mentor can have this availability:

{'id': 1, 'mentor': 1, 'weekday': 1, 'from_hour': '12:15', 'to_hour': '12:45'}
{'id': 2, 'mentor': 1, 'weekday': 1, 'from_hour': '12:45', 'to_hour': '13:15'}
{'id': 3, 'mentor': 1, 'weekday': 1, 'from_hour': '13:15', 'to_hour': '14:45'}

In turn, Bookings can now hold the student and mentor_session (Availability) relation, but with the booked date. This will support getting the Availability items and exclude by booked date.

class BookingSession(models.Model):
    student = models.ForeignKey(
        User,
        on_delete=models.CASCADE,
        related_name="bookings"
    )
    mentor_session = models.ForeignKey(
        Availability,
        on_delete=models.CASCADE,
        related_name="bookings"
    )
    date = models.DateField()

For example, if we have the below bookings:

{'id': 1, 'student': 1, 'mentor_session': 1, 'date': '2021-01-01'}
{'id': 2, 'student': 1, 'mentor_session': 1, 'date': '2021-01-02'}
{'id': 3, 'student': 1, 'mentor_session': 1, 'date': '2021-01-03'}

This means that availability id 1 of the mentor is booked on above dates by student with id 1. So now to get the available time for a mentor based on a date:

date = datetime.date(2021, 01, 01)
weekday = date.isoweekday()

Availability.objects.filter(mentor=mentor, weekday=weekday).annotate(
    is_booked_on_date=Exists(
        BookingSession.objects.filter(mentor_session=OuterRef('pk'), date=date)
    )
).filter(is_booked_on_date=False)

Since availability with id 1 is already booked on the date specified (it has a booking session), this should return only the availability ids 2 and 3 for the date 2021-01-01.

To support the expected output you want, you can just iterate through each day on a certain day range and use the above query's result.

Answer 4

You can save the availability as time-slots of 15 minutes for example. The instead of having BookingSession link between student and mentor, you can have it link between a student (user) and an AvailableHour object.

 class BookingSession(models.Model):
   student = models.ForeignKey(
        User,
        on_delete=models.CASCADE,
        related_name="student_booking"
    )

And in the AvailableHour model, you can have a ForeignKey field linked to the Bookingsession object related to it.

# this is a timeslot now
class AvailableHour(models.Model): #Based on weekday
    weekday = models.ForeignKey(
        WeekDay,
        on_delete=models.CASCADE,
        related_name="available_hour"
    )
    from_hour = models.TimeField()
    to_hour = models.TimeField()
    booked_by = ForeignKey(User, null=True)

and when filtering to show, you just filter out any time slot that doesn't contain a booked_by==null

AvailableHour.objects.filter(weekday__weekday=weekday, weekday__mentor=obj.mentor, booked_by=null)