What is wrong with my relationships in SQL Alchemy?

Question

I am using SQLAlchemy with Flask to create relationships for my application. I recently rewrote the relationships, and, no matter what I change, I keep getting the error:

sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between 
parent/child tables on relationship CurriculumVersion.enrollments - there are 
no foreign keys linking these tables.  Ensure that referencing columns are 
associated with a ForeignKey or ForeignKeyConstraint, or specify
a 'primaryjoin' expression.

On my models:

class User(db.Model, AuthUser):
    id = db.Column(db.Integer, primary_key=True)
    tf_login = db.Column(db.String(255), unique=True, nullable=False) # can assume is an email
    password = db.Column(db.String(120), nullable=False)
    salt = db.Column(db.String(80))
    role = db.Column(db.String(80)) # for later when have different permission types
    zoho_contactid = db.Column(db.String(20), unique=True, nullable=False)
    created_asof = db.Column(db.DateTime, default=datetime.datetime.utcnow)
    enrollments = db.relationship('Enrollment', backref='enrollment', lazy='dynamic')
    firstname = db.Column(db.String(80))
    lastname = db.Column(db.String(80))


    def __repr__(self):
        return '#%d tf_login: %s, First Name: %s Last Name: %s created_asof %s' % (self.id, self.tf_login, self.firstname, self.lastname, self.created_asof)

    def __getstate__(self):
        return {
            'id': self.id,
            'tf_login': self.tf_login,
            'firstname': self.firstname,
            'lastname': self.lastname,
            'role': self.role,
            'created_asof': self.created_asof,
        }

    @classmethod
    def load_current_user(cls, apply_timeout=True):
        data = get_current_user_data(apply_timeout)
        if not data:
            return None
        return cls.query.filter(cls.email==data['email']).one()
        return '#%d Course Name: %s, Course Version: %s, Closing Date: %s' %(self.id, self.course_name, self.course_version, self.closing_date)

class Enrollment(db.Model, AuthUser):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    version_id = db.Column(db.Integer, db.ForeignKey('curriculumversion.id'))
    cohort_id = db.Column(db.Integer, db.ForeignKey('cohort.id'))

    def __repr__(self):
        return '#%d User ID: %d Version ID: %d, Cohort ID: %d' %(self.id, self.user_id, self.version_id, self.cohort_id)

class Cohort(db.Model, AuthUser):
    id = db.Column(db.Integer, primary_key=True)
    days_to_completion = db.Column(db.String(20))
    course_id = db.Column(db.Integer, db.ForeignKey('course.id'))
    enrollments = db.relationship('Enrollment', backref='enrollment', lazy='dynamic')

    def __repr__(self):
        return '#%d Days To Completion: %s' %(self.id, self.days_to_completion)


class CurriculumVersion(db.Model, AuthUser):
    id = db.Column(db.Integer, primary_key=True)
    version_number = db.Column(db.String(6))
    date_implemented = db.Column(db.DateTime)
    course_id = db.Column(db.Integer, db.ForeignKey('course.id'))
    enrollments = db.relationship('Enrollment', backref='enrollment', lazy='dynamic')

    def __repr__(self):
        return '#%d Version Number: %s, Date Implemented: %s' %(self.id, self.version_number, self.date_implemented)

class Course(db.Model, AuthUser):
    id = db.Column(db.Integer, primary_key=True)
    course_code = db.Column(db.String(20))
    course_name = db.Column(db.String(50))
    versions = db.relationship('CurriculumVersion', backref='version', lazy='dynamic')
    cohorts = db.relationship('Cohort', backref='cohort', lazy='dynamic')


    def __repr__(self):
        return '#%d Course Code: %s, Course Name: %s' %(self.id, self.course_code, self.course_name)

Any help would be appreciated!

Solution 1

This error:

Could not determine join condition between parent/child tables on relationship CurriculumVersion.enrollments 

means that SQLAlchemy could not find a proper column in Enrollments to use as the foreign key in the relationship.

You defined the foreign key, but you used an incorrect table name. Flask-SQLAlchemy converts CamelCase classes to camel_case when creating the table, so you need to change this:

class Enrollment(db.Model, AuthUser):
    # ...
    version_id = db.Column(db.Integer, db.ForeignKey('curriculumversion.id'))
    #...

to this:

class Enrollment(db.Model, AuthUser):
    # ...
    version_id = db.Column(db.Integer, db.ForeignKey('curriculum_version.id'))
    #...

Alternatively you can use the __tablename__ attribute to override the default naming convention used by Flask-SQLAlchemy.

Solution 2

Try to use primaryjoin in your CurriculumVersion class as follows:

Change

enrollments = db.relationship('Enrollment', backref='enrollment', lazy='dynamic')

to

enrollments = db.relationship('Enrollment', backref='enrollment', lazy='dynamic', primaryjoin="Enrollment.version_id==CurriculumVersion.id")

Note: You might need to do this for the other classes as well.

Comments

[Jason Brooks]

That is giving me the error message: "sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'enrollment.version_id' could not find table 'CurriculumVersion' with which to generate a foreign key to target column 'id'" Any ideas?

[zzzeek]

there's detail missing from your example to fix this - the ForeignKey in the example refers to 'curriculumversion.id' but for some reason it's looking for a table named "CurriculumVersion" - what exactly does db.Model do, where is __tablename__, etc. ?

[Nick Stinemates]

This really pissed me off. Any idea why it does that?

[zzzeek]

because Flask thinks that creating names for you, rather than letting the developer explicitly produce these conventions, makes life easier. SQLAlchemy itself goes out of its way to never produce any kind of conventions, just gives you the tools to make your own.