how to replace SQLAlchemy mapper deprecated "order_by" option?

  python, python-3.x, sqlalchemy

I have a Single Table Inheritance sqlalchemy orm model that looks like this:

class Human(Base):
    
    __tablename__ = "human"

    id = Column(Integer, primary_key=True)
    name = Column(String)
    index = Column(Integer)
    parent_id = Column(Integer, ForeignKey("human.id"), nullable=True)
    type = Column(String)
    parent = relationship(
        "Human",
        cascade="save-update, merge",
        backref=backref("children", cascade="all"),
        lazy=False,
        remote_side="Human.id",
    )

    __mapper_args__ = {"polymorphic_on": type, "polymorphic_identity": "human"}

class Parent(Human):

    __mapper_args__ = {"polymorphic_identity": "parent"}

class Child(Human):

    hobby = Column(String)
    pet = Column(String)

    __mapper_args__ = {"polymorphic_identity": "child"}

This relationship looks like a nested tree, where I can query the oldest person (i.e: great grandparent, using query.get(1)) and then in his children attribute all his children will be present, and each child will also have their children present.
This relationship works just fine in every aspect.

I want the children that are queried when I query a Parent (due to lazy=False) to be ordered by the index column not the id column (which is the default).

I was achieving this by adding the following "order_by": "index" to the Human class’ __mapper_args__ as follows:

__mapper_args__ = {"polymorphic_on": type, "polymorphic_identity": "human", "order_by": "index"}

I recently found out that in SQLAlchemy the order_by argument in the mapper() class is deprecated since SQLAlchemy version 1.1 (reference). And that they say to use Query.order_by() to order the set instead, which is not working for me for some reason.

I have tried the following:

  • adding order_by to the query, session.query(Human).order_by(Human.index).get(1)
  • adding order_by="Human.index" to the relationship() in the Human class.

these two methods are not working.

How can I achieve the same effect as the order_by in mapper() now that it is being deprecated?

Source: Python Questions

LEAVE A COMMENT