Could not determine join condition with many-to-many join Sqlalchemy

Question

I am getting the following error in SQLAlchemy:

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

I am trying to create a many-to-many relationship between the User and Event tables. The objective is to have User be added as "Members" of an Event. The event is also "Owned" by one user but this is a different one-to-many relationship.

class Membership(db.Model):
    __tablename__ = 'event_membership'

    id = db.Column(db.Integer, primary_key = True, unique=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True),
    event_id = db.Column(db.Integer, db.ForeignKey('event.id'), primary_key=True),
    time_created = db.Column(db.DateTime,nullable = False, default = func.now())

    user = db.relationship("Event",back_populates="memberships",foreign_keys=[])
    event = db.relationship("User",back_populates="members")


class User(db.Model,UserMixin):
    __tablename__ = 'user'
    id = db.Column(db.Integer(),primary_key =True)
    firstname = db.Column(db.String(50))
    lastname = db.Column(db.String(50))
    password = db.Column(db.String(255))
    username = db.Column(db.String(50), index = True)
    email = db.Column(db.String(255), index = True)
    create_date = db.Column(db.DateTime())
    questions = db.relationship('Question',backref='user',lazy='dynamic')
    events = db.relationship('Event',backref='owner',lazy='dynamic')
    admin = db.Column(db.Boolean,nullable=False,default=False)
    #moderating_events = db.Column(db.Integer(),db.ForeignKey('event.id'))
    authenticated = db.Column(db.Boolean,nullable=False,default=False)
    lastseen = db.Column(db.DateTime)
    validated = db.Column(db.Boolean,nullable=False,default=False)

    memberships = db.relationship("Membership", back_populates= "user" )

class Event(db.Model):
    __tablename__ = 'event'
    id = db.Column(db.Integer(),primary_key = True)
    name = db.Column(db.String(255))
    event_code = db.Column(db.String(30))
    description = db.Column(db.Text())
    owner_id = db.Column(db.Integer(),db.ForeignKey('user.id'))
    #owner = db.relationship("User",back_populates="events")
    questions = db.relationship('Question',backref='event',lazy='dynamic')
    create_date = db.Column(db.DateTime())
    active_date = db.Column(db.DateTime())
    active = db.Column(db.Boolean())
    meeting_time = db.Column(db.DateTime())
    #moderators = db.relationship('User', backref='event_moderator',lazy='dynamic')
    moderate_questions = db.Column(db.Boolean,nullable=False,default=True)
    allow_anonymous = db.Column(db.Boolean,nullable=False,default=True)

    members = db.relationship("Membership", back_populates="event")

Espoir Murhabazi Espoir Murhabazi · Accepted Answer · 2017-03-26T19:39:16

This error is not new to me! I face it 2 month ago and I resolve it in this way for many to many relationship you will have to edit your association table "Membership". then you can pass it as secondary argument of relationship in user

try this code:

Membership =db.Table(
'Membership',
db.Column('User_id',db.Integer,db.ForeignKey('User.id')),
db.Column('Event_id', db.Integer, db.ForeignKey('Event.id')),

and in User Table table add:

 Events = relationship("Event",
                secondary=Membership)

find more here in the official documentation of SqlALchemy

Could not determine join condition with many-to-many join Sqlalchemy

1 Answers