Convert a raw self join sql to Django orm code (no internal foreign key)

user_1660013 0
Viewed 108

I have Article and Tag models, having a many to many relation through another model of ArticleTag. I want to find that for a given tag "Health", how many times it's been simultaneously with other tags on articles. (e.g. on 4 articles with tag "Covid", on 0 articles with tag "Infection" , etc)

I can perform a self join on ArticleTag with some Where conditions and Group By clause and get the desired result:

SELECT tag.title, COUNT(*) as co_occurrences
FROM app_articletag as t0
INNER JOIN app_articletag t1 on (t0.article_id = t1.article_id)
INNER JOIN app_tag tag on (tag.id = t1.tag_id)
WHERE t0.tag_id = 43 and t1.tag_id != 43
GROUP BY t1.tag_id, tag.title

However I want to stay away from raw queries as much as possible and work with Django QuerySet APIs.

I've seen other threads about self join, but their model all have a foreign key to itself.

Here are my Django models:

class Tag(Model):
    ...

class Article(Model):
    tags = models.ManyToManyField(Tag, through='ArticleTag', through_fields=('article', 'tag'))

class ArticleTag(Model):
    tag = models.ForeignKey(Tag, on_delete=models.CASCADE)) 
    article = models.ForeignKey(Article, on_delete=models.CASCADE))
django-queryset django-orm django
1 Answers

One approach is something like this, given t is the Health tag:

ArticleTag.objects.values(
    "tag__name"
).annotate(
    articles_with_health=Count(
        "pk", filter=Q(article__articletag__tag=t)
    )
).exclude(tag=t)

This should return a result like:

[
    {'tag__name': 'Infection', 'articles_with_health': 0}, 
    {'tag__name': 'Covid', 'articles_with_health': 4}
]
user_67598440
Related