Flask SQLAlchemy query join

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

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.