How do you do multiple joins simultaneously in Django ORM?

Question

Given a bunch of graphical maps, I need to query which maps a user has access to, either because he owns the map or because he has been granted 'membership' of it.

There is a Map_desc (Map description) model, and for each object in Map_desc, potentially many Mapmembership objects.

Because it will later be fed to ModelChoiceFields in templates, I need a single QuerySet that will return all the Map_desc objects that have related Mapmembership objects, plus all the Map_desc objects that have 'owner' set to the current user.

It's a simple join, but surprisingly difficult to pull off in Django.

models.py (simplified)

class Map_desc(models.Model):
  owner = models.ForeignKey(User, null=False, on_delete=models.CASCADE)

class Mapmember(models.Model):
  member = models.ForeignKey(User, null=False, on_delete=models.CASCADE)
  ofmap = models.ForeignKey(Map_desc, null=False, on_delete=models.CASCADE)

What I tried first that didn't work (in semi-pseudo code):

shared = Map_desc.objects.filter(id=mapmember_set.ofmap)

then a second query to get the Maps owned:

owned = Map_desc.objects.filter(owner=thisUser)

and tying them together with

accessiblemaps = chain(shared,owned)

The problem is that because there are multiple Map_desc objects, mapmember_set is not actually available. It does work if you limit the query to just one Map_desc, using .first(), and this is what all the tutorials and SO questions I've found do.

So I came up with the following solution, which does work, but I'm wondering if there are more efficient ways, or just different ways that would help me understand how the ORM is meant to be used for Joins involving multiple "parent object" rows.

views.py

from django.db.models import Q

def all_accessible_maps(request, thisUser):
  # Returns maps thisUser has access to, whether owned or as a 'member'
  try:
    sharedmaps = Mapmember.objects.filter(member=thisUser).values_list('ofmap')
    allaccessible = Map_desc.objects.filter(Q(id__in=sharedmaps) | Q(owner=thisUser))    
  except:
    raise

all_accessible_maps.alters_data = True

Looks like you could have used a ManyToManyField to represent the membership relationship — Benjamin Toueg, Feb 07 '18 at 08:40

neverwalkaloner · Accepted Answer · 2018-02-07T09:34:01.313

1

You can just add Q(mapmember__member=thisUser), the __ will do join with Mapmember table:

Map_desc.objects.filter(Q(owner=thisUser) | Q(mapmember__member=thisUser))

edited Feb 07 '18 at 09:34

answered Feb 07 '18 at 08:42

neverwalkaloner

46,181
7
92
100

If I understand correctly, you could own a map without ever being a member of any map. In this case you would miss some entries – Benjamin Toueg Feb 07 '18 at 08:50
@BenjaminToueg hm.. I think it shoud work with this case also. Query have or condition and return record for any of them. – neverwalkaloner Feb 07 '18 at 08:54
On second thought, I'm pretty sure it won't work. If you think I'm wrong, please elaborate – Benjamin Toueg Feb 07 '18 at 08:59
@BenjaminToueg oh, I see I selected Mapmember instead of Map_desc. New version should work. – neverwalkaloner Feb 07 '18 at 09:35
This works, and saves a query. I've accepted it as an answer. Thanks @neverwalkaloner. Still having a hard time visualizing how it works since 'mapmember' is a separate table, yet the query returns Map_desc table objects. There's no explicit mention of a shared key between them in the query. I'm used to mentally visualizing null values in a join between two tables. – Ron Apr 13 '18 at 17:04
Uh oh, if the owner of a map has 'membership' of it (which is proper), and also another user does, too, the map will show up twice using this query. This doesn't happen with the method used in the question. The SQL generated using @neverwalkaloner 's answer is: SELECT (blah, blah) FROM "map_desc" LEFT OUTER JOIN "mapmember" ON ("map_desc"."id" = "mapmember"."map_id") WHERE (("map_desc"."owner_id" = 3) OR ("mapmember"."member_id" = 3)). Easy enough to clean up with unique(), though that seems a little like cheating. – Ron Apr 14 '18 at 15:38
The SQL generated by the original solution uses a subselect, which gives slightly different results!: SELECT (blah, blah) FROM "map_desc" WHERE ("map_desc"."id" IN (SELECT U0."map_id" FROM "mapmember" U0 WHERE (U0."member_id" = 3)) OR ("map_desc"."owner_id" = 3)) – Ron Apr 14 '18 at 15:44
Supposedly left outer joins are [generally faster than subselects](https://stackoverflow.com/questions/2577174/join-vs-sub-query), so the @neverwalkaloner answer is still both faster and more readable. – Ron Apr 14 '18 at 15:49

How do you do multiple joins simultaneously in Django ORM?

1 Answers1