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