Нормализованные сводки / строки MySQL в столбцы без уникальных имен

Question

Нормализованные сводки / строки MySQL в столбцы без уникальных имен

Кажется, я врезался в стену, пытаясь найти подходящий запрос для следующей базы данных (упрощенно):

Таблица «Пользователи»

userID    userName
1         John
2         Jane

Таблица «Ингредиенты»

ПРИМЕЧАНИЕ: эта таблица станет довольно большой!

ingredientID     ingredientName     ingredientImage
1                Tomatoes           tomato.png
2                Onions             onion.png
3                Red Pepper         redPepper.png

Таблица «Рецепты»

recipeID     userID     recipeName     description
1            1          Tomato Sauce   My homemade tomato sauce
2            1          Salsa          Spicy Salse
3            2          Bruschetta     Onion and Tomato Topping!

Таблица «RecipeIngredients»

ПРИМЕЧАНИЕ: каждый рецепт может содержать не более 5 ингредиентов, это будет ограничено при создании рецепта (во время вставки)

recipeIngredientID     recipeID     ingredientID     amount
1                      1            1                3
2                      2            1                2
3                      2            2                1
4                      3            1                2
4                      3            2                1
4                      3            3                2

Я хочу быть в состоянии сделать запрос, чтобы перечислить все рецепты и дал бы мне следующие данные в строке.

RecipeID
RecipeName
RecipeDescription
Идентификатор пользователя
UserName
Ingredient1
Ingredient1Amount
Ingredient2
Ingredient2Amount
Ingredient3
Ingredient3Amount
Ingredient4
Ingredient4Amount
Ingredient5
Ingredient5Amount
ПРИМЕЧАНИЕ: подходит только к Ingredient5, потому что в рецептах может быть максимум 5 ингредиентов.

Пример того, что я ищу

(1, «Томатный соус», «Мой домашний томатный соус», 1, «Джон», «Помидоры», «3», NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
(1, «Сальса», «Острая сальса», 1, «Джон», «Помидоры», 2, «Лук», 1, NULL, NULL, NULL, NULL, NULL, NULL)
(1, «Брускетта Топпинг», «Лук и помидор Топпинг», 2, «Джейн», «Помидоры», 2, «Лук», 1, «Красный перец», 2, NULL, NULL, NULL, NULL)

Пример того, что я ищу в табличном формате

RecipeID    RecipeName    RecipeDescription           UserID    UserName    Ingredient1    Ingredient1Image    Ingredient1Amount    Ingredient2    IIngredient1Image    Ingredient2Amount    Ingredient3    Ingredient1Image    Ingredient3Amount    Ingredient4    Ingredient1Image    Ingredient4Amount    Ingredient5    Ingredient1Image    Ingredient5Amount
1           Tomato Sauce  My homemade tomato sauce    1         John        Tomatoes       tomato.png          3                    NULL           NULL                 NULL                 NULL           NULL                NULL                 NULL           NULL                NULL                 NULL           NULL                NULL
2           Salsa         Spicy salsa                 1         John        Tomatoes       tomato.png          2                    Onions         onion.png            1                    NULL           NULL                NULL                 NULL           NULL                NULL                 NULL           NULL                NULL
3           Bruschetta    Onion and Tomato Topping    2         Jane        Tomatoes       tomato.png          2                    Onions         onion.png            1                    Red Pepper     redPepper.png       2                    NULL           NULL                NULL                 NULL           NULL                NULL

Любая помощь будет принята с благодарностью. Я посмотрел на «MAX (CASE …» заявления (как показано здесь https://dba.stackexchange.com/questions/47902/how-to-transpose-convert-rows-as-columns-in-mysql), но, похоже, не работает в этой ситуации, так как список ингредиентов в итоге будет очень большим (тогда как объекты в примере — довольно маленький набор).

РЕДАКТИРОВАТЬ
Какова лучшая практика для этого? Я вижу, что было бы проще вытащить рецепты, чем сделать вложенный цикл, чтобы вытащить ингредиенты, но это кажется интенсивным, особенно если число пользователей становится слишком большим. Я также рассмотрел вопрос о нормализации таблицы, но мне понадобятся данные, нормализованные для будущей аналитики / статистики, для потенциальных вопросов, таких как: сколько рецептов используют помидоры или что-то в этом роде.

У меня есть нормализованные таблицы, которые я хочу поместить в одну строку для отображения пользователю, и решение «MAX (CASE …») не работает для меня, так как набор данных будет очень большим

1

mysql php pivot-table sql

Решение

Другие решения

Других решений пока нет …

Источник

Accepted Answer

Вот пример использования нескольких LEFT JOINs. Присоединиться к пользователю довольно просто. Присоединение ингредиентов немного сложнее, так как вы должны присоединиться к RecipeIngredients стол, а затем использовать этот идентификатор, чтобы присоединиться к ингредиенту. (обратите внимание, что это только первые 2 ингредиента, следующие / последние 3 могут быть сделаны таким же образом)

SELECT
r.recipeID, r.recipeName, r.description,
u.userID, u.userName,
I1.ingredientName as Ingredient1, I1.ingredientImage as Ingredient1Image, rI1.amount as Ingredient1Amount,
I2.ingredientName as Ingredient2, I2.ingredientImage as Ingredient2Image, rI2.amount as Ingredient2Amount
FROM
Recipes r
LEFT JOIN
Users u
ON
u.userID = r.userID

/*First Ingredient*/
LEFT JOIN
RecipeIngredients rI1
ON
rI1.recipeIngredientID = (
SELECT
rIa.recipeIngredientID
FROM RecipeIngredients rIa
WHERE rIa.recipeID = r.recipeID ORDER BY rIa.recipeIngredientID LIMIT 0,1 )
LEFT JOIN
Ingredients I1
ON
I1.ingredientID = rI1.ingredientID

/*Second Ingredient*/
LEFT JOIN
RecipeIngredients rI2
ON
rI2.recipeIngredientID = (
SELECT
rIb.recipeIngredientID
FROM RecipeIngredients rIb
WHERE rIb.recipeID = r.recipeID ORDER BY rIb.recipeIngredientID LIMIT 1,1 )
LEFT JOIN
Ingredients I2
ON
I2.ingredientID = rI2.ingredientID

Вот рабочий sqlFiddle — http://sqlfiddle.com/#!2/3215e1/6

0