Flask SQLAlchemy relationship

Question

I have a pretty simple model in Flask and SQLAlchemy, with companies playing matches. A match is defined by a host and a guest. I don't know how to bring the host and the guest companies to the template, I am getting their IDs.

The code is like this:

class Company(db.Model):

    __tablename__ = 'companies'
    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String(64), unique = True)
    address = db.Column(db.String(120), unique = False)
    website = db.Column(db.String(100), unique = False)
    ...

class Match(db.Model):

    __tablename__ = 'matches'
    id = db.Column(db.Integer, primary_key = True)

    local_id =db.Column(db.Integer, db.ForeignKey('companies.id'))    
    guest_id = db.Column(db.Integer, db.ForeignKey('companies.id'))

    match_time = db.Column(db.DateTime())   # not important

I would like to be able to do something like this in the template:

{{ match.host.name }} - {{ match.guest.name }}

Any help would be greatly appreciated.

Answer 1

You need to have foreignkey relationship in sqlalchemy to enable the access.

for example,

Solution:

class Match(db.Model):
    __tablename__ = 'matches'

    id = db.Column(db.Integer, primary_key = True)

    local_id =db.Column(db.Integer, db.ForeignKey('companies.id'))    
    guest_id = db.Column(db.Integer, db.ForeignKey('companies.id'))

    local = db.relationship('Company', foreign_keys=local_id)
    guest = db.relationship('Company', foreign_keys=guest_id)

    match_time = db.Column(db.DateTime())   # not important

This will solve your problem.There is even backref keyword available to do reverse access if you need.

source

http://docs.sqlalchemy.org/en/rel_0_8/orm/relationships.html