python

Applying Left Outer Join query in SQLAlchemy

You can apply outer join in SQLAlchemy using the outerjoin() method and then applying the condition on which column basis it will be joined with another table.

self.session.query(
    UserModel.id,
    UserModel.username.label("name")
    UserModel.email
).outerjoin(
    UserContactModel, 
    and_( 
        UserModel.is == UserContactModel.user_id, 
        UserContactModel.country == 'india'
    )
).filter(UserModel.role == 'user').all()

In the code example, we are joining two tables user and user_contact. We are using the outerjoin() method for this purpose and based on user id we are joining them.

Was this helpful?