Tortoise ORM filter with logical operators

Question

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()

Answer 1

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()