CODEWITHSUNDEEP
python-2.7flasksqlalchemymany-to-manyflask-sqlalchemy
user2990084
user2990084

Reputation: 2840

Set relation to many-to-many relationship

I have this many-to-many relationship that works correctly. However, now I need to have another class with a relation to this many-to-many.

currencies = db.Table('currencies_many',
    db.Column('id', db.Integer, primary_key=True),
    db.Column('currency_id', db.Integer, db.ForeignKey('currencies.id')),
    db.Column('bank_id', db.Integer, db.ForeignKey('banks.id'))
)

class Bank(db.Model):
    __tablename__ = 'banks'
    id = db.Column(db.Integer, primary_key=True)
    bank_name = db.Column(db.String(300))
    currencies = db.relationship('Currency', secondary=currencies,
        backref=db.backref('banks', lazy='dynamic'))

class Currency(db.Model):
    __tablename__ = 'currencies'
    id = db.Column(db.Integer, primary_key=True)
    currency_name = db.Column(db.String(300))

What I mean is, for example, an order, I need to have the association to many to many.

class Order(db.Model):
    __tablename__ = 'orders'
    id = db.Column(db.Integer, primary_key=True)
    bank_currency_identification = db.Column(db.Integer, db.ForeignKey('currencies_many.id'))

How can I do that? In my example I don't have db.relationship for bank_currency_identification, it is correct?

Upvotes: 5

Views: 591

Answers (1)

pech0rin
pech0rin

Reputation: 5036

So if I understand your question correctly, you want to reference the currencies_many table from your orders table. If so, you are correct in having a foreign key relationship with the currencies_many table.

However, down the road you may come into some trouble when you want to query orders from your banks table. I would suggest, although it seems redundant, to create a one-to-many relationship between Order and Bank as well as between Order and Currency.

bank_id = db.Column(db.Integer, db.ForeignKey('bank.id'))
currency_id = db.Column(db.Integer, db.ForeignKey('currency.id'))

And then in the Bank class

orders = db.relationship('Order', backref='bank')

This gives you a much cleaner querying interface. 

bank_orders = bank.orders

As well as makes your data model cleaner. It would be awkward to have to query orders from an intermediate table that also houses the currency. Just my two cents, but having an easy to understand Data model is better than making awkward relationships to save some redundancy.

Upvotes: 2

Related Questions