Support for integration with USDA Nutrition Database

Yes there is a lot of info, but the main point would be to have the ability to print off nutrition facts about each recipe (ie, this recipe contains 22 grams of fat, 550 Calories, 60 mg of sodium, etc.)

Secondarily, the database has a fairly exaustive list of ingredients. It might help consistency on a large recipe database if the ingredients were validated against the list instead of just freely typed. But that may be too much of a pain to work with. That's why I'm looking for some kind of feedback. If we were going to do this, what would be the best way to go about it?

--Stephen

This has come up before, and I'd like to see the feature.

Units might be a problem though - you'd need to be able to translate, say, 1 cup flour into its equivalent weight.

Units would be the least of our problems if this was going to become a reality. There are simple mathematical conversions available for that, and I'm pretty sure those are all in the USDA database already. But as moonray pointed out, the database doesn't have every single ingredient known to mankind in it, and simple ingredients such as salt can become nedlessly complex when there are several types of salt to choose from in the database.

Does anyone know how these other web sites have overcome these problems? Perhaps there is an easier way to do this that I haven't thought of.

--Stephen

Using SR20, I've loaded the nutrient information into my own database, and thought you guys might find this useful.

My table definitions:
CREATE TABLE FOOD_DES (
`NDB_No` CHAR(5) NOT NULL,
`FdGrp_Cd` CHAR(4) NOT NULL,
`Long_Desc` CHAR(200) NOT NULL,
`Shrt_Desc` CHAR(60) NOT NULL,
`ComName` CHAR(100) NULL,
`ManufacName` CHAR(50) NULL,
`Survey` CHAR NULL,
`Ref_desc` CHAR(60) NULL,
`Refuse` INT NULL,
`SciName` CHAR(60) NULL,
`N_Factor` DOUBLE NULL,
`Pro_Factor` DOUBLE NULL,
`Fat_Factor` DOUBLE NULL,
`CHO_Factor` DOUBLE NULL
);

CREATE TABLE NUT_DATA (
`NDB_No` CHAR(5) NOT NULL,
`Nutr_No` CHAR(3) NOT NULL,
`Nutr_Val` DOUBLE NOT NULL,
`Num_Data_Pts` DOUBLE NOT NULL,
`Std_Error` DOUBLE NULL,
`Src_Cd` CHAR(2) NOT NULL,
`Deriv_Cd` CHAR(4) NULL,
`Ref_NDB_No` CHAR(5) NULL,
`Add_Nutr_Mark` CHAR NULL,
`Num_Studies` INT NULL,
`Min` DOUBLE NULL,
`Max` DOUBLE NULL,
`DF` INT NULL,
`Low_EB` DOUBLE NULL,
`Up_EB` DOUBLE NULL,
`Stat_cmt` CHAR(10) NULL,
`CC` CHAR NULL
);

CREATE TABLE FD_GROUP (
`FdGrp_Cd` CHAR(4) NOT NULL,
`FdGrp_Desc` CHAR(60) NOT NULL
);

CREATE TABLE NUTR_DEF (
`Nutr_No` CHAR(3) NOT NULL,
`Units` CHAR(7) NOT NULL,
`Tagname` CHAR(20) NULL,
`NutrDesc` CHAR(60) NOT NULL,
`Num_Dec` CHAR NOT NULL,
`SR_Order` INT NOT NULL
);

CREATE TABLE SRC_CD (
`Src_Cd` CHAR(2) NOT NULL,
`SrcCd_Desc` CHAR(60) NOT NULL
);

CREATE TABLE DERIV_CD (
`Deriv_Cd` CHAR(4) NOT NULL,
`Deriv_Desc` CHAR(120) NOT NULL
);

CREATE TABLE WEIGHT (
`NDB_No` CHAR(5) NOT NULL,
`Seq` CHAR(2) NOT NULL,
`Amount` DOUBLE NOT NULL,
`Msre_Desc` CHAR(80) NOT NULL,
`Gm_Wgt` DOUBLE NOT NULL,
`Num_Data_Pts` INT NULL,
`Std_Dev` DOUBLE NULL
);

CREATE TABLE FOOTNOTE (
`NDB_No` CHAR(5) NOT NULL,
`Footnt_No` CHAR(4) NOT NULL,
`Footnt_Typ` CHAR NOT NULL,
`Nutr_No` CHAR(3) NULL,
`Footnt_Txt` CHAR(200) NOT NULL
);

CREATE TABLE DATA_SRC (
`DataSrc_ID` CHAR(6) NOT NULL,
`Authors` CHAR(255) NULL,
`Title` CHAR(255) NOT NULL,
`Year` CHAR(4) NULL,
`Journal` CHAR(135) NULL,
`Vol_City` CHAR(10) NULL,
`Issue_State` CHAR(5) NULL,
`Start_Page` CHAR(5) NULL,
`End_Page` CHAR(5) NULL
);

CREATE TABLE DATSRCLN (
`NDB_No` CHAR(5) NOT NULL,
`Nutr_No` CHAR(3) NOT NULL,
`DataSrc_ID` CHAR(6) NOT NULL
);

CREATE TABLE FIELDINFO (`TABLENAME` VARCHAR(255), `FIELDNAME` VARCHAR(255), `DESCRIPTION` VARCHAR(255)
);

INSERT INTO `FIELDINFO` VALUES('FOOD_DES','NDB_No','5-digit Nutrient Databank number that uniquely identifies a food item');
INSERT INTO `FIELDINFO` VALUES('FOOD_DES','FdGrp_Cd','4-digit code indicating food group to which a food item belongs');
INSERT INTO `FIELDINFO` VALUES('FOOD_DES','Long_Desc','200-character description of food item');
INSERT INTO `FIELDINFO` VALUES('FOOD_DES','Shrt_Desc','60-character abbreviated description of food item. Generated from the 200-character description using abbreviations in appendix A. If short description was longer than 60 characters, additional abbreviations were made.');
INSERT INTO `FIELDINFO` VALUES('FOOD_DES','ComName','Other names commonly used to describe a food, including local or regional names for various foods, for example, “soda” or “pop” for “carbonated beverages”');
INSERT INTO `FIELDINFO` VALUES('FOOD_DES','ManufacName','Indicates the company that manufactured the product, when appropriate');
INSERT INTO `FIELDINFO` VALUES('FOOD_DES','Survey','Indicates if the food item is used in the USDA Food and Nutrient Database for Dietary Studies (FNDDS) and has a complete nutrient profile for a specified set of nutrients');
INSERT INTO `FIELDINFO` VALUES('FOOD_DES','Ref_desc','Description of inedible parts of a food item (refuse), such as seeds or bone');
INSERT INTO `FIELDINFO` VALUES('FOOD_DES','Refuse','Percentage of refuse');
INSERT INTO `FIELDINFO` VALUES('FOOD_DES','SciName','Scientific name of the food item. Given for the least');
INSERT INTO `FIELDINFO` VALUES('FOOD_DES','N_Factor','Factor for converting nitrogen to protein (see p. 7)');
INSERT INTO `FIELDINFO` VALUES('FOOD_DES','Pro_Factor','Factor for calculating calories from protein (see p. 8)');
INSERT INTO `FIELDINFO` VALUES('FOOD_DES','Fat_Factor','Factor for calculating calories from fat (see p. 8)');
INSERT INTO `FIELDINFO` VALUES('FOOD_DES','CHO_Factor','Factor for calculating calories from carbohydrate (see p. 8)');

INSERT INTO `FIELDINFO` VALUES('NUT_DATA','NDB_No','5-digit Nutrient Databank number');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Nutr_No','Unique 3-digit identifier code for a nutrient');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Nutr_Val','Amount in 100 grams, edible portion †');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Num_Data_Pts','Number of data points (previously called Sample_Ct)');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Std_Error','Standard error of the mean. Null if could not be calculated');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Src_Cd','Code indicating type of data');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Deriv_Cd','Data Derivation Code giving specific information on how the value was determined');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Ref_NDB_No','NDB number of the item used to impute a missing value. Populated only for items added or updated starting with SR14');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Add_Nutr_Mark','Indicates a vitamin or mineral added for fortification or enrichment. This field is populated for ready-to-eat breakfast cereals and many brand name hot cereals in food group 8.');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Num_Studies','Number of studies');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Min','Minimum value');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Max','Maximum value');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','DF','Degrees of Freedom');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Low_EB','Lower 95% error bound');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Up_EB','Upper 95% error bound');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Stat_cmt','Statistical comments. See definitions below.');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','CC','Confidence Code indicating data quality, based on evaluation of sample plan, sample handling, analytical method, analytical quality control, and number of samples analyzed. Not included in this release, but is planned for future releases.');

INSERT INTO `FIELDINFO` VALUES('FD_GROUP','FdGrp_Cd','4-digit code identifying a food group. Only the first 2 digits are currently assigned. In the future, the last 2 digits may be used. Codes may not be consecutive.');
INSERT INTO `FIELDINFO` VALUES('FD_GROUP','FdGrp_Desc','Name of food group');

INSERT INTO `FIELDINFO` VALUES('NUTR_DEF','Nutr_No','Unique 3-digit identifier code for a nutrient');
INSERT INTO `FIELDINFO` VALUES('NUTR_DEF','Units','Units of measure (mg, g, µg, and so on.)');
INSERT INTO `FIELDINFO` VALUES('NUTR_DEF','Tagname','International Network of Food Data Systems (INFOODS) Tagnames.† A unique abbreviation for a nutrient/food component developed by INFOODS to aid in the interchange of data');
INSERT INTO `FIELDINFO` VALUES('NUTR_DEF','NutrDesc','Name of nutrient/food component');
INSERT INTO `FIELDINFO` VALUES('NUTR_DEF','Num_Dec','Number of decimal places that a nutrient value is rounded to');
INSERT INTO `FIELDINFO` VALUES('NUTR_DEF','SR_Order','Used to sort nutrient records in the same order as various reports produced from SR');

INSERT INTO `FIELDINFO` VALUES('SRC_CD','Src_Cd','2-digit code');
INSERT INTO `FIELDINFO` VALUES('SRC_CD','SrcCd_Desc','Description of source code that identifies the type of nutrient data');

INSERT INTO `FIELDINFO` VALUES('DERIV_CD','Deriv_Cd','Derivation Code');
INSERT INTO `FIELDINFO` VALUES('DERIV_CD','Deriv_Desc','Description of derivation code giving specific information on how the value was determined');

INSERT INTO `FIELDINFO` VALUES('WEIGHT','NDB_No','5-digit Nutrient Databank number');
INSERT INTO `FIELDINFO` VALUES('WEIGHT','Seq','Sequence number');
INSERT INTO `FIELDINFO` VALUES('WEIGHT','Amount','Unit modifier (for example, 1 in “1 cup”)');
INSERT INTO `FIELDINFO` VALUES('WEIGHT','Msre_Desc','Description (for example, cup, diced, and 1-inch pieces)');
INSERT INTO `FIELDINFO` VALUES('WEIGHT','Gm_Wgt','Gram weight');
INSERT INTO `FIELDINFO` VALUES('WEIGHT','Num_Data_Pts','Number of data points');
INSERT INTO `FIELDINFO` VALUES('WEIGHT','Std_Dev','Standard deviation');

INSERT INTO `FIELDINFO` VALUES('FOOTNOTE','NDB_No','5-digit Nutrient Databank number');
INSERT INTO `FIELDINFO` VALUES('FOOTNOTE','Footnt_No','Sequence number. If a given footnote applies to more than one nutrient number, the same footnote number is used. As a result, this file cannot be indexed.');
INSERT INTO `FIELDINFO` VALUES('FOOTNOTE','Footnt_Typ','Type of footnote. D = footnote adding information to the food description; M = footnote adding information to measure description; N = footnote providing additional information on a nutrient value. If the Footnt_typ = N, the Nutr_No will also be filled in');
INSERT INTO `FIELDINFO` VALUES('FOOTNOTE','Nutr_No','Unique 3-digit identifier code for a nutrient to which footnote applies');
INSERT INTO `FIELDINFO` VALUES('FOOTNOTE','Footnt_Txt','Footnote text');

INSERT INTO `FIELDINFO` VALUES('DATA_SRC','DataSrc_ID','Unique number identifying the reference/source');
INSERT INTO `FIELDINFO` VALUES('DATA_SRC','Authors','List of authors for a journal article or name of sponsoring organization for other documents');
INSERT INTO `FIELDINFO` VALUES('DATA_SRC','Title','Title of article or name of document, such as a report from a company or trade association');
INSERT INTO `FIELDINFO` VALUES('DATA_SRC','Year','Year article or document was published');
INSERT INTO `FIELDINFO` VALUES('DATA_SRC','Journal','Name of the journal in which the article was published');
INSERT INTO `FIELDINFO` VALUES('DATA_SRC','Vol_City','Volume number for journal articles or books; city where sponsoring organization is located');
INSERT INTO `FIELDINFO` VALUES('DATA_SRC','Issue_State','Issue number for journal article; State where the sponsoring organization is located');
INSERT INTO `FIELDINFO` VALUES('DATA_SRC','Start_Page','Starting page number of article/document');
INSERT INTO `FIELDINFO` VALUES('DATA_SRC','End_Page','Ending page number of article/document');

INSERT INTO `FIELDINFO` VALUES('DATSRCLN','NDB_No','5-digit Nutrient Databank number');
INSERT INTO `FIELDINFO` VALUES('DATSRCLN','Nutr_No','Unique 3-digit identifier code for a nutrient');
INSERT INTO `FIELDINFO` VALUES('DATSRCLN','DataSrc_ID','Unique ID identifying the reference/source');

And the code to populate the database: (sorry - it's a bit messy, but I'm new to PHP!)

I think we might need primary key for the tables

Would probably be a pain, yeah...

now the other piece of this is how to deal with updates that come in periodically from USDA?

Hi all,
I've done a fair amount of work with the USDA SR19 database and have made some progress on a module that can tie into Recipe. As has been mentioned several times here, the biggest hurdle is making sense of the data. For instance, using a simple autocomplete search on "salt" gives you 643 possible USDA SR19 entries.

Slogging through the data, I found that the "long descriptions" are fairly uniform and in fact can be relatively easily parsed as a CSV. There were about 30 exceptions (about .5% based on 7293 entries) and most of these were what I consider to be mistakes in the data (e.g. "fast foods" category broken out as "Fast foods", "Fast Foods" and "Fast food"). I wrote a parser to break the CSV descriptions down into a tree and then created a couple of tables mapping the USDA nutrition number to the appropriate path through the tree. What that gives me is the ability expand and collapse the 7293 entries all the way up into the 24 top level groupings. This works well for most groups, however several of them are still very cumbersome to work with. For instance, the USDA group "Beverages" has 50 or 60 second level groups (as opposed to, say, "Baby Foods" which has 5).

Working with what I have right now, I put together a jQuery driven module for basically just displaying the USDA data and I'd love to hear some comments on how to improve it. The page is at http://www.eatandbefit.com/nutrition. What you'll see is a table with 24 cells, each containing one of the 24 USDA basic nutrition groups. The next line will be a (possibly ellipsis shortened) list of the first drill down into that level. Items that are gray in color are branch nodes; they have other descriptors beneath them. Items in blue are actual leaf nodes representing a line in the USDA nutrition database. These would be what you click on to add an ingredient to your recipe. The autocomplete input is there but non-functional.

Things I see immediately that I think need changing or don't know what to do with:

• The "path" through the tree should be a different color. For instance under Baby Foods: Child formula, MEAD JOHNSON, PORTAGEN, with iron, prepared with powder, the first four levels should be a different color other than gray to show where in the tree you are.
• The autocomplete needs to work. At this point I'm thinking the autocomplete should always reside at the level of the current tree depth so whatever you search for, the link to get there is assumed: using the above example, at the "Child formula, MEAD JOHNSON, PORTAGEN" level, typing "with ..." into the autocomplete should give you 2 choices in the popup, "with iron, prepared from powder" and "with iron, powder, not reconstituted"
• An autocomplete at the top of each of the 24 major nutrition groups? Is this necessary?
• Presentation of realllly long sub groups. For instance, what to do with the second level of Beverages? A nice left/right scrolly thing would be cool, but practical? Worth trying to program? How should that be presented? Should there be something similar to the existing "... (more)"?
• Should blocks of data be made "re-collapsible"? That's a lot of work I think.
• Encapsulated in a form: right now, it's not a form. The whole thing needs to be presented so when you hit a child node, you can actually capture the data of that child node as input.
• Best way to use this in the Recipe module?
• Autocompletion animation for upper level blocks, to let you know when clicking on a keyword that it's actually searching.

Please post any thoughts on this you may have.

I tried running the above program & 3 data files do not get uploaded. They are DATSRCLN, FOOD_DES, NUT_DATA. I understand that they are huge data files. The error I get is Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 25720267 bytes) at line: $file = file_get_contents('C:/Recipe Database/sr20/'.$filename.'.txt');
Any help would be greatly appreciated. Is this due to RAM of my machine. I have a 2 GB RAM machine.

Thanks

Actually it is probably due to the PHP memory_limit setting in your php.ini file. See the section regarding "Resource Limits" the PHP core directives page: http://php.net/manual/en/ini.core.php, then try increasing your memory allocation incrementally to resolve this problem.

This is tricky. I'm implementing this in my Nutrition module which is pretty close to "alpha" right now. The problem is that going directly against the USDA database gives the user WAY too many choices. For instance, if you search for "salt" when what you're looking for is "salt, table", you'll actually return 643 results to your user.

The solution I'm testing is creating a USDA "filter". Basically it's a series of checkboxes with all of the major, top level USDA food groups (about 24). I then modify the autocomplete URL for the recipe ingredients textfield based on the food groups checked. The autocomplete always searches the recipe custom ingredients, but it will also optionally search the USDA database underneath the food groups you have checked. So in the example case, you would select the "Spices and Herbs" food group from the USDA DB, and start typing "salt". You'll only get one hit - "salt, table" which is exactly the one you want.

Now the question becomes what to do with the ABBREV table. In theory you could just use the ABBREV table. In fact, that's probably the best way to do it because most everything you're interested in, and everything on the standard US nutrition label is in the ABBREV table, however in order to get the same filtering capabilities I mentioned above, you'll need a number of supporting tables.

Regarding maintenance, what to do about custom ingredients? In my opinion, they should go into the ABBREV table, but I haven't gotten that far yet so I haven't quite sussed out the best way to do it. In the next couple of weeks I hope to have a clean alpha to be able to release. There are a couple of sticky issues I have yet to work out - the biggest one required a modification to core autocomplete.js which I'm really not happy about and will be looking at ways to get around.

I'll keep you posted.

Hi soupy,

I've used your Database source code and the php - thank you.
I found an error in the php, it can be fixed by replacing

$fields = $line;
with

$fields = trim($line);
otherwise a trailing CR will be in the data, preventing your replacement-by-0 method.

One more: `Vol_City` CHAR(10) NULL, should be CHAR(16).

The default database type with my mysql was InnoDB, and with innodb it took 5 minutes to load only 10000 records into the nut_data Database :-( it has 500000 records.
So I changed it to type=Myisam and added DELAYED to the INSERT statement - after that it took only seconds to run completely.

Hope this may help somebody.

Now I'm thinking about some php applications to replace my msacces queries.
If anybody cound provide some php-code for usind the database -to start with- I'd appreciate it.

cheers, Ingo

here comes my slightly altered mysql create code:

gajillion: how soon until you put your nutrition module here on drupal.org? I'm interested in seeing the code. Thanks!

We're looking at using some of the cck3 features and some fuzzy taxonomy, simply because its clear that this module DOES need a complete re-write...

UI / Nutrition seems to be the theme of the summer (fall now), at least on my end.

I'll be in touch.

subscribe

subscribe

Would someone be interested in making this feature into a new Module for Drupal, which adds on to Recipe?

Reasons include:
1) Something like this would be pretty specialized
2) it does seem like there are a few enthusiastic users of the nutritional DB.
3) There are some want-to-use-this folks struggling with setup

That's exactly what it would take to create an add-on module, keep it up to date, and get yet more people using it.
This is something I could not do right now, but someone else (who uses this db) could as an add-on.

To whatever degree I can help, let me know.

I think this would be a great feature, perhaps as a sister module:)

It would go something like this:
1. Load up the NDB into a single table => recipe_ndb (ndb_no, details, weights, ...)
2. Link recipe_ingredient => recipe_ndb by adding ndb_no to recipe_ingredient.
3. Allows users to link ingredients to closest_ndb_no
4. If the recipe has every ingredient linked, either show an ndb tab or a summary box.
5. And make sure to make a note that everything is approx. Ingredients and preparation differ, etc....

Shouldn't be too hard. Hopefully when they release a new ndb the ndb_no don't change.

If performance is too slow though, the nutritional info may need to be copied into the recipe_ingredient
table.

One nice thing is that the ndb doesn't change that much rev to rev (relatively speaking).
If we stayed at rev x for a while, it wouldn't be that bad.

And the 3 year old thread continues!

My wife is desperate for the ingredients module, which means I have to be desperate too.

Subscribe

Subscribing

I think there is plenty of interest, but I don't think any more work has been done (besides thinking about it) since 2008. It would be a nice addition if it could be _optionally_ integrated as a sister module without really messing up the recipe module. Now that we have cleaned up the recipe module quite a bit, this kind of thing becomes feasible once more.

>My main question would be how to perform the math calculations for the different serving sizes in the weight table because all the weights are based on a 100 gram sample of the food. I know the calculation would be nutr_val (from nut_data table)* gm_wgt (from weight table) / 100 to convert from the 100 gram amount to something like cup, tbsp, etc.... What would be the best way to perform this conversion for optimal performance? Should a module be created that does the conversions or can this be done in views?

Ahh... you are touching upon core issues that are not easy to resolve: normalization and conversion of measurements. This in itself would be a major reform of the code that would (as you suggest) make integration like this a little bit easier. It covers a lot of issues I've pondered, but can not get into in this ticket without changing the topic.

There is a units module somewhere, BTW, but it doesn't look up to the task and would need patching if it were to be some sort of back-end helper for Recipe.

My personal take on the issue of USDA integration:
- That this would be an add-on module (depends on Recipe)
- It should simplify and condense the USDA DB (see previous comments about the nutritional "differences" between 50 different types of salt, for example). Most likely this would be some cleanup scripts that target specific USDA releases, and those scripts need to be updated for each newer USDA release. The db cleanup would occur as part of install or upgrade of course, so there's no ongoing calculation penalty
- It should take care of joining and linking to the Recipe DB
- the current status of Recipe's measurement handling (or the lack of it) should be accepted and worked around. This means the "USDA Add-on for Recipe" module would handle all the unit conversions.

This is how I see the problem and solutions anyways.

It sounds like both John and I would love to help some new developer on this, but that someone or someones needs to pick this up, do the heavy lifting and get a working concept release (no matter how limited it is in terms of features) as a companion module.

The trick is for this to happen before the solo developer loses interest or moves on, and it will have a life of it's own. Then the NEXT person looking at this will have a common area to contribute back their improvements. Community stuff.

It looks like some of these issues HAVE been solved by some installations (see earlier comments), but these solutions have not been contributed back either because they are buggy/incomplete, or are too site-specific, or because there's been no place to store these ideas of patches.

I think the issue with the 50 different types of salt example could be resolved through taxonomy. something like this: "herbs & spices" > "spices" > Salt = ingredient nodes that have to do with salt would all be assigned to the salt tag. Hierarchical select makes sense to me to drill down this way. I think we could definitely setup node types with node references connecting the relationships between USDA tables. As far as recipe module goes, the ingredient field would have to link to the ingredient content type as I'm imagining it, select an ingredient through hierarchical select or whatever, and the nutrient values would be calculated on the backend based on serving size specified in the recipe UI. Then, I would guess the best way to display the total nutrient values would be in a block that sums up all the values for each corresponding ingredient entry.

This module http://drupal.org/project/hs_nodereference might also be a way to drill down independent of taxonomy or even in conjunction with by setting up a views node reference that filters by taxonomy term.

More thoughts on ingredient nodes.

While RDF (http://drupal.org/project/rdf) presents some interesting standardized features, it might be too much for our purposes. CCK Nodereference will probably be sufficient. Especially when combined with something like Noderelationships (http://drupal.org/project/noderelationships).

Since the purpose of ingredient nodes will be to shield users from the raw USDA database, I think their creation should indeed be handled within Recipe creation and edits, not as a separate process. A separate process would be too complicated and disconnected from recipes. Thus the on demand creation that node_widget provides would be preferred to http://drupal.org/project/autocreate or http://drupal.org/project/noderefcreate since ingredient data would be immediately available versus making the recipe nutritional data incomplete until the ingredient nodes are completed.

Computed Field will definitely give you an idea how to save data to a CCK field when a node is saved. Looking quickly through the source code, looks like that magic is done on the computed_field_field function (cck hook_field function). Unlike Computed Field, the user won't be entering data to be computed or evaluated. We'll simply be running our own data, which is actually much more simple of a job.

subscribing

ooh I was just looking at those tables. I was thinking the same thing. It would be great to categorize them better. I would really love to have your converted tables. Been looking at them all day to decide how to get them imported.

I'll watch the thread and if I can help let me know.

Phil

Okay looking into this a little further I realize nutritiondata.com also allows users to add an ingredient to their own food list which they can select from that list when adding ingredients to recipes. This is a really good way of doing it to eliminate the need to sort through 50+ entries for egg, salt, etc... How do you guys think about doing this in Drupal? I'm thinking the flag module might be handy for doing this and then allowing the user to select ingredients from a view of flagged ingredients. This would make sense for people to be able to select only the common ingredients they use to create their recipes from. I encourage others to check out this site and offer up some ideas based on how to do what they are doing in drupal.

Hi Gajillion,

Well we hope the change of job, hosting, and drinking habits is a happier and healthier move for you.

Just wanted to say, Love what you are doing and can't wait to see it. I added a recipe, hope you like it.

Phil

Also, what are your ideas to calculate the nutrients once the data is in there? Please share if you don't mind how you're going about this.
Thanks

Great discussion, everyone. Let's keep this going!

There are three approaches I see coming together to integrate the USDA database with Drupal.

1) Completely external database. This is the approach of gajillion. The benefit of this is that you can host the tables in a separate database, perhaps in the cloud. This also allows a custom scheme to sit in between the raw database and the user's ability to add ingredients. Issues this would run into would be coming up with that scheme and letting users add ingredients that are outside the scheme. Also, this would not result in ingredient nodes.

2) Complete import of ingredients into nodes. This is apmsooner's approach. This overcomes a shortcoming of #1 because all ingredients are accessible. However this creates a HUGE Drupal database install. All maintenance tasks on the Drupal database are now monstrously large, and maintaining such a huge Drupal database would be a nightmare. If someone just wanted a simple recipe component of their site, they would now have the headache of managing a huge install just to get recipes.

3) Community driven on demand creation of ingredients. This is the approach I mentioned, and is a hybrid. between #1 and #2. In this approach, the USDA datase base remains external to Drupal and can be hosted anywhere. Some sort of scheme can be used to organize it better, but that isn't essential. It avoids the flexibility shortcoming of #1, though, because users can add any ingredient to their recipe pulling either from ones already created by the community or creating a new one. It avoids the performance and management shortcomings of #2 because it doesn't instantly create a massive Drupal database but rather lets users create a range of ingredients common to them. The concerns this approach would have would still be helping manage the ingredient nodes and a UI that lets users easily create and maintain ingredients.

On another note, one more argument for making ingredients be nodes is that reverse operations can be done. Currently ingredients belong to recipes. But recipes could be found based on ingredient using built in Drupal methods if ingredients were nodes. For example, users could create an "ingredient cupboard" that lists all the ingredients and amounts in their house. Then, with some Views or SQL magic, they could look up all the recipes they have the ability to create based on their current ingredient collection. Ingredient nodes would also assist creation of shopping lists(!), and combined with the ingredient cupboard, smart shopping lists that only show what's left to buy.

here's a few more screen shots of the other node setups.

You /wouldn't/ want to re-calculate nutrition every time you load the recipe. The Drupal caching API should be of help with this sort of thing, so the difference in displaying these bigger data sets should be nil (should... I haven't looked at caching yet for any previous work.).

Hey I'm open to whatever works the best and perhaps external database that just plugs into recipe module is advantageous as long as the ingredients that are in there can be filtered or flagged for use in the recipe UI so we don't have a bunch of ingredients that some of us would never use. Again just for reference, here is the formula for calculating the nutrient values:

N = (V*W)/100
Where:
N = nutrient value per household measure (this is the value we're looking for)
V = nutrient value per 100 g (Nutr_Val in the Nutrient Data file), and W = g weight of portion (Gm_Wgt in the Weight file).

Take this number "N" and multiply it by the "yield" number of servings in recipe module and we have what we're after. I'm no programmer but perhaps you add to the recipe module a function that performs the math above, creates a variable of the value of N and then apply that variable to the yield function I believe already in recipe module and thus outputs result in table. If it can be cached as mentioned in earlier thread then perhaps thats the way to go but my understanding is that the values would have to be stored in the database for each ingredient instance that occurs in each recipe node. Perhaps you're referring to the caching of the actual variable created through the function of getting value of "N" to speed up calculating? Sounds logical anyways... I look forward to this feature as it would be a sweet addon to the already awesome recipe module!

sub

I don't want this thread to dry up and I've done a little more research into what I believe needs to happen for all this to work. There are various modules mentioned in assisting the functionality we need however I don't think there is a complete answer for our objective so I've outlined some objectives and explanation around perhaps how to go about this and leave it up for discussion.
1. We've already identified the need to make ingredients nodes in the recipe module. But the big question is how to we use an autocomplete widget in that field to pull an ingredient from the database in an elegant way, meaning not pulling from 8,000 records. My thoughts around this would be that we could use a widget like node hieararchy select or node reference explorer (http://drupal.org/project/nodereference_explorer) to get the ingredient node populated into the ingredient field.
2. Once we get the ingredient node populated into the ingredient field we need to think about how to pull in units specific to that ingredient. This would ultimately pull from the weights table rather than the current units setup in recipe module. Reason being, weights are calculated specifically to the type of ingredient for appropriate nutrient values therefore we cannot select from all generic unit values because the nut_data table values only pertains to the measurements identified by USDA. So if we have a dropdown field for the weights table that references the food description table, how do we filter by only the weights that reference to the food description selected in the previous field? It seems as though a dependent dropdown field that uses ahah functionality would be most suitable. Example provided here: http://drupal.org/node/524220
3. it seems once we get over these 2 major hurdles, we can figure out how to calculate nutrient values for each ingredient and for overall recipe based on number of servings.
4. As a sidenote, I think it would be advantageous to be able to also create ingredients independent of recipes to create a food journal for dieting. What i mean by this is that the food_desc table from USDA is not exactly just composed of "ingredients". They are food items that people might want to track in a daily journal. Come to think of it, the ability to create a "meal" node that references "ingredients" in the same manner recipe module would so someone could create meal then attach foods accordingly that come from USDA food desc table. So for instance, I want to create a meal called "breakfast" and then attach individual food items like: 1 whole egg, 1 slice bread, 2 apples, etc... and also perhaps have the ability to attach a recipe as a meal item as well... "omelet"?

Okay so the ideas around this project have grown into something probably much more complex than originally anticipated but in doing some brainstorming here I think we can slowly grow the recipe module into something bigger and better if up to the challenge. I can contribute ideas conceptually but my lack in programming limits progressing this along to where it needs to be. I'd like to have some feedback nonetheless of some of the ideas poised here and determine whats doable. thanks!

tzscott, good call on breaking things down. If had to envision the releases, it'd go something like this:

Release 1 - Ingredients as nodes. This includes a working and elegant UI for selecting ingredients within recipes and calculating their nutritional value.

Release 2 - USDA Ingredient DB integration. Whichever of the three options we go with, this will happen at this stage.

Release 3 - Cupboard / Shopping List. The idea I mentioned that lets users enter the ingredients they have and create smart shopping lists that tell them what they still need to buy for recipes, and possible also search for recipes that they already have all the ingredients for.

Release 4 - Meal Plans (apmsooner's idea). This is actually a really exciting idea, and something I was considering as a separate module for one of my clients, Better Weight For Me. Combine this with recommended calories for each user, and you have a smash bang system.

Okay so say we make ingredients as nodes, can we match the food_desc table field definitions as a prerequesite? I'm not talking about changing the UI at this point in selecting ingredients but would just want to ensure that the necessary fields are in the "ingredient node" table so the food_desc table can be appropriately mapped and imported via phpmyadmin. Next thing, perhaps we can ensure that the units used in the USDA weights table match to the available already existing units table. Is there any thought of making units "nodes" as well? If we can add the units we need to match then we're good. If we have ability to add CCK field to units table for gram weight as well then we're moving in the right direction to be able to do calculations at some point. Again, this does nothing to change the current UI of recipe module, just sets up the tables to be more usable to relate via node references and stuff to bring this all together eventually. Once we have ingredients as nodes with the right table structure and units as nodes to match weight table then we at least have the ability to add CCK nodereference of nut_data nodetype on our own to the ingredients table and perhaps we can work through the UI enhancements down the road to make all this work together. Even if we did nothing to the current recipe module but copied the basic assets of recipe to create a separate USDA recipe module where people can contribute to as a side project?

A separate question, why ingredients as nodes? To use node selection tools? or
let people build up expansive ingredient info pages with pictures and stuff?

I still feel most ingredient nodes will just have a title and that's it.

Also right now if you change a recipe ingredient from salt to tomato the module
handles it by removing the salt ingredient and adding the tomato ingredient.
In the future I have 400 recipes that use salt and someone changes the salt ingredient node
to pepper it just modified 400 recipes to be incorrect and I don't have a good way to fix
it if I'm not sure what happened or didn't notice it. For me this is a big deal.

>"A separate question, why ingredients as nodes? To use node selection tools? or
let people build up expansive ingredient info pages with pictures and stuff?"

Yes/Correct.

>"I still feel most ingredient nodes will just have a title and that's it."

I would agree.

If I possibly "read into" this statement as concern about bloating things by making them nodes, I am not worried. The bar for node-making should be, would that be useful to another module? (there's probably a better way to phrase that).

The point of nodes now is to let these things be independently extended or used by other users and developers, without affecting the original and intended use. This discussion is a use case.. if we make ingredients nodes, people can push ahead without much co-ordination from Recipe.

>"Also right now if you change a recipe ingredient from salt to tomato the module
handles it by removing the salt ingredient and adding the tomato ingredient.
In the future I have 400 recipes that use salt and someone changes the salt ingredient node
to pepper it just modified 400 recipes to be incorrect and I don't have a good way to fix
it if I'm not sure what happened or didn't notice it. For me this is a big deal."

Not actually. Ingredients are normalized and abstracted in a mini-node like system. All recipes using an ingredient point to an ingredient id, not a word. Replacing items in one recipe replaces that one pointer. Even if you renamed an ingredient (which you shouldn't to unless it's a synonym), that would just rename the ingredient.id's name.

Or do you see another issue?

@tzoscott, another issue perhaps.
Since we are creating an ingredient editing system where you change the ingredient without seeing the recipes, people might not understand what they are doing. The only reason we avoid this now is because there is no way to edit ingredients now. So once you enter a recipe, it creates ingredients as needed, but never renames/deletes them. The ingredient=editable_node is a new paradigm that we have to be watchful of. I think there are unforeseen consequences to this workflow(can you say newbies:). I think a lot of people come to recipe from the "keep a bunch of recipe emails in my inbox" background, and may not see the problem of changing an existing ingredient.

ex.
recipe1 uses Gebhardt's chili powder
someone while adding recipe2 wants a plain chili powder, they edit the ingredient to
chili powder, then add it to recipe2. While technically accurate, they just screwed up recipe1
which was written by someone that knows that Gebhardt's tastes quite a bit different than
McCormick/Schilling's Chili Powder. The current immutable ingredient system avoids this.

PS: I don't care about the bloat really, I think the node system can handle it just fine. Just the new work flow.

Perhaps putting together graphical representation of the current table layout compared to what we are proposing would be helpful to understand?

John's right about the ingredient node issues.. they need to be planned out. A quick response is, Permissions could let you forbid most users from renaming an ingredient (or at least, from renaming someone else's...)

Maybe they could be prohibited from renaming... so the only way to "replace" ingredients would be with some kind of bulk command, one which forces you to at least view a list of "affected recipes" and who owns them. Not perfect, but users of Node Reference (a neat CCK module) get burned if someone radically changes a node that is referenced. Although, I could see that type of thing happening more on a system like Recipe, where there could be more push and pull on the recipe db.

I'll say that overlapping or unnecessary ingredients are a huge pet peeve - kind of like "freetagging" on websites, the value of the vocabulary diminishes more as a greater number of people tag something (just look at the freetagging of bookmarks on Del.icio.us..).

On the plus side, ingredients as nodes DOES give a standard UI for deleting ingredient nodes, and it's for free.

John and I tend to not be rash at putting things in (and lately, I haven't put anything in at all, it's been all John). So this ingredient node idea needs to "stew" some more, and probably needs proper specifications (especially to support upgrades and permissions). This would be additional reason to consider the approach I recently suggested, where this add-on module could move forward without anything happening yet in Recipe.

Anything happening on this. I hope so this would be a valuable contribution

Hi apmsooner,

Can you tell me how to find the DV (% daily Value). I mean the formula based on the values from the tables.

saud

Hi apmsooner,

Plz see the attached file. I already know what you are telling me. I am asking about some thing else.

*What I feel is that we dont have this data in the USDA tables and we have to take it from some where else.

Plz reply soon.

Thanks.

@apmsooner in #91

The USDA pdf file you reference contains a lot and is quite dense to plow through.

But, on the top of page 13 there's a line that caught my eye. I'm not sure what thoughts it might stir if any so here goes,

"When the protein content of a multi-ingredient food (e.g., beef stew) is calculated using the recipe program of the NDBS the specific nitrogen to protein conversion factors are applied at the ingredient level."

Is there more information on "recipe program of the NDBS" that could be useful in helping to determine the key fields for the recipe module?

Or do we have enough to determine what values to store in the drupal database and what calculations to perform on them.

Thanks,

Izzy

I hope it is okay that i am asking :)
Do you think it will be possible to make this work for sites in other laguages?

Thanks in advance
Mette

I couldn't see that this had already been mentioned so I'd just like to say that www.recipezaar.com uses the USDA database to great effect.
Achieving something similar would be fantastic.

I can't wait to see what comes out of this!

I like this idea, how is this possible?

any news about one module or API?

subscribe

I wonder how accurate some of these sites are??
http://www.food.com/recipe/hot-buttered-rum-233230
Their nutritional states 0 fat. I wonder what kind of butter they're using...

At least this recipe (as of today):
http://www.food.com/recipe/a-1-pot-roast-chuck-steak-177369
Let's you know that the streak is not included in the nutritional.

subscribe

subscribe

I think ingredients as nodes is the way to go, as it gives us maximum flexibility within the Drupal framework. As far as performance goes, I imported the entire database using node_import and I'm not too worried about performance. If it comes down to it, we could have people query from a "common ingredients" list and then give them the option to expand their search to include everything. The common ingredients list could be made up of the x most commonly referenced ingredients where x is a reasonable threshold that will meet the needs of most ingredients.

I have organized a bounty for completion of this project here: http://samgarfield.com/content/usda-recipes

I pledged $250. I imagine if a few people did the same then someone could justify spending the time on finishing this up.

Let me know if anyone would like to firm up that requirements list or change something.

Just to cover my ass: I am not responsible for making sure people pay on their pledges, but I will give all the data to the developer who completes the project.

If more then one developer contributed to completing the project and they can't come to a gentlemanly agreement as to who gets what percentage than the top three contributors will decide. Fair?

Me, too -- $50

One other thing i forgot to point out is that we don't need all the tables available in the USDA resource files. Some of them are clearly associated to the research conducted to get the values of the nutrients. I for one don't care about this. Additionally, many fields aren't necessary in all of the tables. We don't need statistical analysis fields and all that garbage for this project. The key tables we need are:
fd_group
fd_sub_group (added per my opinion)
food_des
nut_data
nutr_def
weight

I personally don't want all the food groups either because my needs are for healthy nutrition so sweets, fast foods, etc.. are useless for me but everyone can choose what they do and don't want to import for their needs. Again, i think this should be treated as a one time import for people and let them modify to their needs from there. I've looked at the USDA updates and the data is not updated often enough nor does it include any substantial changes to justify the need to make our module incorporate the USDA's updates. That part could be done very simply with node import and feeds module.

@gthing,
I think you'll see my concerns with node import module for handling this task when you read through my comments. Things i see as roadblocks with that is the amount of data to import... i think you're going to see some timeouts. Have you actually imported all the tables in via node import or did you just do the abbreviated list? The abbreviated list i'm afraid won't work for us because it doesn't contain all the nutritional values and only includes a few weight measures which means we need to get all the individual tables in and relate to one another. I'm interested in how you've set your structure up for importing to compare to my experiences on this. I just don't think the import part of this project can be done effectively through any other way then manually on the backend or through some intelligent batching process...
Have you received any interest from developers in taking the bounty so far to work on this? Once again i think if we can break it up into smaller parts... some that i would be willing to do then you might be more likely to have someone take on the tougher items as it makes more sense.

apmsooner: Ah I see your concern with node import. I used it to import about 60,000 records yesterday without any problems but didn't realize how big the full USDA database was. We might want to look into the ni_cron module which automates node import into batches that run during cron. It basically just runs until it times out and then next time cron runs it picks up where it left off. This could also provide a solution for keeping the database up to date.

If we want to import data directly into mysql and ignore the node_import route, then I'd recommend something like Big Dump (http://www.ozerov.de/bigdump.php) which I've had much success with in the past. It's much better than phpmyadmin at importing db content.

I've been thinking about this some more and I'm wondering how far we could go using the flexifield module. You could have a node reference field for the ingredient, then a field for the measurements and the number. I believe you can add unlimited flexifields just like any other field. Then you could use computed fields to calculate all of the nutrition facts. Thoughts?

Hey, I am going to keep this one going. I need a module that will import the USDA db and calculate the calories entered by the user. Any suggestions or ideas are welcomed. I am also willing to fund for part of the module.

Bob, i would elect to not use nodes for everything as the field collection module would seemingly make much better sense in my opinion for storage attached to one nodetype for ingredient. I think that would take care of things like node title where it just wouldn't make sense to have a weight node with a required title. As i posted earlier, the one hitch in development i found was sending an ajax request to the database to retrieve specific weights when an ingredient is specified. Thats the big difference from recipe module and why this needed to branch out separately because the USDA has so many different weights and tons of them in their datafiles whereas recipe module has standardized measurements that don't account for things like: 1 package, 1 pat, etc....

If you could build an importer that would import into field collections, i think that would be the best way of doing it.

gthing- Thanks for the reply Sam. Given the age of this thread, I understand. I'm interested in seeing if the interest is genuine, just passing or has passed.

ampsooner - Hi Andy,
Currently: I have done a 1-to-1 straight recreation of the USDA tables, only adding nodeIds. given that the table has keys, the node is merely a drupal feature. All sql lookups can be done with the USDA fields. I have not inched my way up to the ajax calls, so input is appreciated.

Fields: Yes, I could (relatively) easily write an importer into fields. I was thinking about fields but it seemed like way too many fields and more complicated. Since each field is effectively a table in mysql, I'm not sure that fields provides a performance enhancement. Are you suggesting that each ingredient has a field for each nutrient value (i.e. that is 136 fields per ingredient)?

Performance: I have general concerns about performance, given the huge database and much of the data being irrelevant to the general user. Is there any data to support faster performance with fields as opposed to joining tables in an sql call? (joining the tables is quite trivial) Does ajax return faster from fields?

Measurements and weights: I understand your concern. The nutritional data is based on a per 100g basis, which makes sense, and dealing with conversion options is going to be a nightmare. In Recipe, the recipe_ingredient table holds just the ingredient name, and recipe_node_ingredient is specific to the recipe node. Thus, the importer would fill and attach fields to the recipe_ingredient table. The Recipe module is very flexible for measures, but for each recipe measure option we would need to calculate the nutritional value from the 100g equivalent to determine the nutritional value in the recipe. Thus either we limit the options in recipe to those specified in USDA, or we somehow interpolate between the differences.

So, I guess my question to you is, where do you see the path of least resistance? Does using a field collection make the Recipe interfacing easier? If you feel strongly that a specific structure is preferred, please provide explicit instructions and I can code the importer to do it.

-Bob

I would also be willing to throw some cash into the pot. $200, possibly more depending on the ease of use.

I've been watching this thread for awhile, hoping to see forward movement. I just re-joined the ranks of the employed so $50 is all I can afford presently.

I haven't even started to build my site yet, but if you still haven't received the dump you requested by next week, Bob, let me know and I'll get mine put together and get the data over to you. (This is, of course, assuming you are still working on this project.)

-Cristina

Subscribe

Hi.

I wonder if anyone has investigated using the Calorie King API as an alternative? I believe it supports 20,000 free queries per month.

Thanks

Hey Bob,

I know we've chatted via email, but I just wanted to post this up here in the event anyone else is interested.

I'll gladly post some recipes up on the site as we've discussed. I think to really give it something to work with, I'm going to use some of my more complex ones with many ingredients as well as a few simpler ones.

As discussed I won't be ready to start on this until next weekend or so. But one of the things I've been kicking around in my head is the possibility of a slick jquery filter when entering ingredients that can help users find the right thing to enter in order to get accurate data for their recipe. I need to look into how this can best be done, but it should certainly be in the realm of possibility!

And now back to work. *bleh*

Cristina