[FIXED] How to filter QuerySet depending on fields of a reverse foreign key related model?

Issue

I have two following models:

class Tour(models.Model):
    name = models.CharField(max_length=100)
    description = models.CharField(max_length=3000, blank=True

    # some other fields here

and

class TourDatesInfo(models.Model):
    departure_date = models.DateTimeField()
    return_date = models.DateTimeField()
    tour = models.ForeignKey(Tour, on_delete=models.CASCADE, related_name='dates')

Tour model has a one-to-many relationship with a TourDatesInfo, so that one tour may have multiple different sets of departure/return dates.

What I try to achieve is to be able to filter the tours QuerySet depending on their set of TourDatesInfo, preciesely on whether each tour contains departure/return pair that satisfies specific conditions, e.g., get all tours that have at least one TourDatesInfo with departure_date > 2022-04-12 and return_date < 2022-05-01.

I can write an SQL query to perform this, something like SELECT * FROM tours_tour tours WHERE tours.id IN (SELECT DISTINCT tour_id FROM tours_tourdatesinfo WHERE departure_date > '2022-04-12' AND return_date < '2022-05-01');, but how it can be done using django ORM without raw queries?

Solution

You can filter with:

Tour.objects.filter(
    dates__departure_date__gt='2022-04-12',
    dates__return_date__lt='2022-05-01'
).distinct()

This will make a JOIN on the table of the TourDatesInfo model, and filter on the departure_date and return_date. If there is thus such TourDatesInfo, it will return the Tour data. The .distinct() call ^[Django-doc] is used to prevent returning the same Tour for as many times as there are matching TourDatesInfos.

Answered By - Willem Van Onsem