Filtering down through multiple ForeignKey relations in django

Question

I am trying to get down through multiple-foreign key relationship where each Hotel has many Rooms, each Room has many Rateplans, each Rateplan has many Prices.

It resembles Christmas tree if you think about it:

Hotel
v
Room
v
Rateplan
v
Prices

How can I execute query that will return hotels in certain hotel_city on certain price_date, certain price_price and certain price_availability? (just like Expedia or booking.com query)

For example:

Hotel.objects.filter(city='Beirut').filter(room__name=Room.objects.filter(rateplan__name=Rateplan.objects.filter(prices__availability=Prices.objects.filter(availability=1))))

I have looked into django complex queries documentation and annotate/aggregate but couldn't wrap my head around it.

My models below:

class Hotel(models.Model):
    name = models.CharField(max_length=64)
    city = models.CharField(max_length=64, default='Warsaw')

class Room(models.Model):
    hotel_id = models.ForeignKey(Hotel, on_delete=models.CASCADE, related_name='room')
    name = models.CharField(max_length=64, default='Double')

class Rateplan(models.Model):
    room_id = models.ForeignKey(Room, on_delete=models.CASCADE, related_name='rateplan')
    name = models.CharField(max_length=64, default='Standard')

class Prices(models.Model):
    rateplan_id = models.ForeignKey(Rateplan, on_delete=models.CASCADE, related_name='prices')
    date = models.DateField()
    price_1 = models.DecimalField(null=False, max_digits=7, decimal_places=2)
    price_2 = models.DecimalField(null=False, max_digits=7, decimal_places=2)
    availability = models.SmallIntegerField()```

Answer 1

You can use __ to filter the values you need across relationships

There are good examples in the documentation

Hotel.objects.filter(
    city=your_city,
    room__rateplan__prices__date=your_date,
    room__rateplan__prices__price_1=your_price,
    room__rateplan__prices__availability=your_availability,
).distinct()