How to deal with several columns linking the same entity

Question

This example is only a schema for my database. I have the following table called recipes:

id | author_name | recipe1_id | recipe2_id | recipe3_id

All recipes are relations to the recipes_ingredients table:

id | recipe_id | ingredient1 | ingredient2 | ingredient3

Right now I do three JOINS on recipe1_id to recipe3_id like this:

SELECT recipe1.ingredient1 as recipe1ingredient1, recipe1.ingredient2 as recipe1ingredient2, recipe1.ingredient3 as recipe1ingredient3, [...]
FROM recipes
INNER JOIN recipe recipe1 ON (recipes.recipe1_id = recipes.id)
INNER JOIN recipe recipe2 ON (recipes.recipe2_id = recipes.id)
INNER JOIN recipe recipe3 ON (recipes.recipe3_id = recipes.id)

To map the 3 recipes into one bean in MyBatis, I have three resultsMaps which map each recipe into a recipe object (recipe1, recipe2, recipe3) containing 3 ingredients.

But: I want to have the recipes in a list. How could I achieve that?

PS: It is for sure, that I only have 3 entities in table recipes and table recipes_ingredients. This will never change, so I don't see a reason to have it in a n:m table.

Answer 1

I think this will work.

<resultMap id="recipe1" type="recipe1">
 ...
</resultMap>

<resultMap id="recipe2" type="recipe2">
 ...
</resultMap>

<resultMap id="recipe3" type="recipe3">
 ...
</resultMap>

<select id="getRecipes" parameterType="list" resultMap="recipe1, recipe2, recipe3">
   SELECT recipe1.ingredient1 as recipe1ingredient1, recipe1.ingredient2 as recipe1ingredient2, recipe1.ingredient3 as recipe1ingredient3, [...]
   FROM recipes
   INNER JOIN recipe recipe1 ON (recipes.recipe1_id = recipes.id)
   INNER JOIN recipe recipe2 ON (recipes.recipe2_id = recipes.id)
   INNER JOIN recipe recipe3 ON (recipes.recipe3_id = recipes.id)
</select>

Source: MyBatis multiple resultsets