Why is ActiveRecord tying to select nonex ID column?

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

On Apr 20, 1:24 am, Fearless F. [email protected] wrote:

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

On 20 April 2010 15:33, Fearless F. [email protected] 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.

Frederick C. 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

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

On Apr 20, 3:33 pm, Fearless F. [email protected] wrote:

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

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 C. 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.

  • ff

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?

On Apr 20, 4:42 pm, Fearless F. [email protected] wrote:

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

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.

Frederick C. 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”…

Thanks for your patience.

  • ff

On Apr 20, 5:34 pm, Fearless F. [email protected] 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

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

On Tue, Apr 20, 2010 at 12:51 PM, Fearless F. [email protected]
wrote:

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

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs