Django: Filter based on foreignkey's start date and end date

user_11445415 0
Viewed 270

I have the following two models:

class ParentModel(models.model):
    name = models.CharField("Name", max_length = 256, blank = True)    
    start_date = models.DateField(blank = True, null = True)
    end_date = models.DateField(blank = True, null = True)


class ChildModel(models.model):
    name = models.CharField("Name", max_length = 256, blank = True)    
    parent_model = models.ForeignKey(ParentModel, blank = True, null = True, on_delete=models.SET_NULL)
    start_date = models.DateField(blank = True, null = True)
    end_date = models.DateField(blank = True, null = True)

Now I want to filter all the childmodel's whose date range is not within the date range of the associated parentmodel's date range.

The code I tried:

import pandas as pd

child_models = ChildModel.objects.filter(start_date__lte=input_end_date, end_date__gte=input_start_date)
exceeding_child_models = []

for child_model in child_models:
    date_range = pd.date_range(start=child_model.start_date, end=child_model.end_date)
    if child_model.start_date not in date_range or child_model.end_date not in date_range:
        exceeding_child_models.append(child_model)

How can I do that using Django ORM in a single query?

django-orm django python
1 Answers

Two intervals [s1, e1] and [s2, e2] do not overlap if s1 > e2 or s2 > e1.

We thus can check this with:

from django.db.models import F, Q

ChildModel.objects.filter(
    Q(start_date__gt=F('parent_model__end_date')) |
    Q(end_date__lt=F('parent_model__start_date'))
)
user_675790
Related