How to union two counts queries in SQLAlchemy?-CodePudding

I have two queries and the only difference between then is that one is counting the success status and the other failure status. Is there a way to get this result in just one query? I'm using SQLALchemy to do the queries.

success_status_query = (
    db_session.query(Playbook.operator, func.count(Playbook.operator).label("success"))
    .filter(Playbook.opname != "failed")
    .join(AccountInfo, AccountInfo.hardware_id == Playbook.hardware_id)
    .group_by(Playbook.operator)
)

failure_status_query = (
    db_session.query(Playbook.operator, func.count(Playbook.operator).label("failure"))
    .filter(Playbook.opname == "failed")
    .join(AccountInfo, AccountInfo.hardware_id == Playbook.hardware_id)
    .group_by(Playbook.operator)
)

CodePudding user response：

you can use the or_() operator like the sample bellow:

from sqlalchemy import or_

stmt = select(users_table).where(
                or_(
                    users_table.c.name == 'wendy',
                    users_table.c.name == 'jack'
                )
            )

It won't be a straight swap, but you will be able to work it out.

You can find more information in the SQLAlchemy documentation: https://docs.sqlalchemy.org/en/14/core/sqlelement.html#sqlalchemy.sql.expression.or_

CodePudding user response：

You can use conditions on Count, your quer will look like

success_status_query = (
    db_session.query(Playbook.operator
    ,func.count(case(
        [((Playbook.opname != "failed"), Playbook.operator)], else_=literal_column("NULL"))).label("success")]),
    func.count(case(
        [((Playbook.opname == "failed"), Playbook.operator)], else_=literal_column("NULL"))).label("failure")])
    .join(AccountInfo, AccountInfo.hardware_id == Playbook.hardware_id)
    .group_by(Playbook.operator)