How to perform queries in Django following double-join relationships (or: How to get around Django's restrictions on ManyToMany "through" models?)

Question

There must be a way to do this query through the ORM, but I'm not seeing it.

The Setup

Here's what I'm modelling: one Tenant can occupy multiple rooms and one User can own multiple rooms. So Rooms have an FK to Tenant and an FK to User. Rooms are also maintained by a (possibly distinct) User.

That is, I have these (simplified) models:

class Tenant(models.Model):
    name = models.CharField(max_length=100)

class Room(models.Model):
    owner = models.ForeignKey(User)
    maintainer = models.ForeignKey(User)
    tenant = models.ForeignKey(Tenant)

The Problem

Given a Tenant, I want the Users owning a room which they occupy.

The relevant SQL query would be:

SELECT auth_user.id, ...
FROM tenants_tenant, tenants_room, auth_user
WHERE tenants_tenant.id = tenants_room.tenant_id
AND tenants_room.owner_id = auth_user.id;

Getting any individual value off the related User objects can be done with, for example, my_tenant.rooms.values_list('owner__email', flat=True), but getting a full queryset of Users is tripping me up.

Normally one way to solve it would be to set up a ManyToMany field on my Tenant model pointing at User with TenantRoom as the 'through' model. That won't work in this case, though, because the TenantRoom model has a second (unrelated) ForeignKey to User(see "restictions"). Plus it seems like needless clutter on the Tenant model.

Doing my_tenant.rooms.values_list('user', flat=True) gets me close, but returns a ValuesListQuerySet of user IDs rather than a queryset of the actual User objects.

The Question

So: is there a way to get a queryset of the actual model instances, through the ORM, using just one query?

---

Edit

If there is, in fact, no way to do this directly in one query through the ORM, what is the best (some combination of most performant, most idiomatic, most readable, etc.) way to accomplish what I'm looking for? Here are the options I see:

1. Subselect

   users = User.objects.filter(id__in=my_tenant.rooms.values_list('user'))
   

2. Subselect through Python (see Performance considerations for reasoning behind this)

   user_ids = id__in=my_tenant.rooms.values_list('user')
   users = User.objects.filter(id__in=list(user_ids))
   

3. Raw SQL:

   User.objects.all("""SELECT auth_user.*
   FROM tenants_tenant, tenants_room, auth_user
   WHERE tenants_tenant.id = tenants_room.tenant_id
   AND tenants_room.owner_id = auth_user.id""")
   

4. Others...?

Answer 1

The proper way to do this is with related_name:

class Tenant(models.Model):
    name = models.CharField(max_length=100)

class Room(models.Model):
    owner = models.ForeignKey(User, related_name='owns')
    maintainer = models.ForeignKey(User, related_name='maintains')
    tenant = models.ForeignKey(Tenant)

Then you can do this:

jrb = User.objects.create(username='jrb')
bill = User.objects.create(username='bill')
bob = models.Tenant.objects.create(name="Bob")
models.Room.objects.create(owner=jrb, maintainer=bill, tenant=bob)

User.objects.filter(owns__tenant=bob)