Multi tool use

Recipe / Bill of Materials MySQL Query

I am writing a Bill of Materials/Recipe App for Minecraft in Node.js, Express, React and MySQL.

I have what I think is a good structure for the database with the tables, but I am running into an issue with the Queries I need to run. Here is an example of what I am trying to do with an example output (I know the recipe is not correct MC players).

Ingredients Table:

RECIPE || INGREDIENT || QTY || TYPE
Piston || Redstone || 1 || RM
Piston || Iron Ingot || 1 || RM
Piston || Wood Planks || 3 || RM
Piston || Stone || 4 || RM
Sticky Piston || Piston || 2 || CO
Sticky Piston || Slimeball || 1 || RM


What I want is that when I Query for a Sticky Piston, there is a table returned with all of the required components summed together (on top) and all of the required Raw Materials summed together (below).

Example Required Output in Table format or JSON format:

INGREDIENT || QTY || TYPE
Piston || 2 || CO
Redstone || 2 || RM
Iron Ingot || 2 || RM
Wood Planks || 6 || RM
Stone || 8 || RM
Slimeball || 1 || RM


[
{
"Ingredient": "Piston",
"Qty": 2,
"Type": CO
},
{
"Ingredient": "Redstone",
"Qty": 2,
"Type": RM
},
{
"Ingredient": "Iron Ingot",
"Qty": 2,
"Type": RM
},
{
"Ingredient": "Wood Planks",
"Qty": 6,
"Type": RM
},
{
"Ingredient": "Stone",
"Qty": 8,
"Type": RM
},
{
"Ingredient": "Slimeball",
"Qty": 2,
"Type": RM
}
]


I assume it is a JOIN with some other fancy stuff that can do it all inside of MYSQL syntax, but I cannot figure it out despite two days of trying out different combinations.

Alternatively, I would be fine if instead of using MYSQL syntax, that JS was used that I can run on my Node.js server.

Here is a modified version of the schema per instructions from below. This is what my understanding of the relationship should be, but I still cannot figure out how to return all of the required resources when recipes are made up of other components that require other Materials as well.

CREATE TABLE Ingredients
(
MaterialID INT unsigned NOT NULL AUTO_INCREMENT,
Material VARCHAR(250) NOT NULL,
MaterialImage VARCHAR(250),
PRIMARY KEY (MaterialID)
);

CREATE TABLE Recipes
(
RecipeID INT unsigned NOT NULL AUTO_INCREMENT,
Recipe VARCHAR(250) NOT NULL,
PRIMARY KEY (ComponentID)
);

CREATE TABLE Recipes_Ingredients
(
RecipeID INT unsigned NOT NULL,
MaterialID INT unsigned NOT NULL,
Quantity INT unsigned NOT NULL
)


Thanks!

1 Answer
1

UPDATE/EDIT:

Updated to correct calculation of the Quantity

I've changed your table arrangement to better match the data you are trying to represent. Since a "recipe" can also be a "material", I've eliminated the recipe table, and stored that data in the materials table.

I also created a SQL Fiddle so you can play with the query and fine tune the results.

The first query below returns all "recipes" and their components. To get just one particular recipe, just add a condition to the where clause that selects the particular recipe.

If you would prefer to have the components listed in a single field, with one row per recipe, you can use the GROUP_CONCAT function and change the group by clause.

The second query below illustrates the GROUP_CONCAT function. It also shows how to change missing sub-ingredients from NULL to 'None'

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE `Ingredients`
(
`MaterialID` INT unsigned NOT NULL AUTO_INCREMENT,
`Material` VARCHAR(250) NOT NULL,
`MaterialImage` VARCHAR(250),
`IsRecipe` TINYINT(1) DEFAULT 0 NULL,
PRIMARY KEY (`MaterialID`)
);

CREATE TABLE `Recipes_Ingredients`
(
`id` INT unsigned NOT NULL AUTO_INCREMENT,
`RecipeID` INT unsigned NOT NULL,
`MaterialID` INT unsigned NOT NULL,
`Quantity` INT unsigned NOT NULL,
PRIMARY KEY (`id`)
);

INSERT INTO `Ingredients`
(`MaterialID`,`Material`,`MaterialImage`,`IsRecipe`)
VALUES
(1,'Redstone','redstone.jpg',0),
(2,'Iron Ingot','ironingot.jpg',0),
(3,'Wood Planks','woodplanks.jpg',0),
(4,'Stone','stone.jpg',0),
(5,'Slimeball','slimeball.jpg',0),
(6,'Piston','piston.jpg',1),
(7,'Sticky Piston','stickypiston.jpg',1),
(8,'Sticky Piston 2','stickypiston2.jpg',1);


INSERT INTO `Recipes_Ingredients`
(`RecipeID`,`MaterialID`,`Quantity`)
VALUES
(6,1,1),
(6,2,1),
(6,3,3),
(6,4,4),
(7,6,1),
(7,5,1),
(8,6,2),
(8,5,1);


Query 1:

SELECT
a.`MaterialID`,
c.`MaterialID`,
a.`Material` as `Recipe`,
a.`MaterialImage` as `RecipeImage`,
c.`Material` as `Ingredient`,
b.`Quantity` as `FirstIngredientQuantity`,
c.`MaterialImage` as `IngredientImage`,
IF(d.`Quantity` IS NULL,SUM(b.`Quantity`),COALESCE(d.`Quantity`,0)*b.`Quantity`) as `Quantity`,
e.`Material` as `Ingredient`,
e.`MaterialImage` as `MaterialImage`
FROM `Ingredients` a
LEFT JOIN `Recipes_Ingredients` b
ON b.`RecipeID` = a.`MaterialID`
LEFT JOIN `Ingredients` c
ON c.`MaterialID` = b.`MaterialID`
LEFT JOIN `Recipes_Ingredients` d
ON d.`RecipeID` = c.`MaterialID`
LEFT JOIN `Ingredients` e
ON e.`MaterialID` = d.`MaterialID` AND c.`IsRecipe` = 1
WHERE a.`IsRecipe` = 1 AND a.`MaterialID` in (7,8)
GROUP BY a.`MaterialID`,c.`MaterialID`,e.`MaterialID`


Results:

| MaterialID | MaterialID | Recipe | RecipeImage | Ingredient | FirstIngredientQuantity | IngredientImage | Quantity | Ingredient | MaterialImage |
|------------|------------|-----------------|-------------------|------------|-------------------------|-----------------|----------|-------------|----------------|
| 7 | 5 | Sticky Piston | stickypiston.jpg | Slimeball | 1 | slimeball.jpg | 1 | (null) | (null) |
| 7 | 6 | Sticky Piston | stickypiston.jpg | Piston | 1 | piston.jpg | 1 | Redstone | redstone.jpg |
| 7 | 6 | Sticky Piston | stickypiston.jpg | Piston | 1 | piston.jpg | 1 | Iron Ingot | ironingot.jpg |
| 7 | 6 | Sticky Piston | stickypiston.jpg | Piston | 1 | piston.jpg | 3 | Wood Planks | woodplanks.jpg |
| 7 | 6 | Sticky Piston | stickypiston.jpg | Piston | 1 | piston.jpg | 4 | Stone | stone.jpg |
| 8 | 5 | Sticky Piston 2 | stickypiston2.jpg | Slimeball | 1 | slimeball.jpg | 1 | (null) | (null) |
| 8 | 6 | Sticky Piston 2 | stickypiston2.jpg | Piston | 2 | piston.jpg | 2 | Redstone | redstone.jpg |
| 8 | 6 | Sticky Piston 2 | stickypiston2.jpg | Piston | 2 | piston.jpg | 2 | Iron Ingot | ironingot.jpg |
| 8 | 6 | Sticky Piston 2 | stickypiston2.jpg | Piston | 2 | piston.jpg | 6 | Wood Planks | woodplanks.jpg |
| 8 | 6 | Sticky Piston 2 | stickypiston2.jpg | Piston | 2 | piston.jpg | 8 | Stone | stone.jpg |


Query 2:

SELECT
a.`MaterialID`,
c.`MaterialID`,
a.`Material` as `Recipe`,
a.`MaterialImage` as `RecipeImage`,
c.`Material` as `Ingredient`,
c.`MaterialImage` as `MaterialImage`,
SUM(b.`Quantity` + COALESCE(d.`Quantity`,0)) as `Quantity`,
COALESCE(GROUP_CONCAT(CONCAT(e.`Material`,' (',b.`Quantity` * d.`Quantity`,') [',e.`MaterialImage`,']')),'None') as `Ingredients`
FROM `Ingredients` a
LEFT JOIN `Recipes_Ingredients` b
ON b.`RecipeID` = a.`MaterialID`
LEFT JOIN `Ingredients` c
ON c.`MaterialID` = b.`MaterialID`
LEFT JOIN `Recipes_Ingredients` d
ON d.`RecipeID` = c.`MaterialID`
LEFT JOIN `Ingredients` e
ON e.`MaterialID` = d.`MaterialID` AND c.`IsRecipe` = 1
WHERE a.`IsRecipe` = 1
GROUP BY a.`MaterialID`,c.`MaterialID`


Results:

| MaterialID | MaterialID | Recipe | RecipeImage | Ingredient | MaterialImage | Quantity | Ingredients |
|------------|------------|-----------------|-------------------|-------------|----------------|----------|-------------------------------------------------------------------------------------------------------------------|
| 6 | 1 | Piston | piston.jpg | Redstone | redstone.jpg | 1 | None |
| 6 | 2 | Piston | piston.jpg | Iron Ingot | ironingot.jpg | 1 | None |
| 6 | 3 | Piston | piston.jpg | Wood Planks | woodplanks.jpg | 3 | None |
| 6 | 4 | Piston | piston.jpg | Stone | stone.jpg | 4 | None |
| 7 | 5 | Sticky Piston | stickypiston.jpg | Slimeball | slimeball.jpg | 1 | None |
| 7 | 6 | Sticky Piston | stickypiston.jpg | Piston | piston.jpg | 13 | Redstone (1) [redstone.jpg],Iron Ingot (1) [ironingot.jpg],Wood Planks (3) [woodplanks.jpg],Stone (4) [stone.jpg] |
| 8 | 5 | Sticky Piston 2 | stickypiston2.jpg | Slimeball | slimeball.jpg | 1 | None |
| 8 | 6 | Sticky Piston 2 | stickypiston2.jpg | Piston | piston.jpg | 17 | Redstone (2) [redstone.jpg],Iron Ingot (2) [ironingot.jpg],Wood Planks (6) [woodplanks.jpg],Stone (8) [stone.jpg] |


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.