Get rows from related table (ORM object) via array_agg()

Question

I want to get the product table data array using the array_agg function. In Postgers, this works great, but in SQLalchemy this can only be done with data types such as integer, strings, and so on. How do I implement this in SQLalchemy?

Code in postgresql:

   select DISTINCT
        seller.title, array_agg(product), COUNT(product.id)
        from seller_product
            INNER JOIN seller ON seller.ozon_id = seller_product.id_seller 
            INNER JOIN product ON product.ozon_id = seller_product.id_product 
        WHERE start_id = 36
        GROUP BY seller.title ORDER BY COUNT(product.id) DESC

Code python. I'm trying to do it like this:

slct_stmt_now = select(Seller, func.array_agg(Product.__table__), func.count(Product.id)).distinct().select_from(seller_product)
    slct_stmt_now = slct_stmt_now.join(Seller, Seller.ozon_id == seller_product.columns["id_seller"]).join(Product, Product.ozon_id == seller_product.columns["id_product"]).where(seller_product.columns["start_id"] == LAST_START_ID).group_by(Seller)
now_data_txt = session.execute(slct_stmt_now.order_by(func.count(Product.id).desc())).all()

Answer 1

The GitHub discussion that @snakecharmerb cites above includes the comment

relationship does exactly what you are looking for in an object oriented way.

# 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')])
    """