How can I query or order_by with Link Model with Extra Fields in a many-to-many relation?

[so2liu]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

class UserRoomLink(SQLModel, table=True):
    room_id: Optional[int] = Field(
        default=None, foreign_key="room.id", primary_key=True
    )
    user_id: Optional[int] = Field(
        default=None, foreign_key="user.id", primary_key=True
    )
    created_at: datetime = Field(
        default_factory=datetime.utcnow, index=True, nullable=False
    )

class Room(RoomBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    users: List["User"] = Relationship(back_populates="rooms", link_model=UserRoomLink)

class User(UserBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)

    rooms: Optional[Room] = Relationship(
        back_populates="users", link_model=UserRoomLink
    )

Description

How can I query or order_by with Link Model with Extra Fields?
For example I want to query a single room with its members, and members' order is in the link created order.
Btw, the doc is always talking about creating and updating, not querying.

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.14

Python Version

3.10.5

Additional Context

No response

[Drachenfels]

I am experimenting with something akin to this:

    heroes: list["Hero"] = Relationship(
        link_model=HeroTeamLink,
        sa_relationship_kwargs={
            "order_by": "Hero.name",
        },
    )

But seems to be perfectly ignored on an object... (unless I need to re-query object or something like that).