如何通过SQLAlchemy混合属性实现按关联表字段排序？

The core issue here is that your hybrid property's expression directly references the Suborder class's column, but SQLAlchemy automatically assigns an alias (suborders_1) to the joined suborders table when using lazy='joined'. Your order clause ends up referencing suborders.buyout_date instead of this aliased table, hence the "Unknown column" error.

Here's the corrected implementation to resolve this:

class PurchaseOrder(db.Model, BaseModel):
    __tablename__ = 'purchase_orders'
    ''' Represents purchase order '''
    id = Column(String(23), primary_key=True, nullable=False)
    vendor_po_id = Column(String(12))
    suborder_id = Column(String(20), ForeignKey('suborders.id'), nullable=False)
    suborder = relationship('Suborder', foreign_keys=[suborder_id], lazy='joined')

    @hybrid_property
    def purchase_date(self):
        return self.suborder.buyout_date

    @purchase_date.expression
    def purchase_date(cls):
        # Use the relationship's expression to fetch the correctly aliased suborder table
        suborder_table = cls.suborder.property.expression
        return suborder_table.c.buyout_date

Why this fixes the problem: #

Instead of hardcoding a reference to Suborder.buyout_date, we leverage the property.expression of the existing suborder relationship. This gives us the exact table reference—including any auto-generated alias—that SQLAlchemy uses in the query's JOIN clause. When you build your order-by logic with this updated hybrid property, the generated SQL will reference the aliased table (suborders_1.buyout_date) instead of the unaliased suborders.buyout_date.

The resulting SQL will look like this (simplified):

SELECT purchase_orders.*, suborders_1.* FROM purchase_orders LEFT OUTER JOIN suborders AS suborders_1 ON suborders_1.id = purchase_orders.suborder_id ORDER BY suborders_1.buyout_date

This aligns the order-by clause with the JOINed table alias and eliminates the 1054 column-not-found error.

内容的提问来源于stack exchange，提问作者Ralfeus