How can I do selectinload with limit in async sqlalchemy?

I want to receive last message in the chat, but this will return every message:

chats = await self.session.execute(
    select(Chatroom).filter(
        or_(
            (Chatroom.first_user == user_id),
            (Chatroom.second_user == user_id)
        )
    ).options(
        selectinload(Chatroom.first_user_link),
        selectinload(Chatroom.second_user_link),
        selectinload(Chatroom.messages)
    )
)

I tried lazy loading(lazyload) but it returns error:

sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place? (Background on this error at: https://sqlalche.me/e/14/xd2s)

CodePudding user response：

This is sync but I think it will just generate a query compatible with await. It uses a subquery to get the latest message instead of using the messages relationship.

with Session(engine) as session, session.begin():
    # hardcoded for this test
    user_id = 2

    # I sort the messages by descending id to determine "latest" but you
    # might use a datetime here.
    message_subq = select(Message).order_by(Message.id.desc()).limit(1).subquery()
    aliased_message = aliased(Message, message_subq)
    q = select(Room, aliased_message).outerjoin(aliased_message).where(
        or_(
            (Room.first_user_id == user_id),
            (Room.second_user_id == user_id)
        )
    ).options(
        selectinload(Room.first_user),
        selectinload(Room.second_user),
        #selectinload(Room.messages)
    )
    for room, latest_message in session.execute(q).all():
        print (room.id, latest_message.id)