Получить строки из связанной таблицы (объект ORM) через array_agg()

Обсуждение на GitHub, которое @snakecharmerb цитирует выше, включает комментарий

отношения делают именно то, что вы ищете, объектно-ориентированным способом.

# https://stackoverflow.com/q/78676983/2144390
# fmt: off
from sqlalchemy import Column, ForeignKey, Integer, String, Table, create_engine, select
from sqlalchemy.orm import Session, declarative_base, joinedload, relationship
# fmt: on

engine = create_engine("postgresql://scott:[email protected]/test")
Base = declarative_base()


seller_product = Table(
    "seller_product",
    Base.metadata,
    Column(
        "id_seller", Integer, ForeignKey("seller.ozon_id"), primary_key=True
    ),
    Column(
        "id_product", Integer, ForeignKey("product.ozon_id"), primary_key=True
    ),
)


class Product(Base):
    __tablename__ = "product"
    id = Column(Integer, primary_key=True)
    ozon_id = Column(Integer, nullable=False)
    name = Column(String, nullable=True)

    def __repr__(self):
        return f"Product({repr(self.name)})"


class Seller(Base):
    __tablename__ = "seller"
    id = Column(Integer, primary_key=True)
    ozon_id = Column(Integer, nullable=False)
    title = Column(String, nullable=True)

    # using this instead of array_agg()
    products = relationship("Product", secondary=seller_product)

    def __repr__(self):
        return (
            f"Seller(title = {repr(self.title)}, products = {repr(self.products)})"
        )


engine.echo = True
with Session(engine) as sess:
    a_seller = sess.scalars(
        select(Seller).options(joinedload(Seller.products))
    ).first()
    """
SELECT seller.id, seller.ozon_id, seller.title, product_1.id AS id_1, product_1.ozon_id AS ozon_id_1, product_1.name 
FROM seller 
LEFT OUTER JOIN (
    seller_product AS seller_product_1 
    JOIN product AS product_1 
    ON product_1.ozon_id = seller_product_1.id_product
) 
ON seller.ozon_id = seller_product_1.id_seller
    """
    print(a_seller)
    """
Seller(title='Harbor Freight', products=[Product('wrench'), Product('hammer')])
    """