how to replace SQLAlchemy mapper deprecated "order_by" option?

user_5568212 0
Viewed 750

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?

sqlalchemy python-3.x python
1 Answers

I got an answer after asking the question on sqlalchemy's github. I am posing the response in case anyone else searches for this issue and finds it here instead of sqlalchemy's github.

The answer:

the ordering of a collection loaded by a relationship is controlled by the order_by parameter of relationship() and/or backref. In this case you want "children" to be ordered so you put that where you define "children":

parent = relationship(
       "Human",
       cascade="save-update, merge",
       backref=backref("children", cascade="all", order_by="Human.index"),
       lazy=False,
       remote_side="Human.id",
   )

that is, this has nothing to do with mapper.order_by, although that attribute was having the effect you were seeking. if you had tried relationship->order_by on the "parent' side, that was the wrong side, it goes where the actual thing to be ordered is defined, in this case the backref.

user_55682120
Related