Django model filtering with F and Q expressions

Question

I am making a scheduler in Django and having issues filtering my events for the weekly calendar view. The calendar supports multi-day events, and my current filter doesn't work with this weekly view.

Here is my model:

 class Event(models.Model):
    title = models.CharField(max_length=40)
    start = models.DateTimeField()
    end = models.DateTimeField()
    description = models.TextField()
    all_day = models.BooleanField(default=False)
    recuring = models.BooleanField(default=False)
    recuring_end = models.DateTimeField(blank=True, null=True)

    def __str__(self):
        return self.title

    def get_absolute_url(self):
        return '/cab/event/%i/' % self.id

and I'm trying to filter the events that occur during a given week. For single day events I do something like.

events = Event.objects.order_by('start').filter(Q(start__gte=monday) | Q(end__lte=sunday))

This works to retrieve all single day events that occur during the week. It also works for multi-day events that either start or stop during the given week. The issue is retrieving objects that start before and finish after the week, but do span the week.

My idea is to try and filter out any event spanning longer than 9 days (ie. would start Sunday of prior week and finish Monday of next week) since I know these are rare and wont completely destroy performance. I want to do this without specifying a date range as this is not dynamic.

To try and minimise the performance impact I was trying to use F expressions to evaluate the duration of the event with the start and end of the event. My first idea was to do something like:

my_events = Event.objects.order_by('start').filter(Q(start__gte=monday) | Q(end__lte=sunday) | Q( (F('end_day') - F('start_day')) >= 9 ) )

but I get error 'bool' object is not iterable

also tried:

my_events = Event.objects.order_by('start').filter(Q(start__gte=monday) | Q(end__lte=sunday) | Q( (F('end_day') - F('start_day')) >= datetime.timedelta(days=9) ) )

but get can't compare datetime.timedelta to ExpressionNode

Anyone have incite as how to do such a thing?

Answer 1

from datetime import timedelta

Event.objects.filter(end__gt=F('start') + timedelta(days=9))

Documentation has example.

UPDATE:

Events, that span more than 9 days AND (start later than Monday OR end sooner than Sunday), ordered by start.

(Event.objects
 .filter(end__gt=F('start') + timedelta(days=9),
         Q(start__gte=monday) | Q(end__lte=sunday))
 .order_by('start'))

Answer 2

Just the warning to @https://stackoverflow.com/users/4907653/f43d65 's answer, that last query lookups with Q objects might be invalid.

Reference to docs

Lookup functions can mix the use of Q objects and keyword arguments. All arguments provided to a lookup function (be they keyword arguments or Q objects) are “AND”ed together. However, if a Q object is provided, it must precede the definition of any keyword arguments. For example:

Poll.objects.get(
Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)),
question__startswith='Who',)

… would be a valid query, equivalent to the previous example; but:

# INVALID QUERY
Poll.objects.get(
    question__startswith='Who',
    Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6))
)

… would not be valid.