Django Combining AND and OR Queries with ManyToMany Field

Question

hoping someone can help me out with this.

I'm trying to figure out whether I can construct a query that will allow me to retrieve items from my db based on a ForeignKey field and a ManyToManyField at the same time. The challenging part is that it will need to filter on multiple ManyToMany objects.

An example will hopefully make this clearer. Here are my (simplified) models:

class Item(models.Model):
    name = models.CharField(max_length=200)
    brand = models.ForeignKey(User, related_name='brand')
    tags = models.ManyToManyField(Tag, blank=True, null=True)
    def __unicode__(self):
        return self.name
    class Meta:
        ordering = ['-id']

class Tag(models.Model):
    name = models.CharField(max_length=64, unique=True)
    def __unicode__(self):
        return self.name

I would like to build a query that retrieves items based on two criteria:

1. Items that were uploaded by users that a user is following (called 'brand' in the model). So for example if a user is following the Paramount user account, I would want all items where brand = Paramount.

2. Items that match the keywords in saved searches. For example the user could make and save the following search: "80s comedy". In this case I would want all items where the tags include both "80s" and "comedy".

Now I know how to construct the query for each independently. For #1, it's:

items = Item.objects.filter(brand=brand)

And for #2 (based on the docs):

items = Item.objects.filter(tags__name='80s').filter(tags__name='comedy')

My question is: is it possible to construct this as a single query so that I don't have to take the hit of converting each query into a list, joining them, and removing duplicates?

The challenge seems to be that there is no way to use Q objects to construct queries where you need an item's manytomany field (in this case tags) to match multiple values. The following query:

items = Item.objects.filter(Q(tags__name='80s') & Q(tags__name='comedy')) 

does NOT work.

(See: https://docs.djangoproject.com/en/dev/topics/db/queries/#spanning-multi-valued-relationships)

Thanks in advance for your help on this!

Answer 1

After much research I could not find a way to combine this into a single query, so I ended up converting my QuerySets into lists and combining them.

Answer 2

Django's filters automatically AND. Q objects are only needed if you're trying to add ORs. Also, the __in query filter will help you out alot.

Assuming users have multiple brands they like and you want to return any brand they like, you should use:

`brand__in=brands`

Where brands is the queryset returned by something like someuser.brands.all().

The same can be used for your search parameters:

`tags__name__in=['80s','comedy']`

That will return things tagged with either '80s' or 'comedy'. If you need both (things tagged with both '80s' AND 'comedy'), you'll have to pass each one in a successive filter:

keywords = ['80s','comedy']
for keyword in keywords:
    qs = qs.filter(tags__name=keyword)

P.S. related_name values should always specify the opposite relationship. You're going to have logic problems with the way you're doing it currently. For example:

brand = models.ForeignKey(User, related_name='brand')

Means that somebrand.brand.all() will actually return Item objects. It should be:

brand = models.ForeignKey(User, related_name='items')

Then, you can get a brands's items with somebrand.items.all(). Makes much more sense.