Problem with a join table having additional data


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:

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:

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:

DataSrc_ID (primary_key)

and a join table:

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”

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”

class DataSourcesNutrientData < ActiveRecord::Base
belongs_to :nutrient_data, :foreign_key => “NDB_No”
belongs_to :data_source, :foreign_key => “DataSrc_ID”

I can say:

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!

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs