TypeORM: @JoinTable with three columns

Question

i got a question on typeorm and the @JoinTable- and @RelationId-Decorator. Maybe anyone can help to answer my question, give me a hint or ideally solve my problem.

I am using nestjs with typeorm to provide a private api with recipes for my family and me.

If i break down the database structure to a minimum we got three main entities:

recipe
- id INT(11)
- name VARCHAR(255)
# PRIMARY KEY is id

ingredient
- id INT(11) // not autoincrement, fetched from https://www.programmableweb.com/api/chomp
- name VARCHAR(255)
- recipeId INT(11)
# PRIMARY KEY is a composite key (id, recipeId) 
# FOREIGN KEY is recipeId on recipe

step
- id INT(11)
- name VARCHAR(255)
- recipeId INT(11)
# PRIMARY KEY is id
# FOREIGN KEY is recipeId on recipe

So, these are my three main entities for my recipes. A recipe can have multiple steps (many-steps-to-one-recipe) and a recipe can have multiple ingredients (many-ingredients-to-one-recipe)

Now the complex part. Each step can have a relation to one or many ingredients. This results in the following relation-table.

ingredient_steps_step
- ingredientId INT(11)
- stepId INT(11)
- recipeId INT(11)
# PRIMARY KEY is a composite key (ingredientId, stepId, recipeId)
# FOREIGN KEYS are ingredientId on ingredient, stepId on step, recipeId on recipe

My ingredient.entity.ts looks like this:

@ManyToMany(type => Step, step => step.ingredients)
@JoinTable({
  name: 'ingredient_steps_step',
  joinColumns: [
    {name: 'ingredientId'},
    {name: 'recipeId'},
  ],
  inverseJoinColumns: [
    {name: 'stepId'},
  ],
})
steps: Step[];

@RelationId((ingredient: Ingredient) => ingredient.steps)
stepIds: number[];

@PrimaryColumn()
recipeId: number;

@PrimaryColumn()
id: number;

@ManyToOne(type => Recipe, recipe => recipe.ingredients)
recipe: Recipe;

The problem is, that my ingredients table gets filled, but the relation-table (ingredient_steps_step) does not get filled with the entries. The problem is, that there's no decorator like @RelationIds, where i can provide two columns for the relation to entity step.

It would be great if anyone of you could help me with this. Maybe it's necessary to provide you more information about the other entities?

kind regards,

digitalhacke

Did you get the answer you were looking for? Can you post it here... — Ashik

Kim Kern Kim Kern · Accepted Answer · 2019-02-26T17:19:22

Each relation should be in its own table. If you represent two relations in one table, you'll create duplication and might end up with inconsitencies.

Example

Let's assume you have a recipe MyRecipe with one step Step1 that has two ingredients. Now you want to move Step1 to another recipe OtherRecipe. Because the relation MyRecipe <-> Step1 is represented twice (duplication) you have to change multiple entries. If you forget one, you'll end up with corrupted data.

ingredient_steps_step:
MyRecipe <-> Step1 <-> IngredientA
MyRecipe <-> Step1 <-> IngredientB

I would model the data as follows:

steps:
Step1 -> MyRecipe

step_ingredients:
Step1 <-> IngredientA
Step1 <-> IngredientB

This way, you don't have duplication. I'm assuming that the ingredients of a recipe are the union of the ingredients of all its steps.

TypeORM: @JoinTable with three columns

1 Answers

Example