Hi, I am a very new to both Rails and SQL, and am trying to learn both by using an already established nutrition database. I am stuck on a join table that is a couple layers deep, and I was hoping that someone could help me out. First, the purpose of the database is to map a food type (ground beef) to a list of nutrients (protein, fat, etc..). Because I am working with a non-rails db, I have to set primary/ foreign keys myself. The database has the following tables: food_description NDB_No (primary key) NameOfFood # i.e. butter, chicken, ground beef, etc... And it maps the food to a list of nutrients (i.e. protein, fat, carbs) through a nutrient_datas table: nutrient_datas NDB_No (primary key) # maps to the Food description Nutr_No # maps to the name of the nutrient Nutr_Val # how much of that nutrient this food has (24g of (Nutr_No) Now, each of the nutrient_datas entries can have one or more sources. Just to make it real, let’s say: Food Description: Ground Beef Nutrient Datas: 12 g of Protein => Source 1: Cattleman’s Association => Source 2: Beef, it's what's for dinner 10 g of Fat => Source 1: PETA => Source 2: Cardiologists of America So, the Sources are handled by two more tables: data_sources DataSrc_ID (primary_key) Title Author and a join table: data_sources_nutrient_datas NDB_No # maps to the food description (i.e. beef) Nutr_No # maps to the Nutrient (i.e. Protein) DataSrc_ID # maps to the Source (i.e. Cattlemans Association) Here is my attempt to define three (kind of working) ActiveRecords class NutrientData < ActiveRecord::Base set_primary_key "NDB_No" belongs_to :food_description, :foreign_key => "NDB_No" belongs_to :nutrient_definition, :foreign_key => "Nutr_No" belongs_to :source_code, :foreign_key => "Src_Cd" has_many :data_sources_nutrient_datas, :foreign_key => "NDB_No" has_many :data_sources, :through => :data_sources_nutrient_datas, :foreign_key => "NDB_No" end class DataSource < ActiveRecord::Base set_primary_key "DataSrc_ID" has_many :data_sources_nutrient_datas, :foreign_key => "NDB_No" has_many :nutrient_datas, :through => :data_sources_nutrient_datas, :foreign_key => "NDB_No" end class DataSourcesNutrientData < ActiveRecord::Base belongs_to :nutrient_data, :foreign_key => "NDB_No" belongs_to :data_source, :foreign_key => "DataSrc_ID" end I can say: >> FoodDescription.find("03104").nutrient_datas.data_sources and be returned an array of sources for ALL of the nutrients (not just the 10th one). So, my questions are: 1. Are my ActiveRecords OK (I think I am missing something) 2. How do I get FoodDescription.find("03104").nutrient_datas .data_sources to return only the records for nutrient (array) 10? (i.e. I need to select the data_source not just on the NDB_No, but also on the Nutr_No). Only the join table seems to have the Nutr_No info that I need, not the data_sources. 3. It seems best, of course, to query all of the data_sources at once, and then display only what is needed in the view. How can I do just that (a different (better) approach than above)? Thanks in advance for your help!
on 2009-03-16 11:10