How could I order by array_to_string hybrid property?

Question

I'm having a problem where I need to query all records from Account table with some ID and order the output by regions. Account and Region are connected using many-to-many relationship. Here are model descriptions:

class Account(Base, ModelMixin):
    __tablename__ = 'accounts'
    user_id = Column(Integer, ForeignKey(
        'users.id', ondelete='CASCADE'), nullable=False)
    user = relationship('User', lazy='joined')
    secret = Column(String(32), nullable=True, unique=False, index=True)
    email = Column(String(255), nullable=False, unique=False, index=True)
    password = Column(String(255), nullable=True)
    first_name = Column(String(255), nullable=True)
    surname = Column(String(255), nullable=True)
    photo = Column(String(255), nullable=True)
    
    @hybrid_property
    def regions(self):
        from .orm import DBSession
        return [
            x.region for x in (
                DBSession().query(AccountRegion)
                .join(AccountRegion.region)
                .filter(AccountRegion.deleted == None)
                .order_by(Region.region_name)
                .all()
                )
            ]
        return []

    @regions.expression
    def regions(cls):
        from .orm import DBSession
        return func.array_to_string(
            DBSession().query(Region.region_name)
            .filter(Region.deleted == None)
            .join(AccountRegion.region)
            .filter(AccountRegion.deleted == None)
            .order_by(Region.region_name)
            .all(),
            ', '
        )

class RegionAccount(Base, ModelMixin):
    __tablename__ = 'account_regions'
    account_id = Column(Integer, ForeignKey(
        'accounts.id', ondelete='CASCADE'), nullable=False)
    account = relationship('Account', lazy='joined')
    region_id = Column(Integer, ForeignKey(
        'regions.id', ondelete='CASCADE'), nullable=False)
    region = relationship('Region', lazy='joined')
    enabled = Column(Boolean, default=False)

class Region(Base, ModelMixin):
    __tablename__ = 'regions'
    user_id = Column(Integer, ForeignKey(
        'users.id', ondelete='CASCADE'), nullable=False)
    region_name = Column(String(255), nullable=False)
    region_description = Column(Text, nullable=True)
    region_accounts = relationship("RegionAccount")

I've tried to change the @regions.expression in the way how suggested in here https://stackoverflow.com/questions/25836076/sqlalchemy-order-by-hybrid-property-that-references-relationship but nothing changed. order_by(Account.regions) doesn't do anything to a query.

How could I order by array_to_string hybrid property?

0 Answers0