'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 Item
s 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 |
---|