How to query on several fields using sqlalchemy hybrid_property?

Question

I have model defined as follow:

class Patch(Base):
    id = Column(Integer, primary_key=True)
    major = Column(Integer, nullable=False)
    minor = Column(Integer, nullable=False)

    @hybrid_property
    def patch(self) -> str:
        return f'{self.major}.{self.minor}'

    @patch.expression
    def patch(self):
        return func.concat(self.major, '.', self.minor)

I would like to make such type of requests:

Patch.query.order_by(Patch.patch)

Which should be equalent to next SQL commands:

SELECT * FROM patch ORDER BY major DESC, minor DESC

I tried to use hybrid_property.expression

    @patch.expression
    def patch(self):
        return self.major, self.minor

but got the exception:

sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped entity expected - got '(<sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x7f1a5cd5fc50>, <sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x7f1a5cd5fd00>)

I know its possible to use custrom comparator, but I havent found how to do that with two fields (i.e. major, minor).

Any ideas?

P.S. it doesn't have to be hybrid_property, either way is fine

Rach Sharp · Accepted Answer · 2019-06-13T13:49:00.420

The below works with the ordering on two separate fields. Worth noting that the python implementation of patch is still a string so ordering would differ between Python objects and SQL Rows unless you change patch to return a tuple of (major, minor). Tested with Python 3.6.5, SQLAlchemy 1.1.15, psycopg2 2.7.7

from sqlalchemy import create_engine, Column, Integer, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql.functions import concat

engine = create_engine('postgresql://localhost:5432/postgres', echo=True)
Base = declarative_base(bind=engine)


class Patch(Base):

    __tablename__ = "patch"

    id = Column(Integer, primary_key=True)
    major = Column(Integer, nullable=False)
    minor = Column(Integer, nullable=False)

    @hybrid_property
    def patch(self) -> str:
        return f'{self.major}.{self.minor}'

    @patch.expression
    def patch(self):
        return self.major.op(",")(self.minor)

    def __repr__(self):
        return f"Patch object {self.id} {self.patch}"


Base.metadata.create_all()
Session = sessionmaker(bind=engine)
session = Session()

if __name__ == "__main__":
    if session.query(Patch).count() == 0:
        example_patches = [Patch(major=major, minor=minor) for major in range(3) for minor in range(3)]
        session.add_all(example_patches)
        session.commit()

    patches = session.query(Patch).order_by(Patch.patch).all()
    print(patches)

2019-06-13 14:44:27,245 INFO sqlalchemy.engine.base.Engine SELECT patch.id AS patch_id, patch.major AS patch_major, patch.minor AS patch_minor 
  FROM patch ORDER BY patch.major , patch.minor
2019-06-13 14:44:27,245 INFO sqlalchemy.engine.base.Engine {}
[Patch object 2740 0.0, Patch object 2741 0.1, Patch object 2742 0.2, Patch object 2743 0.3, Patch object 2744 0.4, Patch object 2745 0.5, Patch object 2746 0.6, Patch object 2747 0.7, Patch object 2748 0.8, Patch object 2749 0.9, Patch object 2750 0.10, ...

Yeah, but that's not what I need. It will order by string, and not by two fields: `ORDER BY concat(patch.major, %(concat_1)s, patch.minor)`. And I need `ORDER BY major, minor` — ailin, Jun 13 '19 at 12:40
Ahh I see what you mean, it needs to be a [natural sort](https://stackoverflow.com/questions/153633/natural-sort-in-mysql/12257917#12257917) somehow — Rach Sharp, Jun 13 '19 at 13:38
Found it, you can use `Column.op(",")(Column)` to achieve this — Rach Sharp, Jun 13 '19 at 13:49
oh wow, yes, that's exactly what I've looked for. Could you update your question so I can accept it? `return self.major.op(',')(self.minor)` in `@patch.expression` works well — ailin, Jun 13 '19 at 14:11

How to query on several fields using sqlalchemy hybrid_property?

1 Answers1