Rails - Mar 2009 - problem with a join table having additional data

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[10].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
[10].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!

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---