Flask-SqlAlchemy Many-To-Many relationship with duplicates allowed

Question

I have an Order - FoodItem Many-To-Many relationship that is as follows:

association_table = db.Table('association', db.Model.metadata,
    db.Column('left_id', db.Integer, db.ForeignKey('orders.order_id')),
    db.Column('right_id', db.Integer, db.ForeignKey('fooditems.fooditem_id'))
)

class OrderModel(ReviewableModel):
    __tablename__ = 'orders'
    order_id = db.Column(db.Integer, db.ForeignKey('reviewables.id'), primary_key=True)
    food_items = db.relationship("FoodItemModel", secondary = association_table)
    __mapper_args__ = {'polymorphic_identity':'orders'}

class FoodItemModel(ReviewableModel):
    __tablename__ = 'fooditems'
    fooditem_id = db.Column(db.Integer, db.ForeignKey('reviewables.id'), primary_key=True)  
    __mapper_args__ = {'polymorphic_identity':'fooditems'}

The user can request an order with duplicate foodItems. This is created properly, but when I save the changes to the database, the duplicates are removed. For e.g., I order 3 Pizzas:

def save_to_db(self):
    print('before: '+str(self.food_items))
    db.session.add(self)
    db.session.commit()
    print('after: '+str(self.food_items))

The output is like this:

before: [<FoodItemModel u'Pizza'>, <FoodItemModel u'Pizza'>, <FoodItemModel u'Pizza'>]
after: [<FoodItemModel u'Pizza'>]

The association table is updated properly:

"left_id"   "right_id"
"6"         "3"
"6"         "3"
"6"         "3"

However, the food_items in the OrderModel only contains 1 item

Answer 1

What Juan Mellado was getting at in his answer is that the relational data (RD) and object relational mapping (ORM) clash: the ORM cannot distinguish separate objects that have the same data. To solve this, simply add an id column as primary key to the association_table - that way the ORM has something to distinguish different records with the same left_id and right_id.

But that would be a workaround and not a solution.

The solution is in thinking about what it means when "The user can request an order with duplicate foodItems". The relation from the order to the food is not direct, it is indirect via an order-item. Each order-item belongs to an order (which in turn belongs to a customer or a dining-table) and each order-item can have a relation with a food item. By making each order-item unique, the problem of "duplicate food-items" disappears. At the same time, we can now have an infinite amount of variations of the food-item by adding an optional "customer request" to each order item. E.g. "food: fries, request: easy on the salt".

Below a demonstration in code where customer "I scream" places 1 order with 3 portions of "ice cream" of which 1 portion is "with sprinkles on top".

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()