I''ve loaded (and updated) thousands of these MeteredUsage records. In the middle of a run, I suddenly get: ActiveRecord::StatementInvalid: Mysql::Error: Unknown column ''id'' in ''where clause'': UPDATE `metered_usages` SET `cost` = 12603.46 WHERE `id` = NULL I can''t see anything that''s different about this particular record compared to its brethren. (Note that I''m invoking this at the console -- this is not a question about views or controllers.) So the schema and the model: ========= create_table "metered_usages", :id => false, :force => true do |t| t.integer "start_time_id" t.integer "end_time_id" t.integer "service_address_id" t.integer "metered_service_id" t.float "quantity" t.float "cost" end =========class MeteredUsage < ActiveRecord::Base belongs_to :start_time, :class_name => ''TimeDimension'', :foreign_key => ''start_time_id'' belongs_to :end_time, :class_name => ''TimeDimension'', :foreign_key => ''end_time_id'' belongs_to :service_address belongs_to :metered_service end =========Note that in this model, I''m inhibiting the generation of the id column -- the uniqueness of a metered_usage is defined by its foreign keys. The call that generated the error was an ActiveRecord#update(): active_record.update_attributes(attributes) where attributes (in this instance) were: {:start_time_id=>10991, :end_time_id=>11021, :service_address_id=>46, :metered_service_id=>97, :quantity=>"119700", :cost=>"12603.46"} So the question is: why would ActiveRecord#update() generate SQL referring to the non-existent id column? And how do I make it stop? - ff -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Frederick Cheung
2010-Apr-20 07:12 UTC
Re: why is ActiveRecord tying to select nonex ID column?
On Apr 20, 1:24 am, Fearless Fool <li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> The call that generated the error was an ActiveRecord#update(): > > active_record.update_attributes(attributes) > > where attributes (in this instance) were: > > {:start_time_id=>10991, :end_time_id=>11021, :service_address_id=>46, > :metered_service_id=>97, :quantity=>"119700", :cost=>"12603.46"} > > So the question is: why would ActiveRecord#update() generate SQL > referring to the non-existent id column? And how do I make it stop? >It is a pretty fundamental assumption of Active Record that tables have a primary key. There is (or at least there used to be) a plugin out there that added support for composite primary keys Fred -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Fearless Fool
2010-Apr-20 14:33 UTC
Re: why is ActiveRecord tying to select nonex ID column?
Frederick Cheung wrote:> It is a pretty fundamental assumption of Active Record that tables > have a primary key. There is (or at least there used to be) a plugin > out there that added support for composite primary keys > > Fred@fred: You may be right, but in that case, the documentation is wrong. From http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html we have:> :id - Whether to automatically add a primary key column. Defaults > to true. Join tables for has_and_belongs_to_many should set > :id => false.And, as I hinted at, this IS a join table. What''s interesting is that I cleared the table and rebuilt it -- no error. When I tried to update the entire table (with identical values), it gave the same error on the same record. Curiouser and curiouser. - ff -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Michael Pavling
2010-Apr-20 14:39 UTC
Re: Re: why is ActiveRecord tying to select nonex ID column?
On 20 April 2010 15:33, Fearless Fool <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> And, as I hinted at, this IS a join table.It doesn''t look like one to me - they tend to just have two fields; one for each foreign key. If it''s a join table using a :through relationship, it still exists as a model in its own right, and needs an id. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Rimantas Liubertas
2010-Apr-20 14:42 UTC
Re: Re: why is ActiveRecord tying to select nonex ID column?
> And, as I hinted at, this IS a join table.Alas, it is not. At least in RoR terms.> What''s interesting is that I cleared the table and rebuilt it -- no > error. When I tried to update the entire table (with identical values), > it gave the same error on the same record. Curiouser and curiouser.There is nothing courious about it. Rails try to locate particular entry by means of id, and if it is not there… Regards, Rimantas -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Frederick Cheung
2010-Apr-20 14:56 UTC
Re: why is ActiveRecord tying to select nonex ID column?
On Apr 20, 3:33 pm, Fearless Fool <li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> @fred: You may be right, but in that case, the documentation is wrong. > From > > http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/Sc... > > we have: > > > :id - Whether to automatically add a primary key column. Defaults > > to true. Join tables for has_and_belongs_to_many should set > > :id => false. > > And, as I hinted at, this IS a join table. > > What''s interesting is that I cleared the table and rebuilt it -- no > error. When I tried to update the entire table (with identical values), > it gave the same error on the same record. Curiouser and curiouser. >Join tables of the habtm variety are just join tables and typically have no associated model - the assumption is that they are only manipulated behind the scenes by active record. If you''re going do be doing foo.save! (or foo.update_attributes which calls save) then you need a primary key, it''s just how active record works Fred -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Fearless Fool
2010-Apr-20 15:42 UTC
Re: why is ActiveRecord tying to select nonex ID column?
My error in terminology -- it''s not a join table, but rather a "fact" table in the Dimensional Database sense. Would it be any different if this was a HABTM table? Frederick Cheung wrote:> ...If you''re going do be > doing foo.save! (or foo.update_attributes which calls save) then you > need a primary key, it''s just how active record worksFred, with all respect, I can''t accept your assertion at face value: I''ve made thousands of calls to foo.update_attributes on this table, and only this particular update has triggered the error. There must be something else going on. - ff -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Frederick Cheung
2010-Apr-20 16:33 UTC
Re: why is ActiveRecord tying to select nonex ID column?
On Apr 20, 4:42 pm, Fearless Fool <li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> My error in terminology -- it''s not a join table, but rather a "fact" > table in the Dimensional Database sense. Would it be any different if > this was a HABTM table? > > Frederick Cheung wrote: > > ...If you''re going do be > > doing foo.save! (or foo.update_attributes which calls save) then you > > need a primary key, it''s just how active record works > > Fred, with all respect, I can''t accept your assertion at face value: > I''ve made thousands of calls to foo.update_attributes on this table, and > only this particular update has triggered the error. There must be > something else going on. >I wouldn''t expect you to. Read the source: activerecord/lib/base.rb def update(attribute_names = @attributes.keys) quoted_attributes = attributes_with_quotes(false, false, attribute_names) return 0 if quoted_attributes.empty? connection.update( "UPDATE #{self.class.quoted_table_name} " + "SET #{quoted_comma_pair_list(connection, quoted_attributes)} " + "WHERE #{connection.quote_column_name(self.class.primary_key)} #{quote_value(id)}", "#{self.class.name} Update" ) end> - ff > > -- > Posted viahttp://www.ruby-forum.com/. > > -- > You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. > To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. > For more options, visit this group athttp://groups.google.com/group/rubyonrails-talk?hl=en.-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Fearless Fool
2010-Apr-20 16:34 UTC
Re: why is ActiveRecord tying to select nonex ID column?
So (apologies to Fred) this may be deeper than I thought: How does a HABTM table EVER get updated? From the Rails 1.9.1 active_record/base.rb sources, update_attributes => save => create_or_update => update. The sources for update(): # Updates the associated record with values matching those of the instance attributes. # Returns the number of affected rows. def update(attribute_names = @attributes.keys) quoted_attributes = attributes_with_quotes(false, false, attribute_names) return 0 if quoted_attributes.empty? connection.update( "UPDATE #{self.class.quoted_table_name} " + "SET #{quoted_comma_pair_list(connection, quoted_attributes)} " + "WHERE #{connection.quote_column_name(self.class.primary_key)} = #{quote_value(id)}", "#{self.class.name} Update" ) end Sure enough, it appears to depend on some sort of ''id'' -- what is the primary_key column name for HABTM tables? Or am off in the weeds? -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Fearless Fool
2010-Apr-20 16:43 UTC
Re: why is ActiveRecord tying to select nonex ID column?
Fred, as I said: apologies! But since you clearly understand better than I do, I''m hoping you can enlighten me as to how HABTM tables ever get updated? Or how a single entry in a HABTM table gets deleted (since the delete method, like update, seems to depend on the existence of an id field)? Thanks again. -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Frederick Cheung
2010-Apr-20 16:44 UTC
Re: why is ActiveRecord tying to select nonex ID column?
On Apr 20, 5:34 pm, Fearless Fool <li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> So (apologies to Fred) this may be deeper than I thought: How does a > HABTM table EVER get updated? From the Rails 1.9.1 > active_record/base.rb sources, update_attributes => save => > create_or_update => update. The sources for update(): >[snip]> Sure enough, it appears to depend on some sort of ''id'' -- what is the > primary_key column name for HABTM tables? Or am off in the weeds? >for a normal HABTM there is no corresponding model, so rows never get updated by this code path. The habtm association generates sql fragments directly and runs them Furthermore, because habtm is usually used as a ''dumb'' join table, it''s only ever a question of deleting or inserting rows Fred> -- > Posted viahttp://www.ruby-forum.com/. > > -- > You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. > To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. > For more options, visit this group athttp://groups.google.com/group/rubyonrails-talk?hl=en.-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Fearless Fool
2010-Apr-20 16:51 UTC
Re: why is ActiveRecord tying to select nonex ID column?
Frederick Cheung wrote:> for a normal HABTM there is no corresponding model, so rows never get > updated by this code path. The habtm association generates sql > fragments directly and runs them > Furthermore, because habtm is usually used as a ''dumb'' join table, > it''s only ever a question of deleting or inserting rows > > FredEnlightenment is a slow process, at least for me. So I think what''s going on is that I''ve created a table that *should* be declared as HABTM. And now that I think about it in those terms, I''m not sure why I didn''t to that in the first place. Time for another "script/generate migration"... Thanks for your patience. - ff -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Rick DeNatale
2010-Apr-22 13:27 UTC
Re: Re: why is ActiveRecord tying to select nonex ID column?
On Tue, Apr 20, 2010 at 12:51 PM, Fearless Fool <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> Frederick Cheung wrote: >> for a normal HABTM there is no corresponding model, so rows never get >> updated by this code path. The habtm association generates sql >> fragments directly and runs them >> Furthermore, because habtm is usually used as a ''dumb'' join table, >> it''s only ever a question of deleting or inserting rows >> >> Fred > > Enlightenment is a slow process, at least for me. > > So I think what''s going on is that I''ve created a table that *should* be > declared as HABTM. And now that I think about it in those terms, I''m > not sure why I didn''t to that in the first place. > > Time for another "script/generate migration"... >Actually, I think what you want is to use has_many :through 1. add an id to metered_usages 2. class MeterUsage belongs_to :service_address belongs_to :metered_service end class ServiceAddress has_many :metered_usages has_many :metered_services, :through => :metered_usages end class MeteredService has_many :metered_usages has_many :service_addresses, :through => :metered_usages end -- Rick DeNatale Blog: http://talklikeaduck.denhaven2.com/ Github: http://github.com/rubyredrick Twitter: @RickDeNatale WWR: http://www.workingwithrails.com/person/9021-rick-denatale LinkedIn: http://www.linkedin.com/in/rickdenatale -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Fearless Fool
2010-Apr-22 15:56 UTC
Re: Re: why is ActiveRecord tying to select nonex ID column?
Rick Denatale wrote:> Actually, I think what you want is to use has_many :through > > 1. add an id to metered_usages > ...Rick -- bingo -- that''s what I ended up doing. You probably already understood that metered_usage needs to carry additional data, so it couldn''t be a pure HABTM table. Thanks for the reassurance! - ff -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Possibly Parallel Threads
- Passing an SQL fragment in AR save
- render :collection calling partial with phantom object?
- Why does ActiveRecord allow perception of success when updating an ID, however it doesn't really work(i.e. no change in database)?
- creating fixtures for has_many :through
- Finding out updated fields