SqlAlchemy: groupby of a union Error: SELECT construct for inclusion in a UNION or other set construct expected

Viewed 312

I trying to do 2 queries with different table, union them and then do a groupby with sum. Here is the PostgreSQL code snippet:

    SELECT      
       to_char(worklist.date,'YYYY-MM') AS year_month,
       department.department_name AS department_name,
       user.username AS user_name,     
       SUM(worklist.hour) AS TotalHour,
       SUM(worklist.overtime_hour) AS overtime_TotalHour,
       CAST(NULL AS numeric(20)) AS total_price
       FROM user, worklist, department
    WHERE user.id = worklist.user_id AND
          user.department_id = department.id
    group BY year_month, user_name, department_name
    UNION
    SELECT 
       to_char(expenditure.date,'YYYY-MM') AS year_month,
       department.department_name AS department_name,
       user.username AS user_name,          
       CAST(NULL AS numeric(20)) AS TotalHour,
       CAST(NULL AS numeric(20)) AS overtime_TotalHour,
       SUM(expenditure.price) AS total_price
    FROM user, department, expenditure
    WHERE user.id = expenditure.user_id AND 
          user.department_id = department.id
    group BY year_month, user_name, department_name
    ORDER BY year_month DESC

query result: PostgreSQL query result

I want to implement this in sqlalchemy and here is how i would approach it:

def get_allusers_monthly(db: Session):
    qry1 = (db.query(
            func.to_char(workhour.Workhour.date,'YYYY-MM').label('year_month'),
            department.Department.department_name.label('department_name'),
            user.User.username.label('user_name'),
            func.sum(workhour.Workhour.hour).label('total_hour'),
            func.sum(workhour.Workhour.overtime_hour).label('total_overtime_hour'),
            cast(expen.Expenditure.price, Numeric(10)).label('total_pric')
            ).filter(user.User.id == workhour.Workhour.user_id and user.User.department_id == department.Department.id
            ).group_by(
            'year_month',
            'department_name',
            'user_name',
            'total_pric'
            ).all())

    qry2 = (db.query(
            func.to_char(expen.Expenditure.date,'YYYY-MM').label('year_month'),
            department.Department.department_name.label('department_name'),
            user.User.username.label('user_name'),
            cast(workhour.Workhour.hour, Numeric(10)).label('total_hour'),
            cast(workhour.Workhour.overtime_hour, Numeric(10)).label('total_overtime_hour'),
            func.sum(expen.Expenditure.price).label('total_pric')
            ).filter(user.User.id == expen.Expenditure.user_id and user.User.department_id == department.Department.id
            ).group_by(
            'year_month',
            'department_name',
            'user_name',
            'total_hour',
            'total_overtime_hour'
            ).all())    

    all_queries = [qry1, qry2]
    golden_set = union(*all_queries).subquery()
    return golden_set

Here is the outputted that gets this error:

sqlalchemy.exc.ArgumentError: SELECT construct for inclusion in a UNION or other set construct expected

Where am I wrong? or is there has any better way to implement this

Can anybody please help?!

0 Answers
Related