CODEWITHSUNDEEP
djangopython-3.xpostgresqldjango-models
DorZ
DorZ

Reputation: 364

Django ManyToManyField query equalivent to a list

I have a list of objects and a model includes ManyToManyField of this object. I'd like to get objects who have the same list of objects in this field. that's means __in won't work because he will do OR between the objects in the list and not AND. I was trying using the AND Q Lookup, but it didn't work (after checking the .query text, it seems that he doing the AND inside the field object, instead of the object itself, so obviously the field object id don't have two different ids..)

here's the interceptor I played with

results_query_set[0]
<Results: Results object (2)>
results_query_set[0].users.all()
<QuerySet [<User: test_user>, <User: test_user_2>]>
users
[<User: test_user>, <User: test_user_2>]
users_q
<Q: (AND: ('users', <User: test_user>), ('users', <User: test_user_2>))>
results_query_set.filter(users=users[0])
<QuerySet [<Results: Results object (2)>]>
results_query_set.filter(users=users[1])
<QuerySet [<Results: Results object (2)>]>
results_query_set.filter(users_q)
<QuerySet []>
results_query_set.filter(Q(users=users[0]) & Q(users=users[1]))
<QuerySet []>

and the result results_query_set.filter(users_q).query.__str__() reproduce is

'SELECT "results_table"."id", "results_table"."date", "results_table"."lookup", "results_table"."value" FROM "results_table" INNER JOIN "results_table_users" ON ("results_table"."id" = "results_table_users"."widgetresults_id") WHERE ("results_table_users"."user_id" = 1 AND "results_table_users"."user_id" = 2)

I can chain .filter for each user, but of course, I'd like to make one query instead of queries by the numbers of my input.

Upvotes: 0

Views: 81

Answers (2)

Todor
Todor

Reputation: 16010

You need to JOIN the target table (User in your case) multiple times (for every single user) in order to build such a query.

The way to do this in Django is by calling .filter multiple times.

users = [user1, user2] # a list of users your are interested to filter on
initial_qs = Results.objects.all() # or whatever your results_query_set is
result_qs = reduce(lambda qs, user: qs.filter(users=user.id), users, initial_qs)

# At this point you will have results containing user1 and user2
# but this also includes results with more users (e.g. users 1, 2 and 3)
# if you want to exclude those, you need to filter by the total users count too
result_qs = result_qs.annotate(cnt=models.Count('users')).filter(cnt=len(users))

Upvotes: 2

schillingt
schillingt

Reputation: 13731

I have not tried this, but I think you might be able to use postgresql's array_agg function. The Django implementation is here.

from django.contrib.postgres.aggregates import ArrayAgg

ideal_user_list = [] # some list. 
                     # Or fetch it directly from the db using the below query and `get`
Results.objects.annotate(
    related_user_array=ArrayAgg('users__id', ordering='users__id')
).filter(related_user_array=ideal_user_list)

Upvotes: 0

Related Questions