Flask-SQLAlchemy many to many join condition

Question

I have 3 tables, Events, Invites, and Proposals. I would like to be able to query for events.invites and return a query of all invitees (and be able to get user's info from my user table, like events.invitees.all()[0].user_name) for that event. Likewise for proposals. Reading through the Flask-SQLAlchemy docs I saw how to make many-to-many tables. I've been trying to get them configured properly but am not having any luck. This is part of a Flask-App based off Overholt. Here are my tables:

events_invitees = db.Table(
    'events_invitees',
    db.Column('event_id', db.Integer(), db.ForeignKey('events.id')),
    db.Column('user_id', db.Integer(), db.ForeignKey('users.id'))
    )

events_proposals = db.Table(
    'events_proposals',
    db.Column('event_id', db.Integer(), db.ForeignKey('events.id')),
    db.Column('proposal_id', db.Integer(), db.ForeignKey('proposals.id'))
    )


class EventJsonSerializer(JsonSerializer):
    pass


class Event(EventJsonSerializer, db.Model):
    __tablename__ = 'events'

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    event_name = db.Column(db.String(30))
    event_mesg = db.Column(db.Text)
    start_date = db.Column(db.Date)
    end_date = db.Column(db.Date)
    event_status = db.Column(db.Boolean)
    rsvp = db.Column(db.Date)
    created = db.Column(db.DateTime, default=get_current_time)

    invitees = db.relationship('Invite',
                secondary=events_invitees,
                backref=db.backref('events', lazy='dynamic'))

    events_proposals = db.relationship('Event',
                        secondary=events_proposals,
                        backref=db.backref('events', lazy='dynamic'))


class InviteJsonSerializer(JsonSerializer):
    pass


class Invite(InviteJsonSerializer, db.Model):
    __tablename__ = 'invites'

    id = db.Column(db.Integer, primary_key=True)
    created = db.Column(db.Date, default=get_current_time)
    event_id = db.Column(db.ForeignKey('events.id'))
    user_id = db.Column(db.ForeignKey('users.id'))


class ProposalJsonSerializer(JsonSerializer):
    pass


class Proposal(ProposalJsonSerializer, db.Model):
    __tablename__ = 'proposals'

    id = db.Column(db.Integer, primary_key=True)
    created = db.Column(db.Date, default=get_current_time)
    date = db.Column(db.Date)
    event_id = db.Column(db.ForeignKey('events.id'))
    user_id = db.Column(db.ForeignKey('users.id'))

This is the error i'm getting: sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Event.invitees - there are no foreign keys linking these tables via secondary table 'events_invitees'. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify 'primaryjoin' and 'secondaryjoin' expressions.

Syed Habib M Syed Habib M · Accepted Answer · 2014-01-08T06:00:27

Can you try this:

Remove this line event_id = db.Column(db.ForeignKey('events.id')) and try like this on Invite table

class Invite(InviteJsonSerializer, db.Model):
    __tablename__ = 'invites'

    id = db.Column(db.Integer, primary_key=True)
    created = db.Column(db.Date, default=get_current_time)
    user_id = db.Column(db.ForeignKey('users.id'))

As of here we need not specify forignkey on child table because of using 'secondary' argument in parent class.

And also make a change on events_invitees "users.id" to "invites.id" like this

events_invitees = db.Table(
    'events_invitees',
    db.Column('event_id', db.Integer(), db.ForeignKey('events.id')),
    db.Column('invite_id', db.Integer(), db.ForeignKey('invites.id'))
)

Flask-SQLAlchemy many to many join condition

1 Answers