[Fixed]-Many to many lookups in Django

9👍

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

Leave a comment