One to many question

mkhumri · April 22, 2006, 6:13pm

A category has parent categories.
A product is in many categories and a category has many products.
Products and category both have images in the same image table. ie. a
product and / or category could have multiple images.<=== my question is
related to this

So among other things I presume I have to do the following:

class Category < ActiveRecord:Base
#…
has_and_belongs_to_many products
acts_as_tree :order => “name”
#…
end

class Product < ActiveRecord:Base
#…
has_and_belongs_to_many categories
#…
end

class Image < ActiveRecord:Base
#…
# ??
#…
end

Lets say the image table sql looks like this:

create table images (
image_id int not null,
entity_id int not null,
entity_type varchar(20) not null,
image_path varchar(255) not null,
image_type varchar(20) not null,
primary key(image_id)
);

For example if i were to store the “BIG”, “SMALL” images for a category
in the above table, I would do:
insert into images values(1, 1, “CATEGORY”, “/big/smile1.jpg”, “BIG”);
insert into images values(1, 1, “CATEGORY”, “/small/smile2.jpg”,
“SMALL”);

Also, for example if I were to store the “SMALL”, “MEDIUM” images for a
product in the above same table, I would do:
insert into images values(1, 1, “PRODUCT”, “/big/hifi1.jpg”, “SMALL”);
insert into images values(1, 1, “PRODUCT”, “/small/hifi2.jpg”,
“MEDIUM”);

My question is how do I tell my app using ActiveRecord to map the one to
many association between category and images AND between product and
images in the above scenario where the product and category images map
to the same table? Example code would be a great help.

Thanks.

mkhumri · April 23, 2006, 4:13am

My question is how do I tell my app using ActiveRecord to map the one to
many association between category and images AND between product and
images in the above scenario where the product and category images map
to the same table? Example code would be a great help.

If I were in your shoes, I would just put two foreign keys in the image
table, one for categories and one for products. Then, images would have:

belongs_to :product
belongs_to :category

And then, products and categories would both have:

has_many :images

Sure, you’ll be wasting a field in every image record. Big deal. You can
have this running in like five minutes, and it’ll work very easily.

mkhumri · April 23, 2006, 4:18am

You could also use a polymorphic association.

On Sunday, April 23, 2006, at 4:13 AM, Bryan D. wrote:

belongs_to :category

Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

_Kevin

mkhumri · April 23, 2006, 4:31am

I might be misunderstanding your problem here, but thought I’d offer my
advice just in case it’s applicable… The other day I was trying to do
something similar… I had a User model, and wanted the user to be able
to
select a list of genres of music that they listen to. I also wanted to
allow them to pick genres that they as a band play (if they were in fact
in
a band). So I’d need two join tables, one that maps users to
personal_genres and one that maps users to band_genres. The problem is,
I
wanted to have only a single genre lookup table, that both band_genres
and
personal_genres relate to. The way I solved it was this:

in User.rb:

has_and_belongs_to_many :personal_genres, :class_name => “Genre”,
:join_table => “personal_genres_scouts”
has_and_belongs_to_many :band_genres, :class_name => ‘Genre’,
:join_table
=> “band_genres_scouts”

and of course I had the following tables:

mysql> select * from genres limit 5;
±—±-----------±-------------------+
| id | short_name | genre_name |
±—±-----------±-------------------+
| 1 | AD | Adult/Contemporary |
| 2 | BL | Blues |
| 3 | CO | Country |
| 4 | DA | Dance/Electronica |
| 5 | EMO | EMO |
±—±-----------±-------------------+

mysql> select * from band_genres_users limit 5;
±---------±---------+
| user_id | genre_id |
±---------±---------+
| 709 | 10 |
| 709 | 13 |
| 709 | 14 |
| 716 | 8 |
| 716 | 13 |
±---------±---------+

mysql> select * from personal_genres_users limit 5;
±---------±---------+
| user_id | genre_id |
±---------±---------+
| 708 | 2 |
| 708 | 6 |
| 708 | 15 |
| 708 | 8 |
| 708 | 12 |
±---------±---------+

so perhaps you could do something similar, by having both products and
images mapped to a single table, by specifying the join table and the
class
name in your model.

Mike

On 23 Apr 2006 02:15:43 -0000, Kevin O. <

mkhumri · April 23, 2006, 6:39am

Category
has_many :images, :as=>:attachable

Product
has_many :images, :as=>:attachable

Image
belongs_to :attachable, :polymorphic=>true

The image table should have a
attachable_type varchar(255)
attachable_id integer

you can then call

category.images
product.images

I don’t know if polymorphic associations work with has_one, if not, just
make sure you set it up with an appropriate limit to get just the most
recent one.

On Sunday, April 23, 2006, at 6:22 AM, Mufaddal K. wrote:

In my case a product could have many images and so could a category

I might be misunderstanding your problem here, but thought I’d offer my
and

| 5 | EMO | EMO |
| 716 | 13 |
| 708 | 12 |
On 23 Apr 2006 02:15:43 -0000, Kevin O. <

–
Posted via http://www.ruby-forum.com/.

Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

_Kevin

mkhumri · April 23, 2006, 7:01am

Kevin,

I will give this approach a try. Thank you for the input on this.

Regards.

Kevin O. wrote:

Category
has_many :images, :as=>:attachable

Product
has_many :images, :as=>:attachable

Image
belongs_to :attachable, :polymorphic=>true

The image table should have a
attachable_type varchar(255)
attachable_id integer

you can then call

category.images
product.images

I don’t know if polymorphic associations work with has_one, if not, just
make sure you set it up with an appropriate limit to get just the most
recent one.

_Kevin

mkhumri · April 23, 2006, 6:22am

Hi,

Mike, the solution you presented works and the approach you present has
definitely crossed my mind. In this approach one can get the
functionality of a one to many association by actually turning it into a
many to one and a one to many association using the join table. I come
from the hibernate world of doing things where you would be able to do
this one to many association without turning it into a many to one and
one to many association.

In my case a product could have many images and so could a category
could have many images. An image on the other hand belongs ONLY TO ONE
category or ONLY TO ONE product. If I follow your approach, the one down
side to it is that I will loose this semantic at the database level i.e
the image belongs to only one product or a category. Though at the
business logic level I know that I am just using the many to many
mapping as a means of doing my one to many association.

Thank you.

Mike G. wrote:

I might be misunderstanding your problem here, but thought I’d offer my
advice just in case it’s applicable… The other day I was trying to do
something similar… I had a User model, and wanted the user to be able
to
select a list of genres of music that they listen to. I also wanted to
allow them to pick genres that they as a band play (if they were in fact
in
a band). So I’d need two join tables, one that maps users to
personal_genres and one that maps users to band_genres. The problem is,
I
wanted to have only a single genre lookup table, that both band_genres
and
personal_genres relate to. The way I solved it was this:

in User.rb:

has_and_belongs_to_many :personal_genres, :class_name => “Genre”,
:join_table => “personal_genres_scouts”
has_and_belongs_to_many :band_genres, :class_name => ‘Genre’,
:join_table
=> “band_genres_scouts”

and of course I had the following tables:

mysql> select * from genres limit 5;
±—±-----------±-------------------+
| id | short_name | genre_name |
±—±-----------±-------------------+
| 1 | AD | Adult/Contemporary |
| 2 | BL | Blues |
| 3 | CO | Country |
| 4 | DA | Dance/Electronica |
| 5 | EMO | EMO |
±—±-----------±-------------------+

mysql> select * from band_genres_users limit 5;
±---------±---------+
| user_id | genre_id |
±---------±---------+
| 709 | 10 |
| 709 | 13 |
| 709 | 14 |
| 716 | 8 |
| 716 | 13 |
±---------±---------+

mysql> select * from personal_genres_users limit 5;
±---------±---------+
| user_id | genre_id |
±---------±---------+
| 708 | 2 |
| 708 | 6 |
| 708 | 15 |
| 708 | 8 |
| 708 | 12 |
±---------±---------+

so perhaps you could do something similar, by having both products and
images mapped to a single table, by specifying the join table and the
class
name in your model.

Mike

On 23 Apr 2006 02:15:43 -0000, Kevin O. <

mkhumri · April 23, 2006, 9:24pm

I finally got sometime this morning to write an example class to
exercise the polymorphic feature. I get the following exception:

/usr/lib/ruby/gems/1.8/gems/activesupport-1.2.5/lib/active_support/core_ext/hash/keys.rb:48:in
assert_valid_keys': Unknown key(s): as (ArgumentError) from /usr/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/associations.rb:343:inhas_many_without_reflection’
from (eval):5:in `has_many’
from db.rb:8

Am I using the wrong version of activerecord or something?
These are the versions I am using:
ruby 1.8.3 (2005-06-23) [i486-linux]
activerecord (1.13.2)

##################################
Here is the example class I wrote:
##################################

require “rubygems”
require_gem “activerecord”

ActiveRecord::Base.establish_connection(:adapter => “mysql”, :host =>
“localhost”, :database => “myTestDb”)

class Stock < ActiveRecord::Base
has_many :trades
has_many :notes, :as => :attachable
end

class Trade < ActiveRecord::Base
belongs_to :stock
has_many :notes, :as => :attachable
end

class Note < ActiveRecord::Base
belongs_to :attachable, :polymorphic => true
end

stock = Stock.new
stock.ticker = “GTT”
stock.company = “Giraffee Co.”
note = Note.new
note.text=“Hello”
stock.notes << note
stock.save;

##############################
Here is the corresponding sql:
##############################

CREATE TABLE notes (
id int(11) NOT NULL auto_increment,
attachable_type varchar(255) NOT NULL default ‘’,
attachable_id int(11) NOT NULL default ‘0’,
text text,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE stocks (
id int(11) NOT NULL auto_increment,
company varchar(255) NOT NULL default ‘’,
ticker varchar(8) NOT NULL default ‘’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE trades (
id int(11) NOT NULL auto_increment,
stock_id int(11) NOT NULL default ‘0’,
qty int(11) NOT NULL default ‘0’,
price decimal(10,2) NOT NULL default ‘0.00’,
commission decimal(10,2) NOT NULL default ‘0.00’,
traded_on datetime NOT NULL default ‘0000-00-00 00:00:00’,
bs_flag varchar(4) NOT NULL default ‘’,
PRIMARY KEY (id),
KEY fk_trades_stock (stock_id),
CONSTRAINT fk_trades_stock FOREIGN KEY (stock_id) REFERENCES
stocks (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Mufaddal K. wrote:

Kevin,

I will give this approach a try. Thank you for the input on this.

Regards.

Kevin O. wrote:

Category
has_many :images, :as=>:attachable

Product
has_many :images, :as=>:attachable

Image
belongs_to :attachable, :polymorphic=>true

The image table should have a
attachable_type varchar(255)
attachable_id integer

you can then call

category.images
product.images

I don’t know if polymorphic associations work with has_one, if not, just
make sure you set it up with an appropriate limit to get just the most
recent one.

_Kevin

mkhumri · April 24, 2006, 3:44am

Worked like a charm after the upgrade.

Thanks.

Kevin O. wrote:

Yeah, you are using an old version. Polymorphic associations require
rails 1.1.
Not sure which version of activerecord that corresponds with.

You will probably need to upgrade your ruby too, I seem to recall that
rails has issues with 1.8.3.

On Sunday, April 23, 2006, at 9:24 PM, Mufaddal K. wrote:
   from (eval):5:in `has_many'
stock.ticker = “GTT”
CREATE TABLE notes (
ticker varchar(8) NOT NULL default ‘’,
bs_flag varchar(4) NOT NULL default ‘’,

belongs_to :attachable, :polymorphic=>true
I don’t know if polymorphic associations work with has_one, if not, just
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails
_Kevin

mkhumri · April 23, 2006, 10:11pm

Yeah, you are using an old version. Polymorphic associations require
rails 1.1.
Not sure which version of activerecord that corresponds with.

You will probably need to upgrade your ruby too, I seem to recall that
rails has issues with 1.8.3.

On Sunday, April 23, 2006, at 9:24 PM, Mufaddal K. wrote:

   from (eval):5:in `has_many'
stock.ticker = “GTT”
CREATE TABLE notes (
ticker varchar(8) NOT NULL default ‘’,
bs_flag varchar(4) NOT NULL default ‘’,

belongs_to :attachable, :polymorphic=>true
I don’t know if polymorphic associations work with has_one, if not, just
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

_Kevin