What’s For Dinner? SALT!

This evening, I’ve been toying around with some SQL queries to model exactly how the “common recipe” search should behave.  This allows me to play with limits and values before I commit anything to code.

In reality, these efforts have exposed some of the culinary cruft that exists within the lower ranks of the major recipe websites.

First, I decided to rank each ingredient in the database with a commonality ranking.  I’ve decided that this ranking would be the total uses in the database divided by the total uses of the most used ingredient.  For example, salt is the most used ingredient in the database, with 28,016 recipes using it.  Salt would have a commonality of 1, since 28,016 / 28,016 = 1.  Granulated sugar is the second most common ingredient, with 25,389 uses.  It’s ranking would be 25389 / 28016.0, or  0.90623.  I created a view to generate these rankings as such:

CREATE VIEW IngredientCommonality AS
 WITH Ing AS (
 SELECT
 ShoppingIngredients.DisplayName,
 ShoppingIngredients.IngredientId,
 (SELECT Count(1) AS Count FROM RecipeIngredients WHERE RecipeIngredients.IngredientId = ShoppingIngredients.IngredientId) AS Uses
 FROM ShoppingIngredients
 )
 SELECT Ing.DisplayName, Ing.IngredientId, Ing.Uses, (Ing.Uses / (MAX(Ing.Uses::float) OVER ())) as Commonality FROM Ing;

Next, I decided to see the top 100 recipes in the database, sorted by how common their average ingredient was:

SELECT
 RecipeId,
 Title,
 (select AVG(Commonality) from RecipeIngredients inner join IngredientCommonality USING (IngredientId) where RecipeId=r.RecipeId) as AvgCommonality
FROM Recipes R ORDER BY AvgCommonality DESC LIMIT 100;

According to KitchenPC, the most common recipe on the entire Internet was crawled from AllRecipes.com, and is simply called “…”.  It appears to be some sort of test recipe, and its only ingredient is salt.  It has a perfect average commonality rating of 1.  You can check out this brilliant piece of culinary genius for yourself here.  By the way, it has a prep time of only 10 minutes, so it’s also an excellent choice for the busy single parent!

Also found was this gem on Food.com.  It’s called “Spa Cream” and consists of milk… and apparently more milk.

Ideally, I’d like to design this feature in such a way where the more common recipes appear at the top of the results, so users will see recipes they can most likely make at the top.  However, the fact that this exposes a lot of these junk recipes is a downside, to say the least.  One idea would be to filter out recipes that only call for a single ingredient, or devise other criteria to help avoid these less than useful matches.  On the bright side, it’s somewhat comforting that other large recipe sites (with huge budgets) also have less than perfect recipe databases.  Now it’s the job of KitchenPC to clean up the Internet!