如何用单条查询在Tortoise-ORM中统计并获取模型记录？

我开发了一个使用Tortoise-ORM操作PostgreSQL数据库的应用，现有如下Dat模型：

class Dat(models.Model):
    id = fields.IntField(pk=True)
    name = fields.CharField(max_length=255, unique=False, null=False)
    ext = fields.CharEnumField(enum_type=ExtEnum, null=False)
    granularity = fields.CharEnumField(enum_type=Granularity, null=False)
    status_code = fields.IntField(null=False)
    status_info = fields.CharField(max_length=255, unique=False, null=False)
    created_at = fields.DatetimeField(auto_now_add=True)
    last_modified = fields.DatetimeField(auto_now=True)

    company = fields.ForeignKeyField(
        "models.Company",
        related_name="dat_company",
        on_delete=CASCADE,
        null=False,
    )

    user = fields.ForeignKeyField(
        "models.User",
        related_name="dat_user",
        on_delete=CASCADE,
        null=False,
    )

    class Meta:
        table = "dat"

User与Dat为一对多关系。我需要实现功能：获取带可选筛选的Dat记录（关联User表name字段），应用offset和limit分页，同时统计符合筛选的总记录数（不受分页影响）。

当前实现方式如下：

async def get_all_with_count(
        self,
        payload: dict = None,
        skip: int = 0,
        limit: int = 10,
    ):
        query = self.model.filter(**payload)
        model = (
            await query.prefetch_related(
                Prefetch(
                    "user", queryset=User.all().only("uid", "name"), to_attr="user_info"
                ),
            )
            .offset(skip)
            .limit(limit)
        )
        total = await query.count()

        return model, total

代码可正常运行，但需执行两次查询。请问能否通过单条查询实现该需求？

可以通过单条查询实现，利用PostgreSQL的**窗口函数COUNT(*) OVER()**一次性获取分页数据和总记录数，结合Tortoise-ORM的annotate方法即可完成。

实现思路 #

1. 用窗口函数给每条符合筛选条件的Dat记录标注总记录数：通过annotate添加total_count字段，值为全局筛选结果的总数。
2. 保留原有的预取关联和分页逻辑：确保能拿到关联的用户信息，同时实现分页。
3. 提取总记录数：由于所有记录的total_count值一致，直接从返回结果的第一条数据中提取即可，无结果时返回0。

代码示例 #

from tortoise.functions import Count
from tortoise.models import Model

async def get_all_with_count(
        self,
        payload: dict = None,
        skip: int = 0,
        limit: int = 10,
    ):
        # 构建查询并添加总记录数字段（窗口函数实现全局计数）
        query = self.model.filter(**payload).annotate(
            total_count=Count("id", _over={})
        )
        
        # 预取关联用户信息并执行分页
        results = await query.prefetch_related(
            Prefetch(
                "user", queryset=User.all().only("uid", "name"), to_attr="user_info"
            )
        ).offset(skip).limit(limit)
        
        # 提取总记录数
        total = results[0].total_count if results else 0
        
        return results, total

注意事项 #

• 窗口函数会在每条记录中重复总计数，会增加少量数据传输，但避免了两次查询的开销，数据量较大时优势更明显。
• 需确保Tortoise-ORM版本在0.19.0及以上，该版本开始支持窗口函数语法。
• 如果筛选条件涉及关联表（比如按User.name筛选），需要先完成表关联再添加窗口函数统计。

内容的提问来源于stack exchange，提问作者Diego L