Related table columns


#1

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


#2

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
removed_email_address@domain.invalid


#3

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 %>

#4

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


#5

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-


#6

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:
http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html
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-


#7

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-


#8

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