Flask SqlAlchemy join two models without foreign key MYSQL

Question

I am joining two models without a foreign key:

Models:

class Users(db.Model):
    __tablename__ = "Users"
    userName = db.Column(db.String, primary_key=True)
    lastLogin = db.Column(db.DateTime)

class TimeOff
    __tablename__ = "timeOff"
    timeOffID = db.Column(db.Integer, primary_key=True)
    userName = db.Column("userName", db.String,   db.ForeignKey('appUsers.userName')),
    dayWork = db.Column(db.DateTime)

View:

result = db.session.query(models.Users).join(models.TimeOff)

sqlalchemy.exc.InvalidRequestError: Could not find a FROM clause to join from. Tried joining to but got: Can't find any foreign key relationships between 'TimeOff' and 'Users'.

I dont have a foreign key defined in table

Answer 1

You need to tell SQLAlchemy how to join the tables. Try something like this:

result = db.session.query(Users).join(TimeOff,Users.userName==TimeOff.userName)

Answer 2

Its an old post but I had a similar problem

result = session.query(models.Users).join(models.TimeOff, models.Users.userName == models.TimeOff.userName).all()

with this method, I can reach the features of the first object which is Users but not the TimeOff. I am wondering if it is possible to reach the secondary object's attributes. But I hope this helps.

Answer 3

To improve upon @Matt Healy's answer, if you also want to be able to access attributes on the joined object you can do something like:

user, timeOff = db.session.query(Users, TimeOff).join(
    TimeOff, Users.userName == TimeOff.userName
).first()

Then timeOff.dayWork etc. will give the information you need.

Answer 4

In my situation, I want to join the two objects so that I can get the Author information of the Posts, but I don't want to use the architecture-level foreign key design to associate the two tables.here is my codes:

class Posts(db.Model):
    id= db.Column(db.String, primary_key=True)
    author_code= db.Column(db.String)
    ...

class Author:
    id= db.Column(db.Integer, primary_key=True)
    name= db.Column(db.String),
    code= db.Column(db.String)
    address= db.Column(db.String)
    ...

we can add the code like this:

from sqlalchemy.orm import relationship

class Posts(db.Model):
    id= db.Column(db.String, primary_key=True)
    author_code= db.Column(db.String)
    ...
    # add this line
    author_info = relationship('Author',
                           foreign_keys=[author_code],
                           primaryjoin='Author.code == Posts.author_code')

then we can get author_info by query like this:

post_id = xxx # some id you defined
post = Posts.query.filter_by(id=post_id).one_or_none()

# here you can get `author_info` attributes you want.
print(dir(post),post.author_info)

You can learn more about this with link here,and read the documents Configuring how Relationship Joins — SQLAlchemy 1.4 Documentation

Answer 5

This worked for me, I have 3 tables, And I have the rtf_id which is unique to all three tables, you have to use the select_from keyword which tells the table starting from left.

db_data = dbobj.session.query(A, B, C). \
select_from(A).join(B, B.rtf_id == A.rtf_id). \
join(C, C.rtf_id == A.rtf_id).all()