Answer: Limit the number of records via join if their id matches in SQLAlchemy 2.x

answer re: Limit the number of records via join if their id matches in SQLAlchemy 2.x

Aug 14 '23

0

looks like I did it (Probably not optimally). Here's my solution:

async def get_topics(session: AsyncSession, limit: int, continue_after: int)
    subquery = (
        select(
            QuizTopic.id,
            Quiz.id.label("quiz_id"),
            func.row_number().over(partition_by=QuizTopic.id, order_by=Quiz.id).label("row_num")
        )
        .join(Quiz, Quiz.topic_id == QuizTopic.id)
        .subquery()
    )

    smt = (
        select(QuizTopic)
        .join(QuizTopic.quizzes_info)
        .join(subquery, subquery.c.quiz_id == Quiz.id)
        .where(subquery.c.row_num <= 3)
        .order_by(QuizTopic.id)
        .offset(continue_after).limit(limit)
        .options(
            load_only(QuizTopic.id,

…

Open Full Answer