Tortoise ORM filter with logical operators

user_9848549 0
Viewed 3207

I have two tables

class User(models.Model):
    id = fields.BigIntField(pk=True)
    name = CharField(max_length=100)
    tags: fields.ManyToManyRelation["Tag"] = fields.ManyToManyField(
        "models.Tag", related_name="users", through="user_tags"
    )

class Tag(models.Model):
    id = fields.BigIntField(pk=True)
    name = fields.CharField(max_length=100)
    value = fields.CharField(max_length=100)
    users: fields.ManyToManyRelation[User]

Let's assume this dummy data

#users
bob = await User.create(name="bob")
alice = await User.create(name="alice")

#tags
foo = await Tag.create(name="t1", value="foo")
bar = await Tag.create(name="t2", value="bar")

#m2m
await bob.tags.add(foo)
await alice.tags.add(foo, bar)

Now I want to count users who have both tags foo and bar, which is alice in this case, so it should be 1.

The below query will give me a single level of filtering, but how do I specify that the user should have both foo and bar in their tags ?

u = await User.filter(tags__name="t1", tags__value="foo").count()
tortoise-orm python-3.x orm python
2 Answers

Tortoise-ORM provides Q objects for complicated queries with logical operators like |(or) and &(and).

Your query could be made like this:


u = await User.filter(Q(tags__name="t1") & 
                     (Q(tags__value="foo") | Q(tags__value="bar"))).count()
user_137826690

Since you cannot group_by on the annotated field in Tortoise ORM as of now.
Here's the solution using the having clause referred from here

u = await User.filter(Q(tags__value="foo") | Q(tags__value="bar"))
              .annotate(count=Count("id"))
              .filter(count==2)

The idea is to get the records having a count equal to the number of tags, which is 2 in this case (bar, foo)

user_98485490
Related