# Related table columns

**URL:** <https://www.ruby-forum.com/t/related-table-columns/105083>\
**Category:** Rails\
**Created:** [May 25, 2007, 5:24pm UTC](https://www.ruby-forum.com/t/related-table-columns/105083 "2007-05-25T17:24:36Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![elmy-liddiard](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/c67d28/32.png) [@elmy-liddiard](https://www.ruby-forum.com/u/elmy-liddiard)\
**Post date:** [May 25, 2007, 5:24pm UTC](https://www.ruby-forum.com/t/related-table-columns/105083/1 "2007-05-25T17:24:36Z")

</div>

Help for newbie please!

I have two tables: controls and programs.

controls has\_many :programs  
Programs belongs\_to :control

Controls has id, controlname  
Programs has, apart from it’s own id, two fields ctl1\_id and ctl2\_id.

These represent two controls that the program relates to.

Foreign keys are set on program table columns ctl1\_id and ctl2\_id back  
to the id field on the control table.

So when I list the program table using

\<% @programs.each do |array| %\>

 \<%= array.ctl1\_id%\> \<%= array.ctl2\_id %\> \<% end %\>

…everything is fine.

But when I list the programs row I want to actually show the controlname  
from controls table rather than the ctl1\_id and ctl2\_id values from the  
programs row. For this I use the following code:-

\<% @programs.each do |array| %\>

 \<%= array.control.controlname %\> \<%= array.control.controlname %\> \<% end %\>

I expect the link via the foreign keys and the models relationships to  
give me what I need but I am obviously missing something because I get  
this:-.

You have a nil object when you didn’t expect it!  
The error occured while evaluating nil.control

Extracted source (around line #56):

53:   
54:

  
55:  
56: \<%= array.control.controlname %\>

I have checked out the cookbook application code and cant see where I am  
misunderstanding.

Any help gratefully received.

Thanks

I can see that

---

<div class="post-metadata">

**Author:** ![elmy-liddiard](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/c67d28/32.png) [@elmy-liddiard](https://www.ruby-forum.com/u/elmy-liddiard)\
**Post date:** [May 25, 2007, 11:30pm UTC](https://www.ruby-forum.com/t/related-table-columns/105083/2 "2007-05-25T23:30:50Z")

</div>

On May 25, 2007, at 11:24 AM, Martyn Elmy-liddiard wrote:

> These represent two controls that the program relates to.
> 
> Foreign keys are set on program table columns ctl1\_id and ctl2\_id back  
> to the id field on the control table.

Let’s make this more explicit:

class Control \< ActiveRecord::Base  
has\_many :programs  
end

class Program \< ActiveRecord::Base  
belongs\_to :ctl1, :class\_name =\> ‘Control’, :foreign\_key =\> ‘ctl1\_id’  
belongs\_to :ctl2, :class\_name =\> ‘Control’, :foreign\_key =\> ‘ctl2\_id’  
end

> So when I list the program table using
> 
> \<% @programs.each do |array| %\>
> 
> \<%= array.ctl1\_id%\> \<%= array.ctl2\_id %\> \<% end %\>
> 
> …everything is fine.

Any of these:  
\<% @programs.each do |program| %\>  
\<% @programs.each do |p| %\>  
\<% for program in @programs %\>

is probably better than “array” for the block variable. (But this is  
a personal choice as long as you avoid keywords like “class” or  
“begin”.)

> Thanks
> 
> I can see that
> 
> –

\<% @programs.each do |program| %\>

 \<%= program.ctl1.controlname %\> \<%= program.ctl2.controlname %\> \<% end %\>

But personally, I’d rename “controlname” to just “name” since it’s a  
column in the Control model anyway.

From this little example, it seems like you have a program with many  
(ok, 2) controls, but does each control really associate to more than  
one program? I’m just wondering whether there’s another join model  
linking programs and controls in a many-to-many relationship.

Anyway, I suspect that you are under the mistaken assumption that the  
existence of foreign keys in the database have some influence on the  
behavior of your ActiveRecord models. Other than potentially causing  
inserts or deletes to fail if performed in the wrong order, your  
Models won’t care.

-Rob

Rob B. [http://agileconsultingllc.com](http://agileconsultingllc.com)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![elmy-liddiard](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/c67d28/32.png) [@elmy-liddiard](https://www.ruby-forum.com/u/elmy-liddiard)\
**Post date:** [May 29, 2007, 1:57pm UTC](https://www.ruby-forum.com/t/related-table-columns/105083/3 "2007-05-29T13:57:34Z")

</div>

Rob,

Thanks very much for bothering to help a newbie.

I was indeed under the mistaken assumption that the existence of foreign  
keys in the database influence the behavior of your ActiveRecord models.  
I now see it’s the model.

I have taken your suggestion and it now works! - many thanks but I do  
have a follow-up, if I may:-

To further explain my structure:

The control table actually has say 10 rows (i.e. there are 10 potential  
controls.)  
The program table has columns ctl1\_id, ctl2\_id…through ctl10\_id.)

So a program can have upto 10 controls that are relevant to it.

Taking your help I now have:-  
class Program \< ActiveRecord::Base  
belongs\_to :ctl1, :class\_name =\> ‘Control’, :foreign\_key =\> ‘ctl1’  
belongs\_to :ctl2, :class\_name =\> ‘Control’, :foreign\_key =\> ‘ctl2’  
belongs\_to :ctl3, :class\_name =\> ‘Control’, :foreign\_key =\> ‘ctl3’  
belongs\_to :ctl4, :class\_name =\> ‘Control’, :foreign\_key =\> ‘ctl4’  
belongs\_to :ctl5, :class\_name =\> ‘Control’, :foreign\_key =\> ‘ctl5’  
belongs\_to :ctl6, :class\_name =\> ‘Control’, :foreign\_key =\> ‘ctl6’  
belongs\_to :ctl7, :class\_name =\> ‘Control’, :foreign\_key =\> ‘ctl7’  
belongs\_to :ctl8, :class\_name =\> ‘Control’, :foreign\_key =\> ‘ctl8’  
belongs\_to :ctl9, :class\_name =\> ‘Control’, :foreign\_key =\> ‘ctl9’  
belongs\_to :ctl10, :class\_name =\> ‘Control’, :foreign\_key =\> ‘ctl10’  
end

and…

\<% @programs.each do |program| %\>

\<%end\>

…this is great but I now have a related issue in that if I list the  
controls related to a program with all 10 nominated it fine but if I  
list the controls related to a program with say 7 controls nominated  
then the code falls over on the first ‘empty’ reference.

i.e. \<%= program.ctl8.control %\> returns an error:-

You have a nil object when you didn’t expect it!  
The error occured while evaluating nil.control

Is there a way to code round this ‘empty’ reference?

Much appreciated!

Martyn

| \<%= program.ctl1.control %\> | \<%= program.ctl2.control %\> | \<%= program.ctl3.control %\> | \<%= program.ctl4.control %\> | \<%= program.ctl5.control %\> | \<%= program.ctl6.control %\> | \<%= program.ctl7.control %\> | \<%= program.ctl8.control %\> | \<%= program.ctl9.control %\> | \<%= program.ctl10.control %\> |

---

<div class="post-metadata">

**Author:** ![elmy-liddiard](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/c67d28/32.png) [@elmy-liddiard](https://www.ruby-forum.com/u/elmy-liddiard)\
**Post date:** [May 30, 2007, 5:00pm UTC](https://www.ruby-forum.com/t/related-table-columns/105083/4 "2007-05-30T17:00:24Z")

</div>

Thorsten

Thanks for your detailed coding help.

Unfortunately I am still failing to get this to work - probably missing  
a key point…soooo frustrating!

I have, as advised, create the controls\_programs table, changed the  
models as advised, changed the controller code and added the view code.

> #controller:  
> @programs = Program.find :all, :include =\> “controls”
> 
> #view  
> \<% @programs.each do |program| %\>  
> \<% program.controls.each do |control| %\>
> 
> \<%= control.controlname %\> \<% end %\> \<% end %\>

I get the program rows returned as before but still get no control rows  
returned. I know helping newbies can be hard-work but if you have any  
ideas do please let me know. In the meantime I’ll keep plugging away…I  
might just get it!

Thanks

Martyn

---

<div class="post-metadata">

**Author:** ![elmy-liddiard](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/c67d28/32.png) [@elmy-liddiard](https://www.ruby-forum.com/u/elmy-liddiard)\
**Post date:** [May 29, 2007, 2:13pm UTC](https://www.ruby-forum.com/t/related-table-columns/105083/5 "2007-05-29T14:13:47Z")

</div>

this is clearly an example of a many-to-many relationship, an not a  
one-to-many relationship as you tried to do with has\_many and  
belongs\_to  
A Control can have one or more Programs, and a Program can belong to  
one or more controls.

use has\_many :through or has\_and\_belong\_to\_many  
#Tables:  
controls:  
id, controlname

programs  
id, name

controls\_programs  
control\_id, program\_id

the last table maps the many-to-many relationship between Controls and  
Programs

#Models:  
Control \< ActiveRecord::Base  
has\_and\_belongs\_to\_many :programs  
end

Control \< ActiveRecord::Base  
has\_and\_belongs\_to\_many :controls  
end

#controller:  
@programs = Program.find :all, :include =\> “controls”

#view  
\<% @programs.each do |program| %\>  
\<% program.controls.each do |control| %\>

 \<%= control.controlname %\> \<% end %\> \<% end %\>

On 29 Mai, 13:57, Martyn Elmy-liddiard \<rails-mailing-l…@andreas-

---

<div class="post-metadata">

**Author:** ![elmy-liddiard](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/c67d28/32.png) [@elmy-liddiard](https://www.ruby-forum.com/u/elmy-liddiard)\
**Post date:** [May 31, 2007, 11:28am UTC](https://www.ruby-forum.com/t/related-table-columns/105083/6 "2007-05-31T11:28:46Z")

</div>

Well, just changing the code doesn’t do the trick … do you have  
associated the programs with the controls?

tables:  
#programs#controls\_programs#controls  
id 1 program\_id 1 id 2  
name TestProg control\_id 2 controlname  
TestControl

now controls\_programs has an entry that reraltes Program 1 with  
Control 2

You achive that like this in your controller:  
class Program \< ActionController::Base  
def add\_control  
@program = Program.find(1)  
@control = Control.find(1)  
@program.controls \<\< @control  
end

The your programs and controls are really associated and the view code  
should work  
You can read more about that here:

> **[ActiveRecord::Associations::ClassMethods](https://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html)**
>
> Associations are a set of macro-like class methods for tying objects together through foreign keys.

[http://wiki.rubyonrails.org/rails/pages/has\_and\_belongs\_to\_many](http://wiki.rubyonrails.org/rails/pages/has_and_belongs_to_many)

also have a look at “has\_many :through =\>” (google will help) which is  
a “better” has\_and\_belongs\_to\_many …  
Hope that helps

On 30 Mai, 17:00, Martyn Elmy-liddiard \<rails-mailing-l…@andreas-

---

<div class="post-metadata">

**Author:** ![elmy-liddiard](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/c67d28/32.png) [@elmy-liddiard](https://www.ruby-forum.com/u/elmy-liddiard)\
**Post date:** [June 1, 2007, 12:52pm UTC](https://www.ruby-forum.com/t/related-table-columns/105083/7 "2007-06-01T12:52:44Z")

</div>

I already gave you an example in my previous post about how to add a  
relationship between a program and a control:  
#controller  
def add\_control\_to\_program  
@program = Program.find(params[:program\_id])  
@control = Control.find(params[:control\_id])  
@program.controls \<\< @control  
@program.save  
end

Rails then adds the corrept mapping line into the controls\_programs  
table  
You can also do it vice versa, like:

@control.porgrams \<\< @porgram

the result is the same because of the nature of the many-to-many  
relationship

for more info read the API Docs about associations please, i already  
gave you the links in my previous post.

On 31 Mai, 17:46, Martyn Elmy-liddiard \<rails-mailing-l…@andreas-

---

<div class="post-metadata">

**Author:** ![elmy-liddiard](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/c67d28/32.png) [@elmy-liddiard](https://www.ruby-forum.com/u/elmy-liddiard)\
**Post date:** [May 31, 2007, 5:46pm UTC](https://www.ruby-forum.com/t/related-table-columns/105083/8 "2007-05-31T17:46:20Z")

</div>

Thorsten

Thnaks very much for bothering to help once again.

Hurrah! I now get it and it’s all working fine.

V.Much appreciated.

One final question please; I had to manually load the data into the  
controls\_programs table; Should the rails framework do this  
automatically based on adding a record to the programs table and  
specifying the controls it relates to?

Martyn