TL;DR: Is there a way to inner join two related querysets if they are based on different models?
Suppose I'm trying to manage access controls for a filesystem, and I have models for User, Folder, and File. A User can access a File if it is public, OR if they can access the File's Folder. I already have a Django queryset that includes all Folders for a User:
# I already have this queryset...
user_folders = Folder.objects.filter(
Q(is_public=True) | Q(owner=user) | Q(folder_shares__user=user),
deleted_at__isnull=True,
)
Now the problem: I also want a queryset that includes all Files for a User. And I want to recycle the users_folders queryset so I don't have to rewrite all the same conditions again.
# ...so I don't want to rewrite the same conditions for this queryset
user_files = File.objects.filter(
Q(is_public=True)
| Q(folder__is_public=True)
| Q(folder__owner=user)
| Q(folder__folder_shares__user=user),
deleted_at__isnull=True,
folder__deleted_at__isnull=True,
)
This is a simplified example. In my real codebase, the two querysets are much more complicated, and it's a 3-level hierarchy. So currently, if the sharing rules change, I have to remember to update 3 different complex queries across the app. That's easy to screw up, and the costs of getting it wrong are really high. It seems like there must be a better way.
As a mediocre solution, I considered doing something like this:
# This is inefficient if `user_folders` contains many results
user_files = File.objects.filter(
Q(is_public=True) | Q(folder__in=user_folders),
deleted_at__isnull=True,
)
But this actually executes two queries and the first query pulls everything from user_folders into memory. In my app there are many thousands of public folders, so doing this for every request seems painfully inefficient.
This is just an inner join between two different querysets, so I'm surprised that I haven't been able to find any practical/efficient way to do this. Am I missing something?