Order by child object's field in SQLAlchemy?

Question

I want to query specific objects of Reference. But I want that list ordered by its child objects (you could say relation): Reference._periodical._name and Reference._author._lastname' (means the __first__ author, which are orded byReferenceAuthor.Index`).

Can not see how to do this. This is the query but without the ordering in it.

return self.session.query(Reference) \
         .filter_by(_mark = False) \
         #.order_by(Reference._periodical) \
         #.order_by(Reference._author._lastname) \
         .all()

This is the model itself

ReferenceAuthor = sa.Table('ReferenceAuthor', _Base.metadata,
        sa.Column('ReferenceID',  sa.Integer, sa.ForeignKey('Reference.ID'), primary_key=True),
        sa.Column('PersonID', sa.Integer, sa.ForeignKey('Person.ID'), primary_key=True),
        sa.Column('Index', sa.Integer)
        )


class Reference(_Base):
    __tablename__ = 'Reference'

    _id = sa.Column('ID', sa.Integer, primary_key=True)
    _mark = sa.Column('HasLabel', sa.Boolean)

    # Autor
    _authors = sao.relationship('Person', secondary=ReferenceAuthor,
            order_by=ReferenceAuthor.c.Index)

    # Journal
    _periodical_fk = sa.Column('PeriodicalID',
                                sa.Integer,
                                sa.ForeignKey('Periodical.ID'))
    _periodical = sao.relationship('Periodical')


class Periodical(_Base):
    __tablename__ = 'Periodical'

    _id = sa.Column('ID', sa.Integer, primary_key=True)
    _name = sa.Column('Name', sa.String)


class Person(_Base):
    __tablename__ = 'Person'

    _id = sa.Column('ID', sa.Integer, primary_key=True)
    _lastname = sa.Column('LastName', sa.String)

Answer 1

As far as I know, you cannot use SQLAlchemy that way. The relationships only work for instances of Reference. One way to do this is via column properties or hybrid attributes, but the clearest way I find is to just do a join and then a sort. The way to do this is as follows:

stmnt = session.query(Reference) \
        .join(Periodical) \
        .order_by(Periodical._name) \
        .filter(Reference._mark == False)

print(stmnt)

This results in the following sql code:

SELECT "Reference"."ID" AS "Reference_ID", "Reference"."HasLabel" AS "Reference_HasLabel", "Reference"."PeriodicalID" AS "Reference_PeriodicalID" 
FROM "Reference" JOIN "Periodical" ON "Periodical"."ID" = "Reference"."PeriodicalID" 
WHERE "Reference"."HasLabel" = 0 ORDER BY "Periodical"."Name"

Following your comments, if you want to extend it to include person author you need something like this:

stmnt = session.query(Reference).\
    filter_by(_mark=False).\
    join(Periodical).\
    join(ReferenceAuthor, and_(ReferenceAuthor.c.ReferenceId == Reference.ID), ReferenceAuthor.c.Index == 0)).\
    join(Person).\
    order_by(Periodical._name).\
    order_by(Person._lastname)