Unknown column error

Hello all,

I am getting an unknown column error (mentioned below). What I want to do is, extract the language a person speaks. This column is present in the mhp_register table, and the profile information is present in the mhp_profile table. I have the following models:

class MhpFriends < ActiveRecord::Base
belongs_to :MhpProfile
def self.table_name() “mhp_friends” end
end

class MhpProfile < ActiveRecord::Base
has_many :MhpFriends
has_one :MhpRegister

def self.table_name() “mhp_profile” end
end

class MhpRegister < ActiveRecord::Base
belongs_to :MhpProfile
def self.table_name() “mhp_register” end
end

The error I am getting is

Mysql::Error: #42S22Unknown column 'mhp_register.mhp_profile_id' in 'where clause': SELECT * FROM `mhp_register` WHERE (mhp_register.mhp_profile_id = 6) LIMIT 1

My index.rhtml file is as follows:
<% @result.each do |res| %>

First name is <%=res.first_name%> Last name is <%= res.last_name%> From state <%=res.from_state%> From city <%=res.from_city%>
Speaks Language **<%=res.MhpRegister.language_speak %>**

<%end%>

The bold area above gives the above mentioned error. Similarly, I also want to access all the friends of a profile, so the solution of this problem will also help me go about accessing the friends using the same method.

My controller is

class ProfileController < ApplicationController
def index
@result = MhpProfile.find(:all)
end
end

Any insight into this error will be appreciated. Please reply at harisgulzar@gmail.com

There is no field mhp_profile_id in the mhp_register table, because you didn’t create one.

But the mhp_register table has a column named “id” and thats what I want to use instead of mhp_profile_id. How can I tell rails to look for the id column and not for the mhp_profile_id column?

That ID is the id of that mhp_register, not of the mhp_profile.

Also, why must it be called MhpRegister? Why not just Register?

But the relation between mhp_profile and mhp_register is One-to-One, hence the mhp_register has only one column named id, and no foriegn key from the mhp_profile table

If it’s a one-to-one it still needs to have an mhp_profile_id column, like I stated before.

The ID column is specifically for that mhp_register record. You must create an mhp_profile_id field in mhp_register.

I’ll also ask this again: Why the MHP prefix?

But that means, if mhp_register table is related to several tables with a One-to-One relationship, it has to have a mhp_tablename_id column for each relation ??

About the mhp prefix, Thats how I got the DB schema. I mean I have to build my RoR application on an existing schema, and the existing schema has mhp prefix with all tables. Thats how my client requires it.

Please also tell me, If I have three tables named mhp_profile, mhp_register, and mhp_friends, and I have three corresponding classes named MhpProfile, MhpRegister and MhpFriends. Each profile has_mane friends, and each profile has_one register. and both friends and register classes belong to profile. Now does rails strictly want EACH profile to have EXACTLY ONE register, and EACH profile to have ATLEAST ONE friend ?? I mean is it possible for a profile not to have any register or not to have any friends ?? Does rails stricly enforce the has_one and has_many relationship?

I hope you get my question

Re the mhp prefix:

You don’t have to name your models MhpRegister, you can just call set_table_name :mhp_register in them. I’m pretty sure there’s a way to do this automatically, like in a config file somewhere, but I don’t recall it.

Re the has_one relationship:

I think you’re misunderstanding how it’s supposed to work. A profile has_one register. How does that register know which profile it belongs to? Rails will do it by default to the mhp_profile_id field in mhp_register. Set :foreign_key => “id” on the has_one association and it should work.

Yes it is possible for a profile not to have a register and not to have any friends, Rails does not enforce it.

Re the mhp prefix:

Yeah I think we can do it by

def self.table_name() “mhp_register” end

Re the relationships:

Do I set the :foreign_key on belongs_to association or on the has_one association. I think I tried it with the belongs_to association but it did not work. I will try it with has_one association as well. Thanx loads for all your help.

Well, I might have been asking some very very basic questions, but I am actually a starter on RoR. so pardon me with that.

Basic questions are fine. That’s what we’re here for.

I was a noob once, and I remember some of the questions I asked and remember how I felt when they were answered, so I try to give that feeling to others.

Could you also please tell me another thing.

If my class name is Profile (and my table name is mhp_profile), Friend (table name is mhp_friends), and Register (table name is mhp_register) as you suggested earlier. And my relations are as I mentioned in my previous email (profile has many friends and has one register)

In the profile controller I do @result = Profile.find(:all)

In my view I do

<% @result.each do |res| %>

now if I want to access the friends or the register info using this res variable, how will I do it ??

Actually here is what Im trying to do…My index.rhtml is as follows
<% @result.each do |res| %>
<%= res.first_name %>


<%= res.register.language_speak
%>





<% end %>
My models are as follows
class Profile < ActiveRecord::Base
has_many :friends, :foreign_key => “user_id”
has_one :register, :foreign_key => “id”

def self.table_name() “mhp_profile” end
end
class Friend < ActiveRecord::Base
belongs_to :profile

def self.table_name() “mhp_friends” end
end
class Register < ActiveRecord::Base
belongs_to :profile

def self.table_name() “mhp_register” end
end
But Im getting the following error.
Showing profile/index.rhtml where line #4 raised:

You have a nil object when you didn't expect it!
The error occurred while evaluating nil.language_speak

Any idea what am I doing wrong…I have only one controller for profile as follows

class ProfileController < ApplicationController

def index

@result = Profile.find(:all)

end

end

Re the mhp prefix:

You don't have to name your models MhpRegister, you can just call
set_table_name :mhp_register in them. I'm pretty sure there's a way
to do this automatically, like in a config file somewhere, but I
don't recall it.

I believe you're thinking of ActiveRecord::Base.table_name_prefix
Set it to 'mhp_' to save some keystrokes.
Fred

config.active_record.table_name_prefix = “mhp” in your environment.rb should do the trick.