Doubts about the correctness of the result of the count() method about tortoise-orm HOT 4 CLOSED

from datetime import datetime, timedelta

from tortoise import Model, run_async, Tortoise, connections, fields
from tortoise.queryset import QuerySet


class User(Model):

    id: int = fields.IntField(pk=True)
    username: str = fields.CharField(max_length=50, null=True, unique=True)

    offers: fields.ReverseRelation["Offer"]
    orders: fields.ReverseRelation["Order"]


class Offer(Model):

    id: int = fields.IntField(pk=True)
    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(Model):

    created: datetime = fields.DatetimeField(null=False)

    user: fields.ForeignKeyRelation["User"] = fields.ForeignKeyField(
        "models.User", related_name="orders"
    )
    offer: fields.ForeignKeyRelation["Offer"] = fields.ForeignKeyField(
        "models.Offer", related_name="orders"
    )


async def sql_count(query: QuerySet[Model]) -> int:
    _, result = await connections.get("default").execute_query(
        f"SELECT count(*) AS total FROM ({query.sql()})"
    )
    return result[0]["total"]


async def main() -> None:
    await Tortoise.init(db_url="sqlite://:memory:", modules={"models": ["__main__"]})
    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")

    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)

    query = (
        User.filter(
            orders__offer__user_id=1
        ).distinct()
    )

    result = await query
    print(result)
    print(await query.count())
    print(await sql_count(query))


if __name__ == "__main__":
    run_async(main())