How to find the set of entities more recent than the last one with children

I have two SQLAlchemy model objects designated thus:

class SpecInstance(Base):
    spec_id = Column(Integer, ForeignKey('spec.spec_id'))
    details = Column(String)

class Spec(Base):
    spec_id = Column(Integer)
    spec_date = Column(DateTime)
    in开发者_高级运维stances = relationship(SpecInstance, backref="spec", cascade="all, delete, delete-orphan")

I am looking for a query that will return only those Spec objects that have a spec_date greater than the most recent one with instances. For example, given objects like these:

Spec(spec_id=1, spec_date='2010-10-01')
Spec(spec_id=2, spec_date='2010-10-02')
Spec(spec_id=3, spec_date='2010-10-03')

SpecInstance(spec_id=2, details='whatever')

I want my query to return only Spec 3. Spec 2 is ineligible because it has instances. Spec 1 is ineligible because it's older than Spec 2.

How do I do this?

---

I'm not testing this code since I'm pretty sure it will work and setting up the env is an overhead.

In plain SQL, one will do this with a subquery. In sqlalchemy, subqueries are created in this manner:

sq = session.query(Spec.spec_date.label('most_recent'))\
            .join((SpecInstance, SpecInstance.spec_id==Spec.spec_id))\
            .order_by(desc(Spec.spec_date))\
            .limit(1).subquery()

Here, we joined the two tables so only the Spec with SpecInstances are taken into account, then we order them by date so the latest are on top, and take only the first - the youngest with instances - and we only need its date. This will not be executed - it will be prepared as subquery in:

session.query(Spec)\
       .join((sq, Spec.spec_date>sq.c.most_recent))

which is pretty straightforward. Be careful to put double parentheses on the join constructs, and to include .c in the second query on sq, since 'most_recent' will be a dynamic column lookup.