CODEWITHSUNDEEP
djangogroup-bydistinctdjango-ormhaving
Erik Kirkebø
Erik Kirkebø

Reputation: 21

Django queryset - column IN() GROUP BY HAVING COUNT DISTINCT

With the following models:

class Post(models.Model):

    class Meta:
        db_table = "posts"

class Tag(models.Model):
    tag = models.CharField(max_length=50)

   class Meta:
       db_table = "tags"

class PostTag(models.Model):
    postid = models.PositiveIntegerField()
    tagid = models.PositiveIntegerField()

    class Meta:
        unique_together = ("postid", "tagid")
        db_table = "posttags"

To get postids of posts which contain all the tagids given in TAGLIST where TAGLEN is the number of tagids in TAGLIST:

SELECT postid
FROM posttags
WHERE tagid IN (TAGLIST)
GROUP BY postid
HAVING COUNT(DISTINCT tagid) = TAGLEN

But how do I do this with Django ORM?

Upvotes: 1

Views: 443

Answers (1)

Erik Kirkebø
Erik Kirkebø

Reputation: 21

I found a solution.

    TAGLEN = TAGLIST.count()
    withtags = PostTag.objects.filter(tagid__in=TAGLIST)
    withall = withtags.values("postid").annotate(tagtotal=Count("tagid", distinct=True)).order_by()
    withall.filter(tagtotal=TAGLEN).values_list("postid", flat=True)

And running .query.__str__() on all this returns basically the following SQL below.

SELECT "postid"
FROM "posttags"
WHERE "tagid" IN (TAGLIST)
GROUP BY "postid"
HAVING COUNT(DISTINCT "tagid") = TAGLEN'

Upvotes: 1

Related Questions