CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
Carl
Carl

Reputation: 161

Flask SQLAlchemy outerjoin with three tables

I'm trying to use an outerjoin() from SQLAlchemy to join three tables, however when I try the query I think should be working it throws the following error:

1054, "Unknown column 'articles.id' in 'on clause'"

I'm sure it could be done in an easier way by using the ORM references, but at the moment I'm trying to figure out how to make this work without that.

Below are the tables and query. I also import and_ from SQLAlchemy

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), index=True, unique=True, nullable=False)


class Articles(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    article_name = db.Column(db.String(100), unique=True, nullable=False)


class ReadArticles(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    article_id = db.Column(db.Integer, db.ForeignKey('articles.id'), nullable=False)

and the query:

r = db.session.query(Articles, User, ReadArticles).outerjoin(ReadArticles,
and_(User.id == ReadArticles.user_id, Articles.id == ReadArticles.article_id)).all()

Has anyone run into this or could point me in the direction of how to see what I'm doing wrong?

Thanks!

Upvotes: 0

Views: 4019

Answers (1)

Carl
Carl

Reputation: 161

Hopefully this will help someone stuck on a similar issue in the future.

I tried it without the and_, and modified the query a bit and this is what works, left/outer joins all three tables properly:

q = db.session.query(Articles, User, ReadArticles).filter(User.id == 1).outerjoin(ReadArticles, ReadArticles.article_id == Articles.id).all()

To make it a bit more readable:

q = db.session.query(Articles, User, ReadArticles)
q = q.filter(User.id == 1).outerjoin(ReadArticles,
     ReadArticles.article_id == Articles.id).all()

This performs a proper left/outer join (at least with a MySQL DB) across all three tables in the ORM classes I outlined above.

Upvotes: 2

Related Questions