'Many-to-many relationship with parent with two releationships to same child

I'm trying to model a simple (at least I thought so) relationship like in a recipe (I'm using flask-sqlalchemy):

I have a Recipe, that has input materials and output products. Both materials and products are of type Item. This is what I've come up with so far:

class RecipeItemAmount(db.Model):
    """ Association between Recipe and Item with an amount. """
    __tablename__ = 'recipe_item_amount'
    recipe_id = db.Column(db.ForeignKey('recipe.id'), primary_key=True)
    item_id = db.Column(db.ForeignKey('item.id'), primary_key=True)
    amount = db.Column(db.Integer, nullable=False)
    item = db.relationship("Item")

    def __repr__(self):
        return f"{self.item}: {self.amount}"


class Recipe(db.Model):
    __tablename__ = 'recipe'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255), nullable=False)
    
    # Relationships
    materials = db.relationship("RecipeItemAmount")
    products = db.relationship("RecipeItemAmount")


class Item(db.Model):
    __tablename__ = 'item'
    id = db.Column(db.Integer, primary_key=True)
    base_price = db.Column(db.Integer, nullable=False)
    name = db.Column(db.String(255), nullable=False)

    def __repr__(self):
        return f"{self.name}"

The problem occurs in the relationships of the Recipe: SQLAlchemy warns me that:

relationship 'Recipe.products' will copy column recipe.id to column recipe_item_amount.recipe_id, which conflicts with relationship(s): 'Recipe.materials'

When I create two Items and add them to the same Recipe, with one as materials and one as products, then both materials and products are a list of the same two items, while i obviously want products to be one, and materials to be the other item.

material = RecipeItemAmount(
    amount=5,
    item=Item(
        base_price=1,
        name="Item1",
    ),
)
product = RecipeItemAmount(
    amount=1,
    item=Item(
        base_price=2,
        name="Item2",
    ),
)
recipe = Recipe(
    name="Pizza",
    materials=[material],
    products=[product],
)
db.session.add(recipe)
db.session.commit()

print(Recipe.query.first().materials) # OUTPUT: [Item1: 5, Item2: 1]
print(Recipe.query.first().products)  # OUTPUT: [Item1: 5, Item2: 1]
     warnings.warn(FSADeprecationWarning(
manage_db.py:33: SAWarning: relationship 'Recipe.products' will copy column recipe.id to column recipe_item_amount.recipe_id, which conflicts with relationship(s): 'Recipe.materials' (copies recipe.id to recipe_item_amount.recipe_id). If this is not the intention, consider if these relationships should be linked with back_populates, or if viewonly=True should be applied to one or more if they are read-only. For the less common case that foreign key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards.   To silence this warning, add the parameter 'overlaps="materials"' to the 'Recipe.products' relationship. (Background on this error at: https://sqlalche.me/e/14/qzyx)
  item=Item(
[Item1: 5, Item2: 1]
[Item1: 5, Item2: 1]

What's a better way to achieve what I want. It seems so easy, but I can't figure it out.

Edit: I found a solution, but I'm not happy with it: I can make two association tables. One for products and one for materials. There must be a better way, right?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source