Flask SQLAlchemy query join

Question

I have 2 table like this:

class Role(db.Model):
    __tablename__ = 'roles'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    index = db.Column(db.String(64))
    users = db.relationship('User',
                            backref='role', lazy='dynamic')

class User(UserMixin, db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(64), unique=True, index=True)
    role_id = db.Column(db.Integer, db.ForeignKey('roles.id'))

Then I try to making 2 kinds of query to get data for the relationship models.

first, I make it like this:

user = db.session.query(User, Role.index).filter_by(email=form.email.data).first()

and the second one I use join statement on that:

user = db.session.query(User, Role.index).join(Role).filter(User.email==form.email.data).first()

My questions are, what's the difference in that query while in the second one I use the join statement but the result still same.

For the fast query or performance, should I use the first or the second one..?

Answer 1

The difference is that the first query will add both users and roles to FROM list, which results in a CROSS JOIN. In other words every row from users is joined with every row from roles. The second query performs an INNER JOIN and SQLAlchemy deduces the ON clause based on the foreign key relationship between the tables.

You should use the first one when you want a cartesian product, and the second one when you want the role related to the user by the foreign key relationship. That the result happens to be the same for you is just a coincidence.

For future reference, try enabling echo so that you can check from your logs what queries are actually emitted. Also have a look at defining ORM relationships, which would allow you to have a role attribute on User for accessing its related Role.

Answer 2

If your entities are from different classes/tables then joining is implied and SQL Alchemy will add it to actual SQL. You may add custom join if that connection isn't the one that SQL Alchemy uses (retrieved from foreign key or such).