selectinload with limit in async sqlalchemy

Question

如何在async sqlalchemy中限制selectinload

我想接收聊天中的最后一条消息，但这将返回每条消息:

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)
)
)

我尝试延迟加载(lazyload)，但它返回错误:

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)

Answer 1

这是同步的，但我认为它只会生成与await兼容的查询。它使用子查询来获取最新消息，而不是使用消息关系。

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)