CODEWITHSUNDEEP
flask-sqlalchemy
Unai
Unai

Reputation: 311

Using Flask-SQLAlchemy how to retrieve on jinja template helper many-to-many table extra fields

Using Flask SQLAlchemy, I'm trying to use a Many-to-Many relationship using a helper table with additional fields.

Model definition:

prodord = db.Table('prodord',
    db.Column('prod_id', db.Integer, db.ForeignKey('Product.id'), primary_key=True),
    db.Column('order_id', db.Integer, db.ForeignKey('AOrder.id'), primary_key=True),
    db.Column('quantity', db.Integer)
)


class Product(db.Model):
    __tablename__ = 'Product'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)


class User(db.Model):
    __tablename__ = 'User'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)


class AOrder(db.Model):
    __tablename__ = 'AOrder'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('User.id'))
    timedate = db.Column(db.String)
    products = db.relationship('Product', secondary=prodord, lazy='subquery',
        backref=db.backref('orders', lazy=True))

Invoking the template:

@app.route('/orders')
def page_orders():
    all_orders = AOrder.query.all()
    return render_template('orders.html', orders=all_orders)

How do I use the quantity value on the template? product.quantity is empty but p.name has value:

{% for o in orders %}
    <tr>
        <td>{{o.timedate}}</td>
        <td>
            {% for p in o.products %}
                {{p.quantity}} {{p.name}} <br />
            {% endfor %}
        </td>
    </tr>
{% endfor %}

Upvotes: 2

Views: 505

Answers (1)

Doobeh
Doobeh

Reputation: 9440

You've set up your relationship using an association table, which essentially becomes a transparent link between the Product and Order models.

Since you want to store extra data in that link table, you need to upgrade it to be an association object, just like your other models.

The documentation goes into the detail of how to do that, here's an example from there:

class Association(Base):
    __tablename__ = 'association'
    left_id = Column(Integer, ForeignKey('left.id'), primary_key=True)
    right_id = Column(Integer, ForeignKey('right.id'), primary_key=True)
    extra_data = Column(String(50))
    child = relationship("Child", back_populates="parents")
    parent = relationship("Parent", back_populates="children")


class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Association", back_populates="parent")


class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)
    parents = relationship("Association", back_populates="child")

Upvotes: 2

Related Questions