How to build query when calculation based on model attributes are needed for filter ?

Using Django
1

I have to show Calendar Events information for current week only.
This is calculated from models CalendarEvent and CalendarEventException.
CalendarEvent is defined with WEEKDAY and TIME. WEEKDAY is defined from DayChoice TextChoices.
CalendarEventException is defined with STARTTIME and ENDTIME of exception which means that between that datetimes CalerndarEvent should not be scheduled.

To calculate the CalendarEvent events for this week first I have to calculate the exact datetime for this week from WEEKDAY and TIME and then I have to check if there are no CalendarEventException exceptions which interfere with the event datetime.

I’m using properties (@property) this_week_event_datetime and this_week_event_datetime_end to calculate real datetime for each event for this week.
And now I have trouble to built the DB query because it seems I cannot use @property in the filter().

What would you suggest how to solve this ?

from datetime import datetime
from datetime import timedelta
from django.utils import dateparse
from django.utils import timezone

# ...other imports and models...

class DayChoice(models.TextChoices):
    D0 = 0, 'MONDAY' 
    D1 = 1, 'TUESDAY'
    D2 = 2, 'WEDNESDAY'
    D3 = 3, 'THURSDAY'
    D4 = 4, 'FRIDAY'
    D5 = 5, 'SATURDAY'
    D6 = 6, 'SUNDAY'

class CalendarEvent(models.Model):
    day = models.CharField(max_length=2, choices=DayChoice.choices, default=DayChoice.D1)
    time = models.TimeField(default=timezone.now())
    duration_minutes = models.IntegerField(default=60)
    is_active = models.BooleanField(default=True, verbose_name='Event is Active')

	@property
    def this_week_event_datetime(self):
		monday0000 = (dt - timedelta(days=dt.weekday())).replace(hour=0, minute=0, second=0, microsecond=0)
		this_week_day = monday0000 + timedelta(days=int(DayChoice(self.day).value))
		return this_week_day.replace(hour=self.time.hour, minute=self.time.minute, second=self.time.second, microsecond=self.time.microsecond)
    
	@property
    def this_week_event_datetime_end(self):
        return self.this_week_event_datetime + timedelta(minutes=duration_minutes)
    
	# THIS DOES NOT WORK because properties are used in filter()
	def get_this_week_events():
        # IDEA WAS: Get CalendarEvents which 1) are ACTIVE and 2) don't have Exceptions which are ACTIVE and which interefere with Event time
		return CalendarEvent.objects.filter(
            Q(is_active=True) &
            ~Q(is_active=True,ue_exceptions__start_at__lte=this_week_event_datetime,ue_exceptions__end_at__gte=this_week_event_datetime_end)
        )

class CalendarEventException(models.Model):
    calendar_vent = models.ForeignKey(CalendarEvent, related_name='event_exceptions', on_delete=models.CASCADE)
    start_at = models.DateTimeField(default=timezone.now())
    end_at = models.DateTimeField(default=timezone.now()+timedelta(days=30))
    is_active = models.BooleanField(default=True, verbose_name='Exception is Active')
2

You can use Func() expressions within your query to perform the calculations you’re trying to do in your code. If you follow the link to the database functions, you’ll see there’s a wide range of date functions available to you.

1 Like
3

@KenWhitesell Thanks, I didn’t know about Func()…