tortoise.functions.Count() — incorrect calculation when several relations

[PieceOfGood]

I'm not sure if the following behavior is a problem, so please take a look at the example below to help understand me what a well-written query definition should look like to get the expected result.

class BaseModel(Model):
    class Meta:
        abstract = True
    id = fields.IntField(pk=True, index=True)

class UserReview(BaseModel):
    class Meta:
        table = "user_reviews"
    owner: fields.ForeignKeyRelation["User"] = fields.ForeignKeyField(
        "models.User", related_name="created_reviews"
    )
    target: fields.ForeignKeyRelation["User"] = fields.ForeignKeyField(
        "models.User", related_name="reviews"
    )
    text: str = fields.TextField(null=False)

class User(BaseModel):
    class Meta:
        table = "users"
    username: str = fields.CharField(max_length=50, null=True, unique=True)
    offers: fields.ReverseRelation["Offer"]
    orders: fields.ReverseRelation["Order"]
    created_reviews: fields.ReverseRelation["UserReview"]
    reviews: fields.ReverseRelation["UserReview"]

class Offer(BaseModel):
    class Meta:
        table = "offers"
    name: str = fields.CharField(max_length=50, null=False)
    user: fields.ForeignKeyRelation[User] = fields.ForeignKeyField(
        "models.User", related_name="offers"
    )
    orders: fields.ReverseRelation["Order"]

class Order(BaseModel):
    class Meta:
        table = "orders"
    created: datetime = fields.DatetimeField(null=False)
    user: fields.ForeignKeyRelation["User"] = fields.ForeignKeyField(
        "models.User", related_name="orders"
    )
    """ Client """
    offer: fields.ForeignKeyRelation["Offer"] = fields.ForeignKeyField(
        "models.Offer", related_name="orders"
    )


async def init() -> None:
    await Tortoise.init(config=TORTOISE_CONFIG)
    await Tortoise.generate_schemas()

    businessman = await User.create(username="businessman")

    offer1 = await Offer.create(name="offer1", user=businessman)
    offer2 = await Offer.create(name="offer2", user=businessman)
    offer3 = await Offer.create(name="offer3", user=businessman)

    await User.create(username="client_one")
    await User.create(username="client_two")
    await User.create(username="client_three")

    # ? Make orders
    moment = datetime(2024, 1, 1, 0, 0, 0)

    orders = []
    for i, offer in enumerate((offer1, offer2, offer3)):
        for j in range(3):
            orders.append(Order(created=moment, user_id=(j + 2), offer=offer))
            moment += timedelta(days=1)

    await Order.bulk_create(orders)


async def test_count() -> None:
    await Tortoise.init(config=TORTOISE_CONFIG)
    await Tortoise.generate_schemas()

    query = (
        # ? Get all clients who made Orders for
        # ? an Offer with ID = 1
        User.filter(orders__offer__user__id=1)

        # ? Count how many Orders each person
        # ? has made, as well as how many reviews
        # ? have been left about each of them
        .annotate(
            orders_count=Count("orders"),
            reviews_count=Count("reviews"),
        )
    )

    result = await query.values(
        "id",
        "orders_count",
        "reviews_count"
    )
    pprint(result)

if __name__ == "__main__":
    run_async(init())
    run_async(test_count())

Output correct:

[{'id': 2, 'orders_count': 3, 'reviews_count': 0},
 {'id': 3, 'orders_count': 3, 'reviews_count': 0},
 {'id': 4, 'orders_count': 3, 'reviews_count': 0}]

When I make one review for User with ID = 2, and call test_count() again:

await UserReview.create(text="target2 text1", owner_id=1, target_id=2)
await test_count()

Incorrect output:

[{'id': 2, 'orders_count': 3, 'reviews_count': 3},
 {'id': 3, 'orders_count': 3, 'reviews_count': 0},
 {'id': 4, 'orders_count': 3, 'reviews_count': 0}]

When I make two review more for User with ID = 2, and call test_count() again:

await UserReview.create(text="target2 text2", owner_id=1, target_id=2)
await UserReview.create(text="target2 text3", owner_id=1, target_id=2)
await test_count()

Incorrect output:

[{'id': 2, 'orders_count': 9, 'reviews_count': 9},
 {'id': 3, 'orders_count': 3, 'reviews_count': 0},
 {'id': 4, 'orders_count': 3, 'reviews_count': 0}]

---

After that, I change body in test_count() to:

query = (
        User.filter(orders__offer__user__id=1)
        .annotate(
            orders_count=Count("orders"),
            # reviews_count=Count("reviews"),
        )
    )

    result = await query.values(
        "id",
        "orders_count",
        # "reviews_count"
    )
    pprint(result)

Output correct:

[{'id': 2, 'orders_count': 3},
 {'id': 3, 'orders_count': 3},
 {'id': 4, 'orders_count': 3}]

Change body in test_count() to:

query = (
        User.filter(orders__offer__user__id=1)
        .annotate(
            # orders_count=Count("orders"),
            reviews_count=Count("reviews"),
        )
    )

    result = await query.values(
        "id",
        # "orders_count",
        "reviews_count"
    )
    pprint(result)

Output incorrect:

[{'id': 2, 'reviews_count': 9},
 {'id': 3, 'reviews_count': 0},
 {'id': 4, 'reviews_count': 0}]

---

Expected output:

[{'id': 2, 'orders_count': 3, 'reviews_count': 1},
 {'id': 3, 'orders_count': 3, 'reviews_count': 0},
 {'id': 4, 'orders_count': 3, 'reviews_count': 0}]

And

[{'id': 2, 'orders_count': 3, 'reviews_count': 3},
 {'id': 3, 'orders_count': 3, 'reviews_count': 0},
 {'id': 4, 'orders_count': 3, 'reviews_count': 0}]

[PieceOfGood]

Everything was found in the documentation, when I read it in the morning with a fresh mind.
Thanks to everyone who cares about all these details!