-
-
Save kevinpeno/1129018 to your computer and use it in GitHub Desktop.
Sample Data for SQL Ingredient Selection
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- ---------------------------- | |
-- Fancy query to get the recipe ingredients the user is close to having. (not destructive) | |
-- In all versions of this, replace ? with User.name | |
-- ---------------------------- | |
SELECT RI.recipe, RI.ingredient | |
FROM RecipeIngredient AS RI | |
LEFT OUTER JOIN UserIngredient AS UI ON (UI.ingredient = RI.ingredient AND UI.user = ? ) | |
WHERE UI.user IS NULL | |
-- ---------------------------- | |
-- SQLite: Same as above, but destructive (group_concat, count, and GROUP BY destroy the relationship to the ingredient) | |
-- ---------------------------- | |
SELECT RI.recipe, COUNT( RI.ingredient ) as MissingIngredients, GROUP_CONCAT( RI.ingredient, ", " ) | |
FROM RecipeIngredient AS RI | |
LEFT OUTER JOIN UserIngredient AS UI ON (UI.ingredient = RI.ingredient AND UI.user = ? ) | |
WHERE UI.user IS NULL | |
GROUP BY recipe | |
ORDER BY MissingIngredients ASC | |
-- ---------------------------- | |
-- MySQL: Same as SQLite version above, but tweaked for syntax difference | |
-- For more details see: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat | |
-- ---------------------------- | |
SELECT RI.recipe, COUNT( RI.ingredient ) as MissingIngredients, GROUP_CONCAT( RI.ingredient SEPARATOR ", " ) | |
FROM RecipeIngredient AS RI | |
LEFT OUTER JOIN UserIngredient AS UI ON (UI.ingredient = RI.ingredient AND UI.user = ? ) | |
WHERE UI.user IS NULL | |
GROUP BY recipe | |
ORDER BY MissingIngredients ASC |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
GOAL: | |
To create a database of liquor in your liquor cabinet, and a database of possible drinks to make. | |
Output: | |
(1) a list of drinks you can make with your existing liquors, (hard!) and | |
(2) a list of drinks you could make if you just bought a few more things | |
=== | |
Views that currently exist on the DB: https://gist.github.com/1128805 | |
(the "view_recipes_in_cabinet_using_all_ingredients" isn't quite right - it only matches a recipe that has YOUR ENTIRE cabinet in it, not a non-exclusive subset) | |
Data setup: | |
table users -- The user database | |
users.identity = unique ID | |
table liquors -- A generic list of alcohol | |
liquors.id = unique ID | |
liquors.name = plaintext name | |
table liquorCabinet -- This is "your stockpile of liquor" as a user | |
liquorCabinet.userID = users.identity | |
liquorCabinet.liquorID = liquors.id | |
table recipes -- A list of possible recipe (drink) names | |
recipes.id = unique ID | |
recipes.name = plaintext name | |
table recipeIngredients -- The list of recipes | |
recipeIngredients.recipeID = recipes.id | |
recipeIngredients.liquorID = liquors.id | |
==== | |
Some sample data: | |
A single user (users.identity = 1) has two things in his liquor cabinet: | |
liquorCabinet.userID = 1 liquorCabinet.liquorID = 1 | |
liquorCabinet.userID = 1 liquorCabinet.liquorID = 2 | |
that corresponds to | |
liquors.id = 1 liquors.name = Vodka | |
liquors.id = 2 liquors.name = Tequila | |
There are two recipes in the system: | |
recipes.id = 1 recipes.name = Shot o' Vodka | |
recipes.id = 2 recipes.name = Gross Mix | |
Which correspond to: | |
recipeIngredients.recipeID = 1 recipeIngredients.liquorID = 1 | |
recipeIngredients.recipeID = 2 recipeIngredients.liquorID = 1 | |
recipeIngredients.recipeID = 2 recipeIngredients.liquorID = 2 | |
As it stands, this user can build both recipes as he has all of the necessary required liquors. |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- ---------------------------- | |
-- Table structures | |
-- If using MySQL, update each table definition to use INNODB or foreign keys will be pointless | |
-- Example: | |
-- CREATE TABLE "Ingredient" ( | |
-- "name" TEXT NOT NULL, | |
-- PRIMARY KEY ("name" ASC) | |
-- ) type INNODB; | |
-- ---------------------------- | |
CREATE TABLE "Ingredient" ( | |
"name" TEXT NOT NULL, | |
PRIMARY KEY ("name" ASC) | |
); | |
CREATE TABLE "Recipe" ( | |
"name" TEXT NOT NULL, | |
PRIMARY KEY ("name") | |
); | |
CREATE TABLE "RecipeIngredient" ( | |
"recipe" TEXT NOT NULL, | |
"ingredient" TEXT NOT NULL, | |
PRIMARY KEY ("recipe", "ingredient"), | |
CONSTRAINT "fkRecipeIngredientRecipe" FOREIGN KEY ("recipe") REFERENCES "Recipe" ("name") ON DELETE CASCADE ON UPDATE CASCADE, | |
CONSTRAINT "fkRecipeIngredientIngredient" FOREIGN KEY ("ingredient") REFERENCES "Ingredient" ("name") ON DELETE CASCADE ON UPDATE CASCADE | |
); | |
CREATE TABLE "User" ( | |
"name" TEXT NOT NULL, | |
PRIMARY KEY ("name") | |
); | |
CREATE TABLE "UserIngredient" ( | |
"user" TEXT NOT NULL, | |
"ingredient" TEXT NOT NULL, | |
PRIMARY KEY ("user", "ingredient"), | |
CONSTRAINT "fkUserCabinetUser" FOREIGN KEY ("user") REFERENCES "User" ("name") ON DELETE CASCADE ON UPDATE CASCADE, | |
CONSTRAINT "fkUserCabinetIngredient" FOREIGN KEY ("ingredient") REFERENCES "Ingredient" ("name") ON DELETE CASCADE ON UPDATE CASCADE | |
); | |
-- ---------------------------- | |
-- Get an ingredient with a recipe | |
-- ---------------------------- | |
CREATE VIEW "view_recipe_by_ingredient" AS | |
SELECT R.name AS recipe, RI.ingredient AS ingredient | |
FROM Recipe AS R | |
INNER JOIN RecipeIngredient AS RI ON R.name = RI.recipe; | |
-- ---------------------------- | |
-- Get the total ingredients the recipe has | |
-- ---------------------------- | |
CREATE VIEW "view_recipe_ingredient_total" AS | |
SELECT RbI.recipe, Count( RbI.ingredient ) as Total | |
FROM view_recipe_by_ingredient AS RbI | |
GROUP BY RbI.recipe; | |
-- ---------------------------- | |
-- Get the total ingredients I have | |
-- ---------------------------- | |
CREATE VIEW "view_cabinet_total" AS | |
SELECT UI.user, Count( UI.ingredient ) AS Total | |
FROM UserIngredient AS UI | |
GROUP BY UI.user; | |
-- ---------------------------- | |
-- Use me to get recipes that I can make with what is in my cabinet | |
-- ---------------------------- | |
CREATE VIEW "view_recipes_in_cabinet" AS | |
SELECT U.user, RbI.recipe, Count( RbI.ingredient ) AS TotalMatchedIngredients, RIT.Total as TotalRecipeIngredients | |
FROM UserIngredient AS U | |
INNER JOIN view_recipe_by_ingredient AS RbI ON( RbI.ingredient = U.ingredient ) | |
INNER JOIN view_recipe_ingredient_total AS RIT ON( RbI.recipe = RIT.recipe) | |
GROUP BY U.user, RbI.recipe | |
HAVING TotalRecipeIngredients = TotalMatchedIngredients; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- ---------------------------- | |
-- Test Data | |
-- ---------------------------- | |
INSERT INTO "Ingredient" VALUES ('Vodka'); | |
INSERT INTO "Ingredient" VALUES ('Rum'); | |
INSERT INTO "Ingredient" VALUES ('Sherry'); | |
INSERT INTO "Ingredient" VALUES ('Whiskey'); | |
INSERT INTO "Ingredient" VALUES ('Cherry'); | |
INSERT INTO "Ingredient" VALUES ('Orange'); | |
INSERT INTO "Ingredient" VALUES ('Olive'); | |
INSERT INTO "Ingredient" VALUES ('Gin'); | |
INSERT INTO "Ingredient" VALUES ('Lemon Juice'); | |
INSERT INTO "Ingredient" VALUES ('Orange Gin'); | |
INSERT INTO "Ingredient" VALUES ('Sweet Vermouth'); | |
INSERT INTO "Ingredient" VALUES ('7-Up'); | |
INSERT INTO "Ingredient" VALUES ('Cranberry Juice'); | |
INSERT INTO "Ingredient" VALUES ('Dekuyper Razzmatazz'); | |
INSERT INTO "Ingredient" VALUES ('Orange Juice'); | |
INSERT INTO "Ingredient" VALUES ('Sour Mix'); | |
INSERT INTO "Ingredient" VALUES ('Dry Cider'); | |
INSERT INTO "Ingredient" VALUES ('Lager'); | |
INSERT INTO "Ingredient" VALUES ('Sloe Gin'); | |
INSERT INTO "Ingredient" VALUES ('Southern Comfort'); | |
INSERT INTO "Ingredient" VALUES ('Triple Sec'); | |
INSERT INTO "Ingredient" VALUES ('Blue Curacao'); | |
INSERT INTO "Ingredient" VALUES ('Lime Juice'); | |
INSERT INTO "Ingredient" VALUES ('Red Curacao'); | |
INSERT INTO "Ingredient" VALUES ('Tequila'); | |
INSERT INTO "Recipe" VALUES ('Leap Year Cocktail'); | |
INSERT INTO "Recipe" VALUES ('Purple Gecko'); | |
INSERT INTO "Recipe" VALUES ('Slow Comfortable Screw In Between the Sheets'); | |
INSERT INTO "Recipe" VALUES ('Snakebite (UK)'); | |
INSERT INTO "Recipe" VALUES ('Stoner Delight'); | |
INSERT INTO "Recipe" VALUES ('Vodka'); | |
INSERT INTO "Recipe" VALUES ('Tequila'); | |
INSERT INTO "Recipe" VALUES ('Whiskey'); | |
INSERT INTO "RecipeIngredient" VALUES ('Leap Year Cocktail', 'Lemon Juice'); | |
INSERT INTO "RecipeIngredient" VALUES ('Leap Year Cocktail', 'Gin'); | |
INSERT INTO "RecipeIngredient" VALUES ('Leap Year Cocktail', 'Orange Gin'); | |
INSERT INTO "RecipeIngredient" VALUES ('Leap Year Cocktail', 'Sweet Vermouth'); | |
INSERT INTO "RecipeIngredient" VALUES ('Purple Gecko', 'Blue Curacao'); | |
INSERT INTO "RecipeIngredient" VALUES ('Purple Gecko', 'Cranberry Juice'); | |
INSERT INTO "RecipeIngredient" VALUES ('Purple Gecko', 'Lime Juice'); | |
INSERT INTO "RecipeIngredient" VALUES ('Purple Gecko', 'Red Curacao'); | |
INSERT INTO "RecipeIngredient" VALUES ('Purple Gecko', 'Sour Mix'); | |
INSERT INTO "RecipeIngredient" VALUES ('Purple Gecko', 'Tequila'); | |
INSERT INTO "RecipeIngredient" VALUES ('Slow Comfortable Screw In Between the Sheets', 'Orange Juice'); | |
INSERT INTO "RecipeIngredient" VALUES ('Slow Comfortable Screw In Between the Sheets', 'Sloe Gin'); | |
INSERT INTO "RecipeIngredient" VALUES ('Slow Comfortable Screw In Between the Sheets', 'Southern Comfort'); | |
INSERT INTO "RecipeIngredient" VALUES ('Slow Comfortable Screw In Between the Sheets', 'Triple Sec'); | |
INSERT INTO "RecipeIngredient" VALUES ('Slow Comfortable Screw In Between the Sheets', 'Vodka'); | |
INSERT INTO "RecipeIngredient" VALUES ('Snakebite (UK)', 'Dry Cider'); | |
INSERT INTO "RecipeIngredient" VALUES ('Snakebite (UK)', 'Lager'); | |
INSERT INTO "RecipeIngredient" VALUES ('Stoner Delight', '7-Up'); | |
INSERT INTO "RecipeIngredient" VALUES ('Stoner Delight', 'Cranberry Juice'); | |
INSERT INTO "RecipeIngredient" VALUES ('Stoner Delight', 'Dekuyper Razzmatazz'); | |
INSERT INTO "RecipeIngredient" VALUES ('Stoner Delight', 'Orange Juice'); | |
INSERT INTO "RecipeIngredient" VALUES ('Stoner Delight', 'Sour Mix'); | |
INSERT INTO "RecipeIngredient" VALUES ('Stoner Delight', 'Vodka'); | |
INSERT INTO "RecipeIngredient" VALUES ('Vodka', 'Vodka'); | |
INSERT INTO "RecipeIngredient" VALUES ('Tequila', 'Tequila'); | |
INSERT INTO "RecipeIngredient" VALUES ('Whiskey', 'Whiskey'); | |
INSERT INTO "User" VALUES ('Kevin'); | |
INSERT INTO "User" VALUES ('Andy'); | |
INSERT INTO "UserIngredient" VALUES ('Kevin', 'Orange Juice'); | |
INSERT INTO "UserIngredient" VALUES ('Andy', 'Rum'); | |
INSERT INTO "UserIngredient" VALUES ('Kevin', 'Blue Curacao'); | |
INSERT INTO "UserIngredient" VALUES ('Kevin', 'Sour Mix'); | |
INSERT INTO "UserIngredient" VALUES ('Kevin', '7-Up'); | |
INSERT INTO "UserIngredient" VALUES ('Andy', 'Lemon Juice'); | |
INSERT INTO "UserIngredient" VALUES ('Kevin', 'Cherry'); | |
INSERT INTO "UserIngredient" VALUES ('Kevin', 'Dry Cider'); | |
INSERT INTO "UserIngredient" VALUES ('Andy', 'Sweet Vermouth'); | |
INSERT INTO "UserIngredient" VALUES ('Andy', 'Blue Curacao'); | |
INSERT INTO "UserIngredient" VALUES ('Kevin', 'Sherry'); | |
INSERT INTO "UserIngredient" VALUES ('Andy', 'Tequila'); | |
INSERT INTO "UserIngredient" VALUES ('Kevin', 'Red Curacao'); | |
INSERT INTO "UserIngredient" VALUES ('Andy', 'Sherry'); | |
INSERT INTO "UserIngredient" VALUES ('Kevin', 'Sweet Vermouth'); | |
INSERT INTO "UserIngredient" VALUES ('Andy', 'Olive'); | |
INSERT INTO "UserIngredient" VALUES ('Andy', 'Dekuyper Razzmatazz'); | |
INSERT INTO "UserIngredient" VALUES ('Andy', 'Lime Juice'); | |
INSERT INTO "UserIngredient" VALUES ('Andy', 'Lager'); | |
INSERT INTO "UserIngredient" VALUES ('Kevin', 'Sloe Gin'); | |
INSERT INTO "UserIngredient" VALUES ('Andy', 'Southern Comfort'); | |
INSERT INTO "UserIngredient" VALUES ('Andy', 'Red Curacao'); | |
INSERT INTO "UserIngredient" VALUES ('Andy', 'Orange Gin'); | |
INSERT INTO "UserIngredient" VALUES ('Andy', 'Orange Juice'); | |
INSERT INTO "UserIngredient" VALUES ('Kevin', 'Rum'); | |
INSERT INTO "UserIngredient" VALUES ('Andy', 'Orange'); | |
INSERT INTO "UserIngredient" VALUES ('Kevin', 'Lemon Juice'); | |
INSERT INTO "UserIngredient" VALUES ('Andy', 'Whiskey'); | |
INSERT INTO "UserIngredient" VALUES ('Kevin', 'Lime Juice'); | |
INSERT INTO "UserIngredient" VALUES ('Kevin', 'Vodka'); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- ---------------------------- | |
-- Empty the UserIngredient table and put random values in it | |
-- Change the LIMIT to something interesting, like Count( User.name ) * 10 | |
-- MySQL: Change random() to RAND() | |
-- ---------------------------- | |
DELETE FROM UserIngredient; | |
INSERT INTO UserIngredient | |
SELECT U.name, I.name | |
FROM User U, Ingredient I | |
ORDER BY random() | |
LIMIT 30; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE VIEW "view_recipes_not_in_cabinet" AS | |
SELECT * | |
FROM RecipeIngredient | |
WHERE ingredient NOT IN( | |
SELECT ingredient FROM UserIngredient | |
); | |
-- ---------------------------- | |
-- Destructive: SQLite edition | |
-- ---------------------------- | |
CREATE VIEW "view_recipes_not_in_cabinet_by_ingredients_missing" AS | |
SELECT recipe, COUNT( RI.ingredient ) as MissingIngredients, GROUP_CONCAT( ingredient, ", " ) | |
FROM RecipeIngredient | |
WHERE ingredient NOT IN( | |
SELECT ingredient FROM UserIngredient | |
) | |
GROUP BY recipe | |
ORDER BY MissingIngredients ASC; | |
-- ---------------------------- | |
-- Destructive: MySQL edition | |
-- ---------------------------- | |
CREATE VIEW "view_recipes_not_in_cabinet_by_ingredients_missing" AS | |
SELECT recipe, COUNT( RI.ingredient ) as MissingIngredients, GROUP_CONCAT( ingredient SEPARATOR ", " ) | |
FROM RecipeIngredient | |
WHERE ingredient NOT IN( | |
SELECT ingredient FROM UserIngredient | |
) | |
GROUP BY recipe | |
ORDER BY MissingIngredients ASC; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Notes:
** If the above is true, then getMissingRecipeIngredients query can actually become a view. See the file named view_recipies_not_in_cabinet.sql