FastAPI - SQLAlchemy - Trouble returning most recent created child record with parent record

I have tried reading the SQLAlchemy documents and trawled many articles here and other places with nothing working. I will admit I am no expert in SQLAlchemy and the solution maybe very simple.

I have a FastAPI running on a local Postgres database with two models for a small sample tracking system I want to implement. Simplified models are below:

class Sample(Base):
    __tablename__ = "samples"

    id: Mapped[int] = mapped_column(primary_key=True, index=True, autoincrement=True)
    : Mapped[str] = mapped_column(String(32), index=True)
    ...
    time_created: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )

    logs: Mapped[list["StationLog"]] = relationship(back_populates="sample")


class LocationLog(Base):
    __tablename__ = "locationlogs"
    
    id: Mapped[int] = mapped_column(primary_key=True, index=True, autoincrement=True)
    ...
    time_created: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    sample_id: Mapped[int] = mapped_column(ForeignKey("samples.id"))

    sample: Mapped["Sample"] = relationship(back_populates="logs")

For the endpoint my schema returns a list of the related LocationLog in the field logs as expected.
I want to limit this to only the latest time_created, but everything i have tried to do this has failed resulting in either an error or all locations being returned for each sample rather than the most recent.

From my research it points to using a SQLAlchemy query like this:

subq = select(func.max(StationLog.time_created).label("max_created"), StationLog.sample_id).group_by(StationLog.sample_id).subquery()
query = select(Sample).join(subq, subq.c.sample_id == Sample.id)

db.scalars(query.order_by(Sample.id).offset(skip).limit(limit)).all()

However, this retrieves all of the related LocationLog records. I've also tried many other variations which either result in an error or just returning all of the related records.

Where am I going wrong? Thank in advance!

This is kind of a weird problem but I think this will work. Note that distinct() will generate a DISTINCT ON clause which is postgresql only. You might be able to combine the sub and extra LEFT OUTER JOIN but I don't know how. I think there are a few ways to do this.

    # return the first distinct row for each sample id, ordering by time_created descending for each sample id.
    # I added the station log id in the ordering as a tie breaker to make the results deterministic.
    sub = select(
        StationLog.sample_id,
        StationLog.id,
        StationLog.time_created
    ).order_by(
        StationLog.sample_id,
        StationLog.time_created.desc(),
        StationLog.id
    ).distinct(
        StationLog.sample_id
    ).subquery()
    # we outer join to the subquery to get the "latest" station log id
    # then outer join again to fetch the full set of columns to
    # build a complete StationLog
    stmt = select(
        Sample,
        StationLog
    ).select_from(
        Sample
    ).outerjoin(
        sub,
        Sample.id == sub.c.sample_id
    ).outerjoin(
        StationLog,
        sub.c.id == StationLog.id)
    result = session.execute(stmt)
    # note that station_log will be None when there are no logs
    for (sample, station_log) in result:
        print (sample, station_log)