So I have a very simple many-to-many relation set-up in Rails.
class Ded < ActiveRecord::Base
has_and_belongs_to_many :districts, :order => "id"
end
create table deds (
id int not null auto_increment,
name varchar(60) not null,
primary key (id)
);
class District < ActiveRecord::Base
has_and_belongs_to_many :deds
end
create table districts (
id varchar(4) not null,
updated_on date null,
primary key (id)
);
create table deds_districts (
ded_id int not null,
district_id varchar(4) not null,
constraint fk_dd_ded foreign key (ded_id) references deds(id),
constraint fk_dd_district foreign key (district_id) references
districts(id)
);
This is practically straight out of the Rails book. The only difference
is the supplied ID in the districts table.
Everything works great wth some pre-loaded data that I''m using:
insert into deds values (null, ''Brian'');
insert into deds values (null, ''Paul'');
insert into districts values (''XXX'',
''2005-07-01'');
insert into districts values (''022'',
''2005-07-01'');
insert into districts values (''550'',
''2005-07-01'');
insert into deds_districts values (1, ''XXX'');
insert into deds_districts values (1, ''550'');
insert into deds_districts values (2, ''022'');
Finds and the like work just fine. And I can easily remove entries in
the join table. The problem comes into play when I go to add new entires
to the join table.
If I execute this code block in my controller:
ded = Ded.find (2)
dist_to_add = District.find(''XXX'')
ded.districts << dist_to_add
I get a new record in the join table with:
ded_id: 2
district_id: ''XXX
Can someone please tell me what''s up with the leading '' in the
district_id column? It''s causing the system to break because
there''s no
district in the districts table with ID = "''XXX"...
-Brian
Brian V. Hughes wrote:> > If I execute this code block in my controller: > > ded = Ded.find (2) > dist_to_add = District.find(''XXX'') > ded.districts << dist_to_add > > I get a new record in the join table with: > ded_id: 2 > district_id: ''XXX > > Can someone please tell me what''s up with the leading '' in the > district_id column? It''s causing the system to break because there''s no > district in the districts table with ID = "''XXX"...Seems, I''m always replying to my own posts... :) Here''s the development.log data for what''s going on behind the scenes: Ded Load (0.001003) SELECT * FROM deds WHERE deds.id = ''2'' LIMIT 1 District Load (0.000810) SELECT * FROM districts WHERE districts.id = ''XXX '' LIMIT 1 Ded Columns (0.001234) SHOW FIELDS FROM deds District Load (0.001364) SELECT t.*, j.* FROM deds_districts j, districts t WHERE t.id = j.district_id AND j.ded_id = 2 ORDER BY id SQL (0.000317) BEGIN District Columns (0.001550) SHOW FIELDS FROM districts deds_districts Columns (0.001423) SHOW FIELDS FROM deds_districts SQL (0.000890) INSERT INTO deds_districts (`district_id`, `ded_id`) VALUES (''\''XXX\'''', ''2'') SQL (0.000437) COMMIT The SQL INSERT statement is trying to insert \''XXX\'', which is being chopped to \''XXX since my field length has a max of 4. So my question is, how to I get those escaped quotes out of the INSERT VALUS? I''d also like to know, if someone can tell me, why those escaped quotes are showing up in the first place? -Brian
I''d shy away from non-int keys. In the long run, they seem to cause more problems than the perceived benefit. I don''t know much about rails or ActiveRecord, but my guess is that it is expecting an int, so it pulls in all the non whitespace and non comma chars until the length of the column for varchars. Shot in the dark, yes. I understand that having an int key on districts may seem weird (after all, it looks like its a simple lookup table with certain "code" values, right?). But IMHE (experience), any kind of meaningful primary keys on a table cause problems eventually. I apologize if this is a trail of thought you have already went down and ruled out. On 8/16/05, Brian V. Hughes <brianvh-ilmOVS5JQ6Xj7r8U7pfrKh2eb7JE58TQ@public.gmane.org> wrote:> > > So I have a very simple many-to-many relation set-up in Rails. > > class Ded < ActiveRecord::Base > has_and_belongs_to_many :districts, :order => "id" > end > > create table deds ( > id int not null auto_increment, > name varchar(60) not null, > primary key (id) > ); > > class District < ActiveRecord::Base > has_and_belongs_to_many :deds > end > > create table districts ( > id varchar(4) not null, > updated_on date null, > primary key (id) > ); > > create table deds_districts ( > ded_id int not null, > district_id varchar(4) not null, > constraint fk_dd_ded foreign key (ded_id) references deds(id), > constraint fk_dd_district foreign key (district_id) references > districts(id) > ); > > > This is practically straight out of the Rails book. The only difference > is the supplied ID in the districts table. > > Everything works great wth some pre-loaded data that I''m using: > > insert into deds values (null, ''Brian''); > insert into deds values (null, ''Paul''); > > insert into districts values (''XXX'', ''2005-07-01''); > insert into districts values (''022'', ''2005-07-01''); > insert into districts values (''550'', ''2005-07-01''); > > insert into deds_districts values (1, ''XXX''); > insert into deds_districts values (1, ''550''); > insert into deds_districts values (2, ''022''); > > Finds and the like work just fine. And I can easily remove entries in > the join table. The problem comes into play when I go to add new entires > to the join table. > > If I execute this code block in my controller: > > ded = Ded.find (2) > dist_to_add = District.find(''XXX'') > ded.districts << dist_to_add > > I get a new record in the join table with: > ded_id: 2 > district_id: ''XXX > > Can someone please tell me what''s up with the leading '' in the > district_id column? It''s causing the system to break because there''s no > district in the districts table with ID = "''XXX"... > > -Brian > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Brock Weaver http://www.circaware.com _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
No, I haven''t ruled this out. In fact, it was one of the first things I thought about, but I''m just hoping that it''s not a requirement for me to add another column to my table in order to make this work. Adding the column is easy, changing all the code where I''m using the varchar value as the ID get''s a little trickier. So I''d like to avoid doing that if I can... -Brian Brock Weaver wrote:> I''d shy away from non-int keys. In the long run, they seem to cause > more problems than the perceived benefit. I don''t know much about rails > or ActiveRecord, but my guess is that it is expecting an int, so it > pulls in all the non whitespace and non comma chars until the length of > the column for varchars. > > Shot in the dark, yes. > > I understand that having an int key on districts may seem weird (after > all, it looks like its a simple lookup table with certain "code" values, > right?). But IMHE (experience), any kind of meaningful primary keys on > a table cause problems eventually. > > I apologize if this is a trail of thought you have already went down and > ruled out.
Thought I''d give a quick update... I added an "int" ID column to my table that was using the varchar ID, fixed all the references in my controller and helper files, and everything is now working as expected... I kind of wish I didn''t have to do this, since the supplied varchar data is guaranteed to be unique and non-changing, but making the modifications to my code wasn''t that hard. -Brian Brian V. Hughes wrote:> > No, I haven''t ruled this out. In fact, it was one of the first things I > thought about, but I''m just hoping that it''s not a requirement for me to > add another column to my table in order to make this work. Adding the > column is easy, changing all the code where I''m using the varchar value > as the ID get''s a little trickier. So I''d like to avoid doing that if I > can... > > -Brian > > Brock Weaver wrote: > >> I''d shy away from non-int keys. In the long run, they seem to cause >> more problems than the perceived benefit. I don''t know much about >> rails or ActiveRecord, but my guess is that it is expecting an int, so >> it pulls in all the non whitespace and non comma chars until the >> length of the column for varchars.
Hi, As an exercise to learn Rails, I''ve been re-implementing a legacy app in RoR which also uses assigned ids (guids). My problem surfaced with habtm associations as well. I''ve had a brief look at the ActiveRecord code, but as I''m relatively new to both Ruby and Rails I got lost ;-) I''d also be very interested in finding a solution to this. - John On 18/08/2005, at 10:46 AM, Brian V. Hughes wrote:> > Thought I''d give a quick update... I added an "int" ID column to my > table that was using the varchar ID, fixed all the references in my > controller and helper files, and everything is now working as > expected... I kind of wish I didn''t have to do this, since the > supplied varchar data is guaranteed to be unique and non-changing, > but making the modifications to my code wasn''t that hard. > > -Brian > > Brian V. Hughes wrote: > >> No, I haven''t ruled this out. In fact, it was one of the first >> things I thought about, but I''m just hoping that it''s not a >> requirement for me to add another column to my table in order to >> make this work. Adding the column is easy, changing all the code >> where I''m using the varchar value as the ID get''s a little >> trickier. So I''d like to avoid doing that if I can... >> -Brian >> Brock Weaver wrote: >> >>> I''d shy away from non-int keys. In the long run, they seem to >>> cause more problems than the perceived benefit. I don''t know >>> much about rails or ActiveRecord, but my guess is that it is >>> expecting an int, so it pulls in all the non whitespace and non >>> comma chars until the length of the column for varchars. >>> > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- John Meredith <jmeredith-0iPedL6B8ETQT0dZR+AlfA@public.gmane.org>
> expected... I kind of wish I didn''t have to do this, since the supplied > varchar data is guaranteed to be unique and non-changing, but making the[snip] If only ''guaranteed unique and non-changing'' would actually happen with a real world application. Over the life of it, any ''guaranteed'' things seem to need changing at some point. That has bitten me more times than I can recall. I would be interested to know how to get around this myself, such as for code-based look ups (i.e. essentially ID columns, but with developer-friendly values that are never shown to the outside world, and do not have any meaning outside being easy for a developer to remember). On 8/17/05, Brian V. Hughes <brianvh-ilmOVS5JQ6Xj7r8U7pfrKh2eb7JE58TQ@public.gmane.org> wrote:> > Thought I''d give a quick update... I added an "int" ID column to my > table that was using the varchar ID, fixed all the references in my > controller and helper files, and everything is now working as > expected... I kind of wish I didn''t have to do this, since the supplied > varchar data is guaranteed to be unique and non-changing, but making the > modifications to my code wasn''t that hard. > > -Brian > > Brian V. Hughes wrote: > > > > No, I haven''t ruled this out. In fact, it was one of the first things I > > thought about, but I''m just hoping that it''s not a requirement for me to > > add another column to my table in order to make this work. Adding the > > column is easy, changing all the code where I''m using the varchar value > > as the ID get''s a little trickier. So I''d like to avoid doing that if I > > can... > > > > -Brian > > > > Brock Weaver wrote: > > > >> I''d shy away from non-int keys. In the long run, they seem to cause > >> more problems than the perceived benefit. I don''t know much about > >> rails or ActiveRecord, but my guess is that it is expecting an int, so > >> it pulls in all the non whitespace and non comma chars until the > >> length of the column for varchars. > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Brock Weaver [OBC]Technique