Bug: SQLAlchemyAsyncQueryRepository.list() returns only first column values

[vladpi]

Description

According to the documentation example, SQLAlchemyAsyncQueryRepository.list() should return a list of tuples with values.

Instead, it returns a list of values from the first column of the query.

This happens because in the method SQLAlchemyAsyncQueryRepository.list(), scalars() is used to obtain the results. It seems that using the all() method would be more appropriate, but I'm not sure.

I really like the project and want to help. If others agree, I can work on this :)

URL to code causing the issue

No response

MCVE

import asyncio

from advanced_alchemy.base import BigIntBase
from advanced_alchemy.repository import SQLAlchemyAsyncQueryRepository, SQLAlchemyAsyncRepository
from sqlalchemy import func, select
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import Mapped


class Post(BigIntBase):
    author_id: Mapped[int]


class PostRepo(SQLAlchemyAsyncRepository[Post]):
    model_type = Post


async def create_posts(db_session: AsyncSession):
    repository = PostRepo(session=db_session)

    for i in range(50):
        await repository.add(Post(author_id=(i // 10) + 1))

    await repository.session.commit()


async def get_posts_per_author(db_session: AsyncSession) -> list[tuple[int, int]]:
    repository = SQLAlchemyAsyncQueryRepository(session=db_session)
    return await repository.list(select(Post.author_id, func.count(Post.id)).group_by(Post.author_id))


async def main():
    engine = create_async_engine("sqlite+aiosqlite://")

    async with engine.begin() as conn:
        await conn.run_sync(Post.metadata.create_all)

    async with AsyncSession(engine) as session:
        await create_posts(session)

        posts_per_author = await get_posts_per_author(session)
        print(posts_per_author)  # [1, 2, 3, 4, 5]

        assert posts_per_author == [(1, 10), (2, 10), (3, 10), (4, 10), (5, 10)]


if __name__ == "__main__":
    asyncio.run(main())

Steps to reproduce

1. Run MCVE
2. Got AssertionError

Screenshots

No response

Logs

No response

Package Version

0.26.2

Platform

• Linux
• Mac
• Windows
• Other (Please specify in the description above)

[cofin]

@vladpi, The repo is currently designed to work with ORM style queries.

You'll want to modify this:

return await repository.list(select(Post.author_id, func.count(Post.id)).group_by(Post.author_id))

to something like this. You'll want to defer columns that shouldn't be loaded

If you are looking to do a core query only, you may be looking for the SQLAlchemyAsyncQueryService which will work on any arbitrary core query.

If you know of a non-breaking way to integrate this support, i'm happy to have the contribution though.

[cofin]

@vladpi after re-reading and re-reviewing all the code (it's been a while since I've written that piece), you are correct. if you want take this on, that would be awesome. Otherwise, let me know and I'll try to get it cleaned up soon.

[vladpi]

@cofin Thank you! Yes, I will make a Pull Request with fix. Within a few days, I think.