fastapi-pagination: `Total` returns more values than expected (not applying distinct on count)

Hi,

I have the tables Author and Books.

Author Book
Alice A
Alice B
Bob C

In my FastAPI endpoint, when paginating the select(Author).join(Books) I want to respond with a list that has 2 dicts:

  • Alice (with her two books)
  • Bob (with his one book)

It works great but the total is off. Instead of counting the unique Authors, it counts the total rows of the query.

Is there an option to fix that?

Perhaps relevant:

  • using FastAPI (+pydantic)
  • async PostgreSQL

About this issue

  • Original URL
  • State: open
  • Created 4 years ago
  • Reactions: 1
  • Comments: 20 (12 by maintainers)

Most upvoted comments

Actually, I managed to solve this issue by applying distinct() to count_query():

def count_query(query: Select, *, use_subquery: bool = True) -> Select:
    query = query.order_by(None).options(noload("*"))

    if use_subquery:
        return select(func.count()).select_from(query.distinct().subquery())

    return query.distinct().with_only_columns(  # noqa: PIE804
        func.count(),
        **{"maintain_column_froms": True},
    )

Do you think it works for all cases?

@lqmanh Now sqlachemy paginate function accepts count_query argument, it allows you to override default count query, it might help to solve this case.

Hi all,

It’s been almost 1 year since the last comment, I will try to spend some time on solving this issue.

Hi all,

Sorry for the long response.

Unfortunately, we can’t fix this issue using distinct count cause it will break the way pagination works. You need to play around with the way you fetch relationships, it should help to solve this issue.

Actually, I managed to solve this issue by applying distinct() to count_query():

def count_query(query: Select, *, use_subquery: bool = True) -> Select:
    query = query.order_by(None).options(noload("*"))

    if use_subquery:
        return select(func.count()).select_from(query.distinct().subquery())

    return query.distinct().with_only_columns(  # noqa: PIE804
        func.count(),
        **{"maintain_column_froms": True},
    )

Do you think it works for all cases?