Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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!
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.
                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.
– zzzeek
                Jul 24, 2013 at 21:55
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.
                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?
– Jason B
                Jul 19, 2013 at 20:21
                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. ?
– zzzeek
                Jul 20, 2013 at 1:00
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.