Search code examples
pythonsqlalchemypyramid

Ordering SQLAlchemy relationship by another field, not by primary key

I have the following model using sqlalchemy in a pyramid application:

class Issue(base_iop):
    __tablename__ = 'issues'
    issueid = Column(BIGINT, primary_key=True, name="ISSUEID")
    issuenum = Column(VARCHAR(20), name="ISSUENUM")
    status = Column(VARCHAR(32), name="STATUS")
    datetime = Column(TIMESTAMP, name="ISSUETIME")

class Related(base_iop):
    __tablename__ = 'related'
    relationid = Column(BIGINT, primary_key=True, name="ROWSTAMP")
    parent_num = Column(VARCHAR(20), ForeignKey('issue.ISSUENUM'),name="RECORDKEY")
    children_num = Column(VARCHAR(20), ForeignKey('issue.ISSUENUM'), name="RELATEDKEY")
    issues = relationship(iop, foreign_keys=[child_num])

I can get the related issues of an issue just fine using: the issues attribute of the related table:

for related in db.query(Issue).all()[0].issues:
    print related.status

However, I didn't find the solution to order the issues by the datetime attribute. If the Related had the datetime attribute like this, it should be pretty straightforward using the order_by in the relationship:

class Issue(base_iop):
    __tablename__ = 'issues'
    issueid = Column(BIGINT, primary_key=True, name="ISSUEID")
    issuenum = Column(VARCHAR(20), name="ISSUENUM")
    status = Column(VARCHAR(32), name="STATUS")
    datetime = Column(TIMESTAMP, name="ISSUETIME")
    children =  relationship("Related", foreign_keys="[Related.parent_num]", backref="parent", order_by="[Related.datetime]")

class Related(base_iop):
    __tablename__ = 'related'
    relationid = Column(BIGINT, primary_key=True, name="ROWSTAMP")
    parent_num = Column(VARCHAR(20), ForeignKey('issue.ISSUENUM'),name="RECORDKEY")
    children_num = Column(VARCHAR(20), ForeignKey('issue.ISSUENUM'), name="RELATEDKEY")
    datetime = Column(TIMESTAMP, name="ISSUETIME")
    issues = relationship(iop, foreign_keys=[child_num])

How do I order the related issues by another field, not by primary key like is it now?

Solution

  • relationship accept the keyword argument order_by to handle your default ordering of the relation. it should be somthing like:

    issues = relationship(iop, foreign_keys=[child_num], order_by="Issue.datetime")

    Note that backref function has the same keyword, if you need to handle that.

    Here you can read more: http://docs.sqlalchemy.org/en/rel_1_0/orm/tutorial.html#building-a-relationship on this topic