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-13 13:49:00Z

1

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

edited Jun 13, 2019 at 13:49

answered Jun 13, 2019 at 12:03

Rach Sharp

2,50418 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ailin Over a year ago

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

Rach Sharp Over a year ago

Ahh I see what you mean, it needs to be a natural sort somehow

Rach Sharp Over a year ago

Found it, you can use Column.op(",")(Column) to achieve this

ailin Over a year ago

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

Rach Sharp Over a year ago

Updated the answer :)

Collectives™ on Stack Overflow

How to query on several fields using sqlalchemy hybrid_property?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related