How to execute requests in Django after a double connection (or: How to get around Django restrictions for ManyToMany through "models"?)

Question

There should be a way to make this request through ORM, but I do not see it.

Customization

Here is what I am modeling: one Tenant can occupy several rooms, and one User can own several rooms. Therefore, the rooms have FK for the tenant and FK for the user. Numbers are also supported by the user (possibly separate).

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) 

Problem

Given the Tenant, I want the Users to have a room in which they occupy.

The corresponding 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 a single value from related user objects can be done, for example, using my_tenant.rooms.values_list('owner__email', flat=True) , but getting a full set of user requests disconnects me.

Normally, one way to solve this problem would be to set the ManyToMany field on my Tenant model, pointing to User with TenantRoom as an “end-to-end” model. However, in this case this will not work, because the TenantRoom model has a second (unrelated) ForeignKey to User ( see the section "stops" ). Plus it seems like a useless mess on the Tenant's model.

Running my_tenant.rooms.values_list('user', flat=True) brings me closer, but returns the ValuesListQuerySet value of the user IDs, not a set of queries on User objects.

Question

So: is there a way to get a set of queries on actual model instances through ORM using only one query?

---

Edit

If there really is no way to do this directly in one request through ORM, then what is the best (some combination of the most perfect, most idiomatic, most read, etc.) way to accomplish what I'm in the search for? Here are the options I see:

• subselection

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

• Python subquery (see performance considerations for an explanation of this)

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

• 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""") 

• Others ...

Answer 1

The correct 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)