result_query.sql

SELECT route.id, route.title, route.created_at FROM route

SELECT routes_points_m2m.route_id AS routes_points_m2m_route_id, routes_points_m2m.point_id AS routes_points_m2m_point_id, routes_points_m2m.start AS routes_points_m2m_start, routes_points_m2m."end" AS routes_points_m2m_end, routes_points_m2m."order" AS routes_points_m2m_order, anon_1.route_id AS anon_1_route_id FROM (SELECT route.id AS route_id FROM route) AS anon_1 JOIN routes_points_m2m ON anon_1.route_id = routes_points_m2m.route_id ORDER BY routes_points_m2m."order" ASC

-- Этот подзапрос не отдает поля "ST_X(point.coordinates) AS lat" и "ST_Y(point.coordinates) AS lon"
SELECT anon_1.id AS anon_1_id, anon_1.title AS anon_1_title, ST_AsEWKB(anon_1.coordinates) AS anon_1_coordinates, anon_1.created_at AS anon_1_created_at 
 FROM (SELECT point.id AS id, point.title AS title, point.coordinates AS coordinates, point.created_at AS created_at, ST_X(point.coordinates) AS lat, ST_Y(point.coordinates) AS lon, routes_points_m2m.start AS start, routes_points_m2m."end" AS "end", routes_points_m2m."order" AS "order" FROM point JOIN routes_points_m2m ON point.id = routes_points_m2m.point_id GROUP BY point.id) AS anon_1
 WHERE anon_1.id IN ($1::VARCHAR(36), $2::VARCHAR(36))

sqlalchemy_m2m_query.py

class RoutesPoints(Base):
    __tablename__ = "routes_points_m2m"

    route_id: Mapped[str] = mapped_column(ForeignKey("route.id"), primary_key=True)
    point_id: Mapped[str] = mapped_column(ForeignKey("point.id"), primary_key=True)
    start: Mapped[bool] = mapped_column(Boolean, default=False)
    end: Mapped[bool] = mapped_column(Boolean, default=False)
    order: Mapped[int] = mapped_column(Integer, nullable=False)

    point: Mapped["Point"] = relationship(
        "Point",
        back_populates="routes",
        innerjoin=True,
    )
    route: Mapped["Route"] = relationship(
        "Route",
        back_populates="points",
        innerjoin=True,
    )


class Point(Base):
    __tablename__ = "point"

    id: Mapped[str] = mapped_column(
        String(36),
        server_default=text("fn_uuid_time_ordered()"),
        primary_key=True,
    )
    title: Mapped[str] = mapped_column(String(length=100), nullable=False)
    coordinates: Mapped[Geometry] = mapped_column(
        Geometry("POINT", name="GEOMETRY", srid=4326),
        nullable=False,
    )
    created_at: Mapped[datetime] = mapped_column(
        TIMESTAMP(True),
        server_default=text("NOW()"),
    )

    routes: Mapped[list["RoutesPoints"]] = relationship(
        "RoutesPoints",
        back_populates="point",
    )


class Route(Base):
    __tablename__ = "route"

    id: Mapped[str] = mapped_column(
        String(36),
        server_default=text("fn_uuid_time_ordered()"),
        primary_key=True,
    )
    title: Mapped[str] = mapped_column(String(100), nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        TIMESTAMP(True),
        server_default=text("NOW()"),
    )

    points: Mapped[list["RoutesPoints"]] = relationship(
        "RoutesPoints",
        back_populates="route",
        order_by="asc(RoutesPoints.order)",
        innerjoin=True,
    )
 

async def get_routes(db_accessor: PostgresAccessor) -> Sequence[Route]:
    async with AsyncSession(db_accessor.engine) as session:
      points_subquery = select(
          Point,
          func.st_x(Point.coordinates).label("lat"),
          func.st_y(Point.coordinates).label("lon"),
      ).group_by(Point.id).subquery("pts")

      alias = aliased(Point, points_subquery)

      route_query = select(Route).options(
          subqueryload(Route.points)
          .joinedload(RoutesPoints.point.of_type(alias))
      )
      return (await session.scalars(route_query)).all()