Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Many to many lookups in Django

This is probably insultingly simple and worthy of a Nelson Muntz laugh, but I'm having a real braindead moment tryng to make many to many connections across various model relationships.

I have the following models (simplified for your enjoyment!):

class Document(models.Model):
    title = models.CharField(max_length=200)
    author = models.ForeignKey(User, blank=True)
    content = models.TextField(blank=True)
    private = models.BooleanField(default=False)

class UserProfile(models.Model):
    user = models.ForeignKey(User, unique=True)
    friends = models.ManyToManyField(User, symmetrical=False, 
                                     related_name='user_friends')
    ignored = models.ManyToManyField(User, symmetrical=False, 
                                     related_name='user_ignored')

Imaginging the following Users:

  • Alice has 3 documents, 1 of which is private (meaning only friends can see it). She is friends with Bob, is ignoring Mallory and is apathetic towards Eve (meaning no stored relationship).
  • Mallory has 2 documents, both public and is apathetic towards everyone.
  • Bob has 1 document which is public and is also apathetic towards everyone.
  • Eve is ignoring Alice and is apathetic to Mallory and Bob

Users searching for documents should produce the following:

  • Bob searching for documents should see 6, as Alice has made him a friend and he can view her private documents.
  • Alice searching for documents should see 4, Bobs 1 and her 3. She doesn't see Mallory's public documents as Alice is ignoring Mallory.
  • Mallory searching for documents sees 5 - Alice's public ones, her own 2 and Bobs 1. Alice ignoring her has no bearing on what Mallory can see, just that Alice doesn't see Mallory's docs.
  • Eve searching for documents sees 3 - Mallory and Bob's public documents as she has ignored Alice.

Basically, I'm having a mental struggle figuring out the filters to returning the querysets I described above. Anyone got any ideas?

EDIT

Thanks to Ferdinands answer below I was able to nut through to what I wanted with the start that he gave me. First off, we want to get a list of people who have friended me which is a reverse lookup through the Many to Many relationship:

friendly_authors = self.user.user_friends.all()

Get all the people I've ignored:

my_ignored = UserProfile.objects.get(user=self.user).ignored.all()

Get a list of docs I can view - docs which are viewable, mine, or written by people who have friended me but whom I haven't ignored:

docs = Document.objects.filter(
    (Q(viewable=True) | Q(author=self.user) | Q(author__in=friendly_authors))
     & ~Q(author__in=my_ignored)
)
like image 964
Steerpike Avatar asked Aug 23 '09 11:08

Steerpike


1 Answers

It is a bit tricky, maybe you are looking for something like that:

>>> from django.db.models import Q
>>> me = User.objects.get(pk=1)
>>> my_friends = UserProfile.objects.get(user=me).friends.all()
>>> docs = Document.objects.filter(
...     Q(author=me) | (
...         Q(author__in=my_friends)
...         & ~Q(author__userprofile__ignored=me)
...     )
... )

This generates the following SQL (I did some formatting on the original output):

SELECT "myapp_document".*
FROM "myapp_document" WHERE (
    "myapp_document"."author_id" = %s
    OR (
        "myapp_document"."author_id" IN (
            SELECT U0."id" FROM "myapp_user" U0
            INNER JOIN "myapp_userprofile_friends" U1
                ON (U0."id" = U1."user_id")
            WHERE U1."userprofile_id" = %s
        )
        AND NOT (
            "myapp_document"."author_id" IN (
                SELECT U2."user_id" FROM "myapp_userprofile" U2
                INNER JOIN "myapp_userprofile_ignored" U3
                    ON (U2."id" = U3."userprofile_id")
                WHERE U3."user_id" = %s
            )
            AND "myapp_document"."author_id" IS NOT NULL
        )
    )
)
like image 78
Ferdinand Beyer Avatar answered Oct 11 '22 19:10

Ferdinand Beyer