Sqlalchemy - How to use joinedload while doing a groupby function

Question

I am trying join a load (vendor) to my main object (service_order) while also running a sum function and groupby. Below is the code I am trying to run.

async def search_service_orders(
    session: AsyncSession,
    service_order_number: str = None,
    source_service_order_number: str = None,
    limit: int = 20,
    offset: int = 0,
) -> Sequence[ServiceOrderSearchModel]:
    async with session:
        statement = (
            select(
                DBServiceOrder,
                func.sum(DBServiceOrderItem.unit_price).label("total_price"),
            )
            .options(joinedload(DBServiceOrder.vendor))
            .outerjoin(
                DBServiceOrderItem,
                DBServiceOrder.service_order_id == DBServiceOrderItem.service_order_id,
            )
            .group_by(DBServiceOrder)
        )

        if service_order_number is not None:
            statement = statement.where(
                DBServiceOrder.service_order_number.ilike(f"%{service_order_number}%")
            )
        if source_service_order_number is not None:
            statement = statement.where(
                DBServiceOrder.source_service_order_number.ilike(
                    f"%{source_service_order_number}%"
                )
            )

        statement = statement.limit(limit).offset(offset)
        result = await session.execute(statement)
        list_of_service_orders = result.all()

This results in an error: ORA-00979: not a GROUP BY expression

Below is the sql that is generated.

[SQL: SELECT service_order.service_order_id, service_order.authorized_by, service_order.authorized_date, service_order.bill_to_addr, service_order.cancel_date, service_order.cancel_by, service_order.expected_delivery_date, service_order.orderd_by, service_order.order_date, service_order.quote_estimate_number, service_order.service_order_number, service_order.service_type, service_order.service_status, service_order.ship_to_addr, service_order.shipping_method, service_order.source_service_order_number, service_order.vendor_id, service_order.created_by, service_order.create_ts, service_order.updated_by, service_order.update_ts, sum(service_order_item.unit_price) AS total_price, vendor_1.vendor_id AS vendor_id_1, 
vendor_1.address_1, vendor_1.address_2, vendor_1.city, vendor_1.comment_txt, vendor_1.company_type, vendor_1.company_name, vendor_1.eog_msa_fl, vendor_1.fax_number, vendor_1.is_active, vendor_1.postal_code, vendor_1.phone_number, vendor_1.state_nm, vendor_1.vendor_number, vendor_1.vendor_uuid, vendor_1.created_by AS created_by_1, vendor_1.created_date, vendor_1.updated_by AS updated_by_1, vendor_1.updated_date
FROM service_order LEFT OUTER JOIN service_order_item ON service_order.service_order_id = service_order_item.service_order_id LEFT OUTER JOIN vendor vendor_1 ON vendor_1.vendor_id = service_order.vendor_id
WHERE lower(service_order.service_order_number) LIKE lower(:service_order_number_1) GROUP BY service_order.service_order_id, service_order.authorized_by, service_order.authorized_date, service_order.bill_to_addr, service_order.cancel_date, 
service_order.cancel_by, service_order.expected_delivery_date, service_order.orderd_by, service_order.order_date, service_order.quote_estimate_number, service_order.service_order_number, service_order.service_type, service_order.service_status, service_order.ship_to_addr, service_order.shipping_method, service_order.source_service_order_number, service_order.vendor_id, service_order.created_by, service_order.create_ts, service_order.updated_by, service_order.update_ts        
 OFFSET 0 ROWS
 FETCH FIRST 20 ROWS ONLY]
[parameters: {'service_order_number_1': '%SO%'}]

I then added the DBVendor object to the groupby statement like below:

.group_by(DBServiceOrder, DBVendor)

which result in error: sqlalchemy.exc.DatabaseError: (oracledb.exceptions.DatabaseError) ORA-00904: "VENDOR"."UPDATED_DATE": invalid identifier

This error makes no sense as all my tables are properly defined and I am able to joinload with vendor on my other queries just fine. Below is the sql query for this error:

[SQL: SELECT service_order.service_order_id, service_order.authorized_by, service_order.authorized_date, service_order.bill_to_addr, service_order.cancel_date, service_order.cancel_by, service_order.expected_delivery_date, service_order.orderd_by, service_order.order_date, service_order.quote_estimate_number, service_order.service_order_number, service_order.service_type, service_order.service_status, service_order.ship_to_addr, service_order.shipping_method, service_order.source_service_order_number, service_order.vendor_id, service_order.created_by, service_order.create_ts, service_order.updated_by, service_order.update_ts, sum(service_order_item.unit_price) AS total_price, vendor_1.vendor_id AS vendor_id_1, 
vendor_1.address_1, vendor_1.address_2, vendor_1.city, vendor_1.comment_txt, vendor_1.company_type, vendor_1.company_name, vendor_1.eog_msa_fl, vendor_1.fax_number, vendor_1.is_active, vendor_1.postal_code, vendor_1.phone_number, vendor_1.state_nm, vendor_1.vendor_number, vendor_1.vendor_uuid, vendor_1.created_by AS created_by_1, vendor_1.created_date, vendor_1.updated_by AS updated_by_1, vendor_1.updated_date
FROM service_order LEFT OUTER JOIN service_order_item ON service_order.service_order_id = service_order_item.service_order_id LEFT OUTER JOIN vendor vendor_1 ON vendor_1.vendor_id = service_order.vendor_id
WHERE lower(service_order.service_order_number) LIKE lower(:service_order_number_1) GROUP BY service_order.service_order_id, service_order.authorized_by, service_order.authorized_date, service_order.bill_to_addr, service_order.cancel_date, 
service_order.cancel_by, service_order.expected_delivery_date, service_order.orderd_by, service_order.order_date, service_order.quote_estimate_number, service_order.service_order_number, service_order.service_type, service_order.service_status, service_order.ship_to_addr, service_order.shipping_method, service_order.source_service_order_number, service_order.vendor_id, service_order.created_by, service_order.create_ts, service_order.updated_by, service_order.update_ts, vendor.vendor_id, vendor.address_1, vendor.address_2, vendor.city, vendor.comment_txt, vendor.company_type, vendor.company_name, vendor.eog_msa_fl, vendor.fax_number, vendor.is_active, vendor.postal_code, vendor.phone_number, vendor.state_nm, vendor.vendor_number, vendor.vendor_uuid, vendor.created_by, vendor.created_date, vendor.updated_by, vendor.updated_date   
 OFFSET 0 ROWS
 FETCH FIRST 20 ROWS ONLY]
[parameters: {'service_order_number_1': '%SO%'}]

Anyone know how I can use joinedload while also doing a func.sum and groupby?

I also tried adding a lazy relationship in the service_order table def but it gives me the same error as above...

Answer 1

I was able to solve this by using subqueryload instead.

statement = (
            select(
                DBServiceOrder,
                func.sum(DBServiceOrderItem.unit_price).label("total_price"),
            )
            .outerjoin(
                DBServiceOrderItem,
                DBServiceOrder.service_order_id == DBServiceOrderItem.service_order_id,
            )
            .group_by(DBServiceOrder)
            .options(subqueryload(DBServiceOrder.vendor))
        )