CODEWITHSUNDEEP
sqldjangoormdjango-orm
drakenation
drakenation

Reputation: 412

How to filter all objects that contain multiple elements in a many-to-many relationship using Django's ORM?

I have two classes in Django linked through a ManyToManyField (the User class is the built-in User model):

from django.contrib.auth.models import User

class Activity():
    participants = models.ManyToManyField(User, related_name='activity_participants')

I want to find all the activities in which two users are simultaneously participating.

I managed to solve my problem using a raw query (my app name is "core", therefore the "core" prefix in the table names):

SELECT ca.id FROM core_activity_participants AS cap, core_activity AS ca
INNER JOIN core_activity_participants AS cap2 ON cap.activity_id
WHERE cap.user_id == 1 AND cap2.user_id == 2
    AND cap.activity_id == cap2.activity_id
    AND ca.id == cap.activity_id

However, if possible, I'd like to avoid using raw queries, since it breaks uniformity from the rest of my app. How could I make this query, or one equivalent to it, using Django's ORM?

Upvotes: 1

Views: 84

Answers (1)

Kevin
Kevin

Reputation: 959

If you're using Django 1.11 or later the intersection queryset method will give you the records you want.

# u1 and u2 are User instances
u1_activities = Activity.objects.filter(participants=u1)
u2_activities = Activity.objects.filter(participants=u2)
common_activities = u1_activities.intersection(u2_activities)

Will produce a query something like this:

SELECT "core_activity"."id"
FROM "core_activity"
INNER JOIN "core_activity_participants"
ON ("core_activity"."id" = "core_activity_participants"."activity_id")
WHERE "core_activity_participants"."user_id" = 1
INTERSECT
SELECT "core_activity"."id"
FROM "core_activity"
INNER JOIN "core_activity_participants"
ON ("core_activity"."id" = "core_activity_participants"."activity_id")
WHERE "core_activity_participants"."user_id" = 2

You can also add extra querysets to the intersection if you want to check for activity overlap between more than 2 users.

Update:

Another approach, which works with older Django versions, would be

u1_activities = u1.activity_participants.values_list('pk', flat=True)
common_activities = u2.activity_participants.filter(pk__in=u1_activities)

Which produces a query like

SELECT "core_activity"."id"
FROM "core_activity"
INNER JOIN "core_activity_participants"
ON ("core_activity"."id" = "core_activity_participants"."activity_id")
WHERE (
    "core_activity_participants"."user_id" = 2
    AND "core_activity"."id" IN (
        SELECT U0."id"
        FROM "core_activity" U0
        INNER JOIN "core_activity_participants" U1
        ON (U0."id" = U1."activity_id")
        WHERE U1."user_id" = 1
    )
)

Upvotes: 1

Related Questions