total не совпадает со списком: считаем группы через подзапрос SQLAlchemy

Endpoint показывает проекты, у которых не меньше двух задач. Список построен через GROUP BY и HAVING, а total считается отдельным count по таблице задач. В результате пользователь видит одну строку и двадцать страниц, половина которых пустая. Счётчик считает исходные записи, хотя интерфейс листает группы после фильтрации.

Посчитайте тот результат, который листает клиент

Для страницы групп единица результата — проект, прошедший HAVING. Сначала построим базовую выборку идентификаторов этих проектов. Затем превратим её в подзапрос и посчитаем строки снаружи. Тот же базовый запрос используем для страницы с порядком и limit. Так условие попадания в список имеет один источник и не дублируется вручную в двух ветках.

LIMIT и OFFSET применяются после подсчёта всего подходящего набора. Если засунуть уже ограниченную страницу в подзапрос count, total будет равен размеру страницы, а не общему количеству групп. ORDER BY для счётчика обычно не нужен; он нужен странице, чтобы соседние запросы имели определённый порядок. В примере сортируем по уникальному id проекта.

Минимальный набор обнаружит ошибку

Нужен SQLAlchemy. Подготовим три проекта: два с достаточным числом задач и один с недостаточным. Страница имеет размер один, поэтому отдельно проверим total=2 и один возвращённый идентификатор. Ошибочный подсчёт количества задач тоже вычислим, чтобы увидеть разницу на реальных данных.

from sqlalchemy import create_engine, ForeignKey, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session

class Base(DeclarativeBase):
    pass

class Project(Base):
    __tablename__ = "projects"
    id: Mapped[int] = mapped_column(primary_key=True)
    tasks: Mapped[list["Task"]] = relationship()

class Task(Base):
    __tablename__ = "tasks"
    id: Mapped[int] = mapped_column(primary_key=True)
    project_id: Mapped[int] = mapped_column(ForeignKey("projects.id"))

engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
with Session(engine) as db:
    db.add_all([Project(id=1, tasks=[Task(id=10), Task(id=11)]), Project(id=2)])
    db.commit()

from sqlalchemy import func

with Session(engine) as db:
    db.add(Project(id=3, tasks=[Task(id=30), Task(id=31), Task(id=32)]))
    db.commit()

grouped = (select(Project.id)
           .join(Project.tasks)
           .group_by(Project.id)
           .having(func.count(Task.id) >= 2))

with Session(engine) as db:
    wrong_total = db.scalar(select(func.count()).select_from(Task))
    total = db.scalar(select(func.count()).select_from(grouped.subquery()))
    page = db.scalars(grouped.order_by(Project.id).limit(1).offset(0)).all()
    second = db.scalars(grouped.order_by(Project.id).limit(1).offset(1)).all()
    assert wrong_total == 5
    assert total == 2 and page == [1] and second == [3]
    empty_groups = grouped.having(func.count(Task.id) >= 10)
    assert db.scalar(select(func.count()).select_from(empty_groups.subquery())) == 0
    print({"task_count": wrong_total, "total": total, "page": page, "next": second})
engine.dispose()

Не теряйте условия доступа

Если пользователь видит только проекты своей команды, соответствующий фильтр должен находиться в grouped до вычисления total. Иначе сам счётчик раскрывает количество недоступных записей или показывает несуществующие страницы. Общий базовый запрос уменьшает риск расхождения, особенно когда к фильтрам добавляются архив, даты и права просмотра.

Добавление HAVING вторым вызовом в примере объединяет условия через AND. Это позволяет проверить пустой набор без переписывания исходной выборки. В реальном коде параметры фильтра лучше передавать в отдельную функцию построения базового select. Её результат используется для списка и счётчика, а HTTP-слой только проверяет ограничения страницы и сериализует числа.

При конкурентных изменениях два отдельных запроса могут увидеть разные состояния. Если договор требует строго согласованных total и списка, выберите подходящий уровень изоляции и единую транзакцию на вашей базе. SQLite-пример показывает арифметику набора, а свойства изоляции PostgreSQL проверяются на PostgreSQL. Не выводите их из успешного локального теста в памяти.

Для больших наборов точный count может стать дорогой частью endpoint. Сначала убедитесь, что клиент действительно использует общее число: иногда ему достаточно next cursor или признака наличия следующей страницы. Но если total обещан договором, ускорение не должно менять единицу подсчёта с групп на исходные строки.

В статье о пагинации разобраны предел страницы и устойчивый порядок. Здесь к ним добавляется отдельная задача: согласовать total с GROUP BY и HAVING.

Продолжить на практике

Соедините фильтры, счётчик и страницу в одном учебном endpoint и проверьте пустой результат. В бесплатной программе FastAPI есть работа с базой и автотесты, на которых удобно закрепить этот договор списка. Откройте бесплатную программу FastAPI.