[Python] How to order table with many-to-many association to the same table

I have a table with many-to-many relationship to the same table.

class User(Base):
__tablename__ = "users"

id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(nullable=False)

managers: Mapped[list["User"]] = relationship(
secondary="managers_to_customers",
primaryjoin=id == "managers_to_customers.customer_id",
secondaryjoin=id == "managers_to_customers.manager_id",
)

customers: Mapped[list["User"]] = relationship(
secondary="managers_to_customers",
primaryjoin=id == "managers_to_customers.manager_id",
secondaryjoin=id == "managers_to_customers.customer_id",
)


class ManagerToCustomer(Base):
__tablename__ = "managers_to_customers"

manager_id: Mapped[str] = mapped_column(ForeignKey("users.id"), primary_key=True)
customer_id: Mapped[str] = mapped_column(ForeignKey("users.id"), primary_key=True)


And I want to sort all users by the name of the manager (if they have a manager or managers).

manager = aliased(User)
stmt = (
select(User)
.join(
ManagerToCustomer,
User.id == ManagerToCustomer.customer_id,
isouter=True,
)
.join(
manager,
ManagerToCustomer.manager_id == manager.id,
isouter=True,
)
.order_by(nulls_last(asc(manager.name)))
)


But this statement sorts users not by the name of they manager, but by the name of the user.
For example if I have:

manager_1 = User(name="B")
manager_2 = User(name="A")
user_1 = User(name="user_1")
user_1.managers = [manager_1]
user_2 = User(name="user_2")
user_2.managers = [manager_2]


I expect:

1. user_2 (has manager with name "A")
2. user_1 (has manager with name "B")
3. manager_1 (no managers)
4. manager_2 (no managers)

But now result is:

1. manager_1 (name == "A")
2. manager_2 (name == "B")
3. user_1 (name == "user_1")
4. user_2 (name == "user_2")

How can I sort it correctly?

Continue reading...