How to use conditional operator or_ in sqlalchemy with conditional if?

Question

Existing code snippet:

if sup_usr_only:
    query_ob = query_ob.filter(
        or_(
            and_(
                Department.id.in_(login_user.department_ids),  # logic is OK - checked.
                model.visibility == VISIBLE_DEPT
            ),

            and_(
                model.visibility == VISIBLE_COMPANY,
                model.company_id == login_user.company_id
            ))
    )
else:
    query_ob = query_ob.filter(
        or_(
            and_(
                Department.id.in_(login_user.department_ids),  # logic is OK - checked.
                model.visibility == VISIBLE_DEPT
            ),
            model.visibility == VISIBLE_GLOBAL,
            and_(
                model.visibility == VISIBLE_COMPANY,
                model.company_id == login_user.company_id
            ))
    )

If there is a way so that I can minimize a piece of code in a line, if validation or any other optimization? Want to do this as shown below (which is syntactically incorrect):

query_ob = query_ob.filter(
            or_(
                and_(
                    Department.id.in_(login_user.department_ids),
                    model.visibility == VISIBLE_DEPT
                ),
                model.visibility == VISIBLE_GLOBAL if not sup_usr_only,
                and_(
                    model.visibility == VISIBLE_COMPANY,
                    model.company_id == login_user.company_id
                ))
        )

Answer 1

You can create your arguments up or_in the list up, and then apply them to the function or_():

options = [
    and_(
        Department.id.in_(login_user.department_ids),
        model.visibility == VISIBLE_DEPT
    ),
    and_(
        model.visibility == VISIBLE_COMPANY,
        model.company_id == login_user.company_id
    )]

if not sup_usr_only:
    options.append(model.visibility == VISIBLE_GLOBAL)

query_ob = query_ob.filter(or_(*options))

It doesn’t matter to the database in which order the parameters for the operator are listed OR, but if you think that the order refers to your application, you can also use options.insert(1, model.visibility == VISIBLE_GLOBAL).