Order of combined Q objects seems to matter when querying against the same relationship

Using Django Using the ORM
1

Hello!

I’m building an app that is using a bunch of Q objects to create user-specified filters. I have now an issue that also exhibits when creating Q objects on the shell, and I would appreciate any kinds of clarifications what’s happening.

Here are my models, with only the fields I’m querying against.

from django.db import models
from django.db.models import Q

class ArticleTag(models.Model):
    key = models.CharField(max_length=255, unique=True)

class Article(models.Model):
    tags = models.ManyToManyField(ArticleTag)

When I create models and associate tags, like this (I have a lot more in my DB).

sport = ArticleTag.objects.create(key="sport")
football = ArticleTag.objects.create(key="football")
skating = ArticleTag.objects.create(key="skating")
economy = ArticleTag.objects.create(key="economy")
Article.objects.create(tags=[sport, football])
Article.objects.create(tags=[sport, skating])
Article.objects.create(tags=[economy, football])

I want to get all articles that have the tag “sport” but not the tag “football”. I can’t revert to using filter and exclude in this, since I’m not writing the queries in Python code in my real use case, and Q objects serve as a nice way of moving the filters across my Django app’s modules.

sport_without_football = Article.objects.filter(Q(tags__key__in=["sport"]) & ~Q(tags__key__in=["football"]))
sport_without_football_b = Article.objects.filter(~Q(tags__key__in=["football"]) & Q(tags__key__in=["sport"]))
print([[tag.key for tag in art.tags.all()] for art in sport_without_football.all()])
# some of these have "football"
print([[tag.key for tag in art.tags.all()] for art in sport_without_football_b.all()])
# here none have "football", which is what I'm expecting.

So, if I combine the Qs one way, it works like I’m expecting, but not the other way around.

Is the order of arguments intentionally significant? Are the ~ and & operators an appropriate way to achieve this? I’m running Django 4.1.13, psycopg2 2.9.5 and PostgreSQL 15.2.

2

Hello @svkoskin, I suspect you might be running into this issue

Could you share the generated SQL of both queries by doing

print(str(Article.objects.filter(Q(tags__key__in=["sport"]) & ~Q(tags__key__in=["football"])).query))
print(str(Article.objects.filter(~Q(tags__key__in=["football"]) & Q(tags__key__in=["sport"])).query))
3

Hi, thanks for your response.

Here’s the generated SQL for both, with some field names removed and prettified.

>>> print(str(Article.objects.filter(Q(tags__key__in=["sport"]) & ~Q(tags__key__in=["football"])).query))
SELECT
    "app_article"."id"
FROM
    "app_article"
    INNER JOIN "app_article_tags" ON (
        "app_article"."id" = "app_article_tags"."article_id"
    )
    INNER JOIN "app_articletag" ON (
        "app_article_tags"."articletag_id" = "app_articletag"."id"
    )
WHERE
    (
        "app_articletag"."key" IN (sport)
        AND NOT (
            EXISTS(
                SELECT
                    1 AS "a"
                FROM
                    "app_article_tags" U1
                    INNER JOIN "app_articletag" U2 ON (U1."articletag_id" = U2."id")
                WHERE
                    (
                        U2."key" IN (football)
                        AND U1."id" = ("app_article_tags"."id")
                        AND "app_article_tags"."article_id" = ("app_article"."id")
                    )
                LIMIT
                    1
            )
        )
    )

    "app_article"."id"
FROM
    "app_article"
    INNER JOIN "app_article_tags" ON (
        "app_article"."id" = "app_article_tags"."article_id"
    )
    INNER JOIN "app_articletag" ON (
        "app_article_tags"."articletag_id" = "app_articletag"."id"
    )
WHERE
    (
        NOT (
            EXISTS(
                SELECT
                    1 AS "a"
                FROM
                    "app_article_tags" U1
                    INNER JOIN "app_articletag" U2 ON (U1."articletag_id" = U2."id")
                WHERE
                    (
                        U2."key" IN (football)
                        AND U1."article_id" = ("app_article"."id")
                    )
                LIMIT
                    1
            )
        )
        AND "app_articletag"."key" IN (sport)
    )
4

Thanks you are definitely running into the aforementioned issue so I suggest using Exists directly instead

author_tags = Article.tags.through.objects.filter(
    article=OuterRef("pk")
)
Author.objects.filter(
    Exists(author_tags.filter(articletag__key__in=["sport"]),
    ~Exists(author_tags.filter(articletag__key__in=["football"]),
)

I wish the ORM allowed for an __n?exists lookup to reduce the boilerplate here

Author.objects.filter(
    tags__exists=Q(key__in=["sport"])
    tags__nexists=Q(key__in=["football"]),
)

but it doesn’t.

The usage of Exists should also avoid duplicate Article behind returned if your additive lookup ( e.g. Q(key__in=["sport", "cooking"]) matches many tags.

5

Thanks! I’ll check Exists out.

6

I think you should use exclude, not Q object.

port_without_football = Article.objects\
  .filter(tags__key__in=["sport"])\
  .exclude(tags__key__in=["football"])