Apply join based on multiple conditions SQLAlchemy

Implementation of joins on tables based on multiple conditions using SQLAlchemy queries.

from sqlalchemy import or_, and_

#USING AND CONDITION
session.query(
    EmployeeModel.name,
    EmployeeDepartment.dept_name
).join(
    EmployeeDepartment,
    and_(
        EmployeeDepartment.employee_id == EmployeeModel.id, 
        EmployeeDepartment.dept_code == 'P01'
    )
).all()


#USING OR CONDITION
session.query(
    EmployeeModel.name,
    EmployeeDepartment.dept_name
).join(
    EmployeeDepartment,
    or_(
        EmployeeDepartment.employee_id == EmployeeModel.id, 
        EmployeeDepartment.dept_code == 'P01'
    )
).all()

Joins in SQLAlchemy can be implemented using the .join() method. But if there is a requirement to join tables based on multiple conditions, you can also do that in SQLAlchemy passing conditions inside join().

In the code snippet, we are joining two tables employee and employee_department using two conditions:

EmployeeDepartment.employee_id == EmployeeModel.id, 
EmployeeDepartment.dept_code == 'P01'

We are using and_ operator from SQLAlchemy in the first query where the query will join the tables based on both conditions true but in or_ case the joining of the tables will depend on at least one condition match

Was this helpful?