CODEWITHSUNDEEP
pythonsqlsqlalchemyflask-sqlalchemy
L'Unità
L'Unità

Reputation: 115

Many-to-many relationships from multiple models using flask-sqlalchemy

For the Flask project which I am doing, I need to implement a feature that would enable multiple-authors for an article. However, the requirement is that the authors would come from two different classes (models) — that is,

  1. User
  2. Committee

These two models will have to be related with the third table, which is,

  1. Article

The models defined using flask-sqlalchemy as follows.

User model:

    class User(db.model):
        id = db.Column(db.Integer, primary_key=True)
        name = db.Column(db.UnicodeText)
        designation = db.Column(db.UnicodeText)

Committee model:

    class Committee(db.model):
        id = db.Column(db.Integer, primary_key=True)
        name = db.Column(db.UnicodeText)
        region = db.Column(db.UnicodeText)

Article model:

    class Article(db.model):
        id = db.Column(db.Integer, primary_key=True)
        title = db.Column(db.UnicodeText)
        body = db.Column(db.UnicodeText)
        authors = db.relationship()

I know the approach to implement a many-to-many relationship involving either Article-User or Article-Committee pairs. However, I am trying to implement Article-(User, Committee) relationships. That is, one article will have one or more user and/or committee as the author(s), and one user or one committee will have one or more articles. Is this possible at all?

Upvotes: 0

Views: 541

Answers (1)

Rory
Rory

Reputation: 169

The many to many relationship link provided is a step in the right direction. I would build an "Associations" table that connects all three primary keys as foreign keys. This will allow multiple entries for all three models and create the many to many relationship in all directions.

UML Diagram

Associations= db.Table('Associations',
    db.Column('FKID_USER', db.Integer, db.ForeignKey('User.id'), primary_key=True),
    db.Column('FKID_COMMITTEE', db.Integer, db.ForeignKey('Committee.id'), primary_key=True),
    db.Column('FKID_ARTICLE', db.Integer, db.ForeignKey('Article.id'), primary_key=True)
)

Based on your description, you may end up with null a value in one column for an entry.

Upvotes: 1

Related Questions