Noob ActiveRecord Join Question


#1

I’m new to rails and ruby, and after trying to get this to work
correctly for hours, I thought I’d give this forum an try.

I’ve setup a trivial project just to mess around with Rails database
naming conventions and associations.

I’ve got a table called Pets, and a table called Types.
Pets contains a list of pets, Parky the cat, Daisy the dog, etc, And
Types contains a list of different pet types, dog - cat - fish - bird -
etc.

Pets table
CREATE TABLE pets (
id int(11) NOT NULL auto_increment,
name varchar(20) default NULL,
pet_id int(11) default NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Types table
CREATE TABLE types (
id int(11) NOT NULL auto_increment,
pettype varchar(11) default NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

What I want to do is to associate the pet_id from Pets with the id from
types, and can do that in SQL like this:

select pets.*, types.pettype
from pets, types
where pets.id = 1
and pets.pet_id = types.id

In Rails, I have two models, 1 for pets, 1 for types

class Type < ActiveRecord::Base
belongs_to :pet
end

class Pet < ActiveRecord::Base
has_one :type, :foreign_key => “id”
end

What I end up with is this SQL statement out of Rails:

select pets.*, types.pettype
from pets, types
where pets.id = 1
and pets.pet_id = 1

Where 1 is the primary key (id) of the pets row.

Just wondering what I’m doing wrong.

Thanks!


#2

I just made the same mistake a few hours ago. I ended up flipping the
belongs_to: and has_one: in my Models and it works. So:

class Type < ActiveRecord::Base
has_one :pet
end

class Pet < ActiveRecord::Base
belongs_to :type
end

You don’t need the foreign key either, rails handles that
automagically. I have no idea if this is right, just that it does
what I want it to do.

On Jan 9, 2006, at 7:28 PM, David Mr. wrote:

etc.
CREATE TABLE types (
select pets.*, types.pettype
class Pet < ActiveRecord::Base
Where 1 is the primary key (id) of the pets row.
Posted via http://www.ruby-forum.com/.


Rails mailing list
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails

  • Bill

#3

David Mr. wrote:

class Type < ActiveRecord::Base
belongs_to :pet
end

As a rule, I would stay away from using “type” as a model name or a
column name. ‘type’ is a reserved ruby keyword, and “type” columns are
automagic for single table inheritance.

You are likely to run into all sorts of wierdness if you use them
outside of those contexts.

_Kevin


#4

Tried that and it still didn’t work for me.

This works, but not as pretty as just saying has_one and belongs_to :frowning:

def self.find_all_with_pettypes(j)
find :all,
{
:conditions => “id = ‘#{j}’”,
:joins => ‘LEFT JOIN types ON pets.petid = types.petid’
}
end

Guess as I play with Rails more I might actually learn what I’m doing.
Thanks for they help!

Bill P. wrote:

I just made the same mistake a few hours ago. I ended up flipping the
belongs_to: and has_one: in my Models and it works. So:

class Type < ActiveRecord::Base
has_one :pet
end

class Pet < ActiveRecord::Base
belongs_to :type
end


#5

I don’t think this is correct. You should have the belongs_to call in
the class that contains the foreign key. So it does not make sense to
say that Type belongs_to :pet. You had it right the first time.

I agree with Kevin, however, I would stay away from “type” as a model
name or attribute.

Lance


#6

So, I tried this once, and it didn’t work. I tried it again, and it did
work.

Now it’s working!!!

Thanks, just needed to wrap my head around it for it to make sense
(although it still seams backwards to me :slight_smile:

Thanks again!

Bill P. wrote:

I just made the same mistake a few hours ago. I ended up flipping the
belongs_to: and has_one: in my Models and it works. So:

class Type < ActiveRecord::Base
has_one :pet
end

class Pet < ActiveRecord::Base
belongs_to :type
end