<p>Juan Mellado在他的<a href="https://stackoverflow.com/a/49031036/3080094">answer</a>中得到的是关系数据(RD)和对象关系映射(ORM)冲突:ORM无法区分具有相同数据的单独对象。要解决这个问题,只需添加一个<code>id</code>列作为<code>association_table</code>的主键,这样ORM就可以区分具有相同<code>left_id</code>和{<cd4>}的不同记录。在</p>
<p>但这将是一个解决办法,而不是一个解决办法。在</p>
<p>解决方案是思考“用户可以请求具有重复foodItems的订单”的含义。从订单到食物的关系不是直接的,而是通过订单项目间接的。每个订单项目属于一个订单(订单又属于客户或餐桌),并且每个订单项目都可以与食品项目有关联。通过使每个订单项目都是唯一的,“重复食品项目”的问题就消失了。同时,我们现在可以通过在每个订购项目中添加一个可选的“客户请求”来获得无限量的食品种类。E、 g.“食物:炸薯条,要求:不含盐”。在</p>
<p>下面是一个代码演示,其中客户“我尖叫”下了1个订单,其中3份是“冰淇淋”,其中1份是“顶部有洒布”。在</p>
<pre class="lang-py prettyprint-override"><code>from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.pool import StaticPool
Base = declarative_base()
class Order(Base):
__tablename__ = 'order'
id = Column(Integer, primary_key=True)
customer = Column(String(127))
items = relationship("OrderItem")
def __repr__(self):
return "<Order(id='{}', customer='{}', items='{}')>".format(self.id, self.customer, self.items)
class Food(Base):
__tablename__ = 'food'
id = Column(Integer, primary_key=True)
name = Column(String(127))
def __repr__(self):
return "<Food(id='{}', name='{}')>".format(self.id, self.name)
class OrderItem(Base):
__tablename__ = 'order_item'
id = Column(Integer, primary_key=True)
order_id = Column(Integer, ForeignKey(Order.id))
order = relationship(Order)
food_id = Column(Integer, ForeignKey(Food.id))
food = relationship(Food)
comment = Column(String(127))
def __repr__(self):
return "<OrderItem(id='{}', order_id='{}', food_id='{}, comment={}')>" \
.format(self.id, self.order_id, self.food_id, self.comment)
def orderFood():
engine = create_engine('sqlite:///:memory:', echo=True, connect_args={'check_same_thread':False}, poolclass=StaticPool)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
food = Food(name='ice cream')
session.add(food)
order = Order(customer='I scream')
session.add(order)
session.commit()
print("Food: {}".format(food))
print("Order: {}".format(order))
order.items = [OrderItem(order=order, food=food), OrderItem(order=order, food=food), \
OrderItem(order=order, food=food, comment='with sprinkles on top')]
session.merge(order)
session.commit()
print("Order: {}".format(order))
print("Order.items")
for item in order.items:
print(item)
print("OrderItems for order")
orderFilter = OrderItem.order_id == order.id
for order_item in session.query(OrderItem).filter(orderFilter).all():
print(order_item)
print("Food in order")
for row in session.query(Food).join(OrderItem).filter(orderFilter).all():
print(row)
session.close();
if __name__ == "__main__":
orderFood()
</code></pre>