# Problem with a join table having additional data

**URL:** <https://www.ruby-forum.com/t/problem-with-a-join-table-having-additional-data/162963>\
**Category:** Rails\
**Created:** [March 16, 2009, 10:10am UTC](https://www.ruby-forum.com/t/problem-with-a-join-table-having-additional-data/162963 "2009-03-16T10:10:49Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![ball](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/858c86/32.png) [@ball](https://www.ruby-forum.com/u/ball)\
**Post date:** [March 16, 2009, 10:10am UTC](https://www.ruby-forum.com/t/problem-with-a-join-table-having-additional-data/162963/1 "2009-03-16T10:10:49Z")

</div>

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!