Heeelp - no idea what's going wrong


#1

I’m working on a simple CMS. The main data type are “listings” a listing
habtm (has_and_belongs_to_many) categories and subcategories.
Subcategories belong_to categories. Subcategories are basically the same
as categories, but they are treated differently in a few situations.

The problem is, I can’t seem to write the view/controller/model code
that allows me to create a listing that contains subcategories! Even
though I’m using the exact same code I used for categories, the models
are the same, and the database configs are the same, the MySQL database
is never updated.

This is the form that’s used:

<% for category in @categories %>

@listing = Listing.new(params[:listing])
@listing.categories = Category.find(params[:category_ids] # this
works
@listing.subcategories = Subcategory.find(params[:subcategory_ids] #
doesn’t
if @listing.save


end

And the databases:

create table listings (
id int not null auto_increment,

primary key (id)
);

create table subcategories (
id int not null auto_increment
name varchar(100) not null,
category_id int not null,
constraint fk_items_category foreign key (category_id) references
categories(id),
primary key (id)
);

create table listings_subcategories
(practically a duplicate of categories_listings, which works)
subcategory_id int not null,
listing_id int not null,
constraint fk_cp_subcategory foreign key (subcategory_id) references
subcategories(id),
constraint fk_cp_listing foreign key (listing_id) references
listings(id),
primary key (subcategory_id, listing_id)
);

When I try to create a listing with subcategories the array
subcategory_ids is populated correctly, and the listing is created, but
the listing has no subcategories and the table listings_subcategories
has no entries.

This problem has been driving me crazy all day. I’ve tried everything I
can think of to get it to work. If anyone has a clue, I will be greatly
indebted.

-Adam


#2

Adam B. wrote:

<for subcategory in category.subcategories %>
This is the controller code for create:
end
id int not null auto_increment
listing_id int not null,
constraint fk_cp_subcategory foreign key (subcategory_id) references
subcategories(id),
constraint fk_cp_listing foreign key (listing_id) references
listings(id),
primary key (subcategory_id, listing_id)
);

You have a composite key for subcategory, but for category you don’t
have a composite key. Even without this, ActiveRecord will need to be
told that you’re id for subcategory doesn’t follow the AR conventions
(id), so you must have set_primary_key :subcategory_id (or something
like that) in your model for subcategory

Can’t see anything else immediately
Kev


#3

You have a composite key for subcategory, but for category you don’t
have a composite key. Even without this, ActiveRecord will need to be
told that you’re id for subcategory doesn’t follow the AR conventions
(id), so you must have set_primary_key :subcategory_id (or something
like that) in your model for subcategory

Can’t see anything else immediately
Kev

Kev,

Sorry, I’m fairly new with MySQL as well (actually I’m new with all of
this stuff…) by composite key do you mean the fact that subcategories
belong_to categories, i.e. the SQL code:

category_id int not null,
constraint fk_items_category foreign key (category_id) references
categories(id),

whereas categories have no such reference.

Other than that, thanks for the help. I’ll see what I can do with that.


#4

Sorry, I’m fairly new with MySQL as well (actually I’m new with all of
this stuff…) by composite key do you mean the fact that subcategories
belong_to categories, i.e. the SQL code:

category_id int not null,
constraint fk_items_category foreign key (category_id) references
categories(id),

create table subcategories (

primary key (id)
);

create table listings_subcategories
subcategory_id int not null, <= not id like ActiveRecord expects so

primary key (subcategory_id, listing_id) <= you have specified a
composite primary key - ie a key that is composed of two values
subcategory_id + listing_id
);

see this for a discussion of composite keys and AR support

http://wrath.rubyonrails.org/pipermail/rails-core/2006-February/000816.html

If you specified
primary key (subcategory_id) and it was an auto_inc style id, then AR
should have no problems

Kev


#5

Thanks for all the new info. It’s nice to get a more complete
understanding of how this all works.

However, I’m still not quite sure why this composite key is different
from the other composite key I’m using:

create table categories (
id int not null auto_increment
name varchar(100) not null,
primary key (id)
);

create table categories_listings
category_id int not null,
listing_id int not null,
constraint fk_cp_category foreign key (category_id) references
categories(id),
constraint fk_cp_listing foreign key (listing_id) references
listings(id),
primary key (category_id, listing_id)

Which works fine for categories and listings. I got this setup, as well
as the form and controller code I posted earlier, from this pdf:

http://jrhicks.net/Projects/rails/has_many_and_belongs_to_many.pdf

Sorry to be so much trouble, and thanks again.

-Adam


#6

I found the problem. I should’ve done a search first; it turns out
there’s a bug in .save:

http://www.ruby-forum.com/topic/48785

I made the suggested change (add the categories, save, add the
subcategories, and save again) and it works.

Thanks,

Adam