SQLAlchemy cant do relationship with order table and product table with some additional columns

user_16990458 0
Viewed 50

Im having issue with my code

class OrderDetail:
__tablename__ = "orders_details"

id = db.Column("id", db.Integer, primary_key=True)
order_id = db.Column("order_id", db.Integer, db.ForeignKey("orders.id"), primary_key=True)
product_id = db.Column("product_id", db.Integer, db.ForeignKey("products.id"), primary_key=True)
quantity = db.Column("quantity", db.Integer, nullable=False)
unit_price = db.Column("unit_price", db.Integer, nullable=False)

def __repr__(self):
    return "<OrderDetail %r>" % self.id

class Order(db.Model): tablename = "orders"

id = db.Column("id", db.Integer, primary_key=True)
user_id = db.Column("user_id", db.Integer, db.ForeignKey("users.id"))
order_date = db.Column("order_date", db.DateTime(timezone=True), default=func.now())

products = db.relationship("Product", secondary="orders_details", backref=db.backref("owners_orders", lazy="dynamic"))

def __repr__(self):
    return "<Order %r>" % self.id

I have a Product, Order and OrderDetails table, how can I do the relationship between them? I try do this:

orders_details = db.Table("orders_details",
db.Column("order_id", db.Integer, db.ForeignKey("orders.id")),
db.Column("product_id", db.Integer, db.ForeignKey("products.id")),
db.Column("quantity", db.Integer, nullable=False),
db.Column("unit_price", db.Integer, nullable=False)

)

but this not work, because I want some columns in my table orders_details.

Can anyone help me with that?

flask-sqlalchemy flask relationship
1 Answers

You can do this in Sqlalchemy easily.

Create relationships like so:

class Association(Base):
    __tablename__ = 'association'
    left_id = Column(ForeignKey('left.id'), primary_key=True)
    right_id = Column(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")

Read here for detail: https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#association-object

To eagerly load relationships while querying, use something like:

query.options(joinedload(Parent.children).joinedload(Association.child))

Read here for detail: https://docs.sqlalchemy.org/en/13/orm/loading_relationships.html

user_19689090
Related