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

[YuriiMotov]

You can specify sa_relationship_kwargs={"order_by": "UserRoomLink.created_at.desc()"} to your relationship attribute:

    rooms: List[Room] = Relationship(
        back_populates="users",
        link_model=UserRoomLink,
        sa_relationship_kwargs={
            "order_by": "UserRoomLink.created_at",
        },
    )

Try changing "order_by": "UserRoomLink.created_at" to "order_by": "UserRoomLink.created_at.desc()" and see that order will be different.

Runable code example in the details:

from datetime import datetime, timedelta
from typing import List, Optional
from sqlmodel import SQLModel, Field, Relationship, create_engine, Session


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(SQLModel, 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(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)

    rooms: List[Room] = Relationship(
        back_populates="users", link_model=UserRoomLink, sa_relationship_kwargs={
            "order_by": "UserRoomLink.created_at.desc()",
        },
    )


engine = create_engine("sqlite:///")

SQLModel.metadata.create_all(engine)


with Session(engine) as session:
    room1 = Room(name="Test Room")
    room2 = Room(name="Test Room 2")
    user = User()

    session.add(room1)
    session.add(room2)
    session.add(user)
    session.commit()

    session.refresh(room1)
    session.refresh(room2)
    session.refresh(user)

    link1 = UserRoomLink(room_id=room1.id, user_id=user.id, created_at=datetime.utcnow())
    link2 = UserRoomLink(room_id=room2.id, user_id=user.id, created_at=datetime.utcnow() + timedelta(seconds=1))


    session.add(link1)
    session.add(link2)

    session.commit()

    user = session.get(User, 1)

    assert len(user.rooms) == 2
    assert user.rooms[0].name == "Test Room 2"  # created_at is more recent
    assert user.rooms[1].name == "Test Room"