`Total` returns more values than expected (not applying distinct on count)

[Riya-900]

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

[uriyyo]

Hi @Riya-900,

Could you please change your query to smth like this:

select(Author).options(selectinload(Author.books))

[bnku]

@Riya-900 Hello! Have you resolved this issue? If yes, then how did you do it?

[bnku]

I passed the total_group_by parameter to the count_query function and called query.group_by(total_group_by) to ensure the correct calculation of total:

# file: fastapi_pagination/ext/sqlalchemy.py

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

    if total_group_by:
        query = query.group_by(total_group_by)

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

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

@uriyyo, perhaps this can help you improve your product.

[uriyyo]

@bnku I guess the issue is with how you load relationships. I am not sure that we need to use group_by to get count.

[uriyyo]

@Riya-900 Any updates, does this comment help you?

#674 (comment)

[lqmanh]

@uriyyo In my use case, I really need to use .join(). How can I fix this issue?

[uriyyo]

Hi @lqmanh,

Could you please show an example?

[lqmanh]

@uriyyo Sorry for my late reply, but I'm doing some kind of 3-way joining like this:

class Status(DeclarativeBase):
    group_id: Mapped[UUID]

class Item(DeclarativeBase):
    status: Mapped[Status] = relationship(...)
    documents: Mapped[list[Document]] = relationship(...)

class Document(DeclarativeBase):
    item_id: Mapped[UUID]
    status_group_id: Mapped[UUID]


stmt = (
    select(Item)
    .join(Item.status)
    .join(Item.documents)
    .where(Status.group_id == Document.status_group_id)  # only include documents of current status group
    .options(
        contains_eager(Item.status),
        contains_eager(Item.documents),
    )
)

UPDATED: Can fastapi-pagination support distinct count, by passing column(s) to paginate()? paginate(..., count_distinct=Item.id) for example.

[uriyyo]

@lqmanh Could you try to change your query to:

stmt = (
    select(Item)
    .join(Item.status)
    .join(Item.documents)
    .options(
        contains_eager(Item.status),
        selectinload(Item.documents),  # use selectinload() to load all documents
    )
)

[lqmanh]

@uriyyo Then how do I filter only documents associated with current status group?
Ah sorry, misread your reply. Using selectinload or joinedload independently won't work anyway

[uriyyo]

@lqmanh If you have your relationship configured correctly, it will be done automatically