How to filter joined models in Django?

Question

I have the following models:

class Street(models.Model):
    name = models.CharField(max_length=40)

class House(models.Model):
    street = models.ForeignKey(Street, models.PROTECT)
    number = models.CharField(max_length=10)

    class Meta:
        unique_together = ('street', 'number')

class Room(models.Model):
    house = models.ForeignKey(House, models.PROTECT)
    number = models.CharField(max_length=10)

    class Meta:
        unique_together = ('house', 'number')

I already know the ID of a Street and would like to get all the rooms of all the houses in that street. In SQL that is easy:

SELECT *
FROM room JOIN house ON house.id = room.house_id
WHERE house.street_id = xyz;

Now how do I do this in Django? I tried

Room.objects.select_related('house').filter(street=xyz)

But I get an exception saying I can't access this field:

django.core.exceptions.FieldError: Cannot resolve keyword 'street' into field. Choices are: house, house_id, id, number

Because of the amounts of data I am facing, I would really like to be able to join and filter using a single query! When giving up one or the other, I would have to resort to either making multiple queries or filtering in Python, both of which are inherently inefficient. An alternative would be raw queries, I guess...

Answer 1

You can get access to related object's field with __ syntax:

Room.objects.select_related('house').filter(house__street=xyz)

This can be done as much time as you need, to select rooms by street name you can do this:

Room.objects.select_related('house').filter(house__street__name=xyz)

Chech details here.