Stack Exchange
Django connected SQL queries with filters
Example:
class Room(models.Model):
    assigned_floor = models.ForeignKey(Floor, null=True, on_delete=models.CASCADE)
    room_nr = models.CharField(db_index=True, max_length=4, unique=True, null=True)
    locked = models.BooleanField(db_index=True, default=False)
    last_cleaning = models.DateTimeField(db_index=True, auto_now_add=True, null=True)
    ...


class Floor(models.Model):
    assigned_building = models.ForeignKey(Building, on_delete=models.CASCADE)
    wall_color = models.CharField(db_index=True, max_length=255, blank=True, null=True)
    ...


class Building(models.Model):
    name = models.CharField(db_index=True, max_length=255, unique=True, null=True)
    number = models.PositiveIntegerField(db_index=True)
    color = models.CharField(db_index=True, max_length=255, null=True)
    ...

I want to output all rooms in a table sorted by Building.number.
Data which I want to print for each room:
Building.number, Building.color, Building.name, Floor.wall_color, Room.last_cleaning
Furthermore I want to allow optional filters:
Room.locked, Room.last_cleaning, Floor.wall_color, Building.number, Building.color
With one table it's no Problem for me, but I don't know how I archive this with three tables.
kwargs = {'number': 123}
kwargs['color'] = 'blue'
all_buildings = Building.objects.filter(**kwargs).order_by('-number')

Can you please help me? Do I need write raw SQL queries or can I archive this with the Django model query APIs?
I'm using the latest Django version with PostgreSQL.

No raw sql needed:
room_queryset = Room.objects.filter(assigned_floor__wall_color='blue')
                                                  ^^
# A double unterscore declares the following attribute to be a field of the object referenced in the foregoing foreign key field.

for room in room_queryset:
    print(room.assigned_floor.assigned_building.number)
    print(room.assigned_floor.assigned_building.color)
    print(room.assigned_floor.assigned_building.name)
    print(room.assigned_floor.wall_color)
    print(room.last_cleaning)

