2

There are 2 models connected with a many-to-many relationship.

class Record():
    working_on_record = models.ManyToManyField(
        UserProfile, related_name="working_on_record", blank=True)

class UserProfile():
    name = CharField...

The UserProfile is just a basic user model.

Now I want to query a search for records and check if all keywords are matched but if I do:

query = Q()
c1 = UserProfile.objects.filter(name__icontains='peter')
query.add(Q(working_on_record__in=c1), Q.AND)

c2 = UserProfile.objects.filter(name__icontains='john')
query.add(Q(working_on_record__in=c2), Q.AND)

set3 = list(models.Record.objects.filter(query))

Set3 is empty, despite there is a record which has two people working on it whose names are "john smith" and "peter parker". If I replace "john" in c2 by "parker" I get all records where "peter parker" is working on it.

But if I chain the filter methods:

set4 = list(models.Record.objects
            .filter(working_on_record__in=c1)
            .filter(working_on_record__in=c2)
       )

set4 is not empty and contains the record I want.

Is it possible to change the Q-query so that I get the behavior I want? Is it possible to filter with a Q-query on one many-to-many-relationship with different values from different objects connected with an AND?

I don't like the idea of chaining the filter methods, because of multiple possible usages of the Q-query, so I would have to iterate at multiple occasions through all queries and chain them.

Sherpa
  • 1,948
  • 13
  • 25
Jehob
  • 101
  • 7
  • That is logical, in the former you specify that the *same* `working_on_record` should be a member of both sets. – Willem Van Onsem Oct 31 '18 at 10:07
  • I don't understand, could you explain it with an example? I want to get the records, on which "john" is working on **and** "peter" is working on, but they are two distinct users, which apparently doesn't work, because the Q-query searches for "john" and "peter" **on the same** user. – Jehob Oct 31 '18 at 10:24
  • 1
    By using `Model.objects.filter(foo=bar).filter(foo=qux)`, you make *two* joins, and thus you state "Look for Model objects where there is a foo that is bar, and a foo that is qux), whereas a filter `Model.objects(Q(foo=bar) & Q(foo=qux))` says, "look for a Model whit a related Foo that is both bar and qux". – Willem Van Onsem Oct 31 '18 at 10:33
  • Ok thank you very much! But is there a way to achieve joining with Q? – Jehob Oct 31 '18 at 10:41
  • Do you need the created Q object later on or do you only use it for the one query? – CoffeeBasedLifeform Oct 31 '18 at 18:05
  • I use it for one or two queries depending on the user permissions. I need the q object itself, because I have some ORs which are connected (to search fulltext in multiple fields of the record). – Jehob Oct 31 '18 at 21:02

0 Answers0