CODEWITHSUNDEEP
ruby-on-railsdatabaseactiverecord
Ben Kreeger
Ben Kreeger

Reputation: 6344

Rails ActiveRecord: legacy table without primary key shows nil for result?

I've got a Rails app that'll be sitting on top of a legacy database with some ugly tables that I'm having to deal with. One is a feature_attributes table related to features. Problem is that this feature_attributes table doesn't have a primary key. I wouldn't think that'd be a problem, but evidently it is. I've got my model name which is different from the table name, but I'm using set_table_name to specify the right one.

class Feature < ActiveRecord::Base
  has_many :feature_attributes
end

class FeatureAttribute < ActiveRecord::Base
  set_table_name 'feature_attribute'
  belongs_to :feature
end

Once I load a feature that I know has related feature_attributes and call feature.feature_attributes on it, I get nil. Matter of fact, even FeatureAttribute.first gives me nil. The result of FeatureAttribute.any? returns false. I'm worried that ActiveRecord isn't reading any of the data from the table because there isn't a primary key. Is that what's going on here?

The feature_attribute table has the following columns.

feature_id
attribute_name
attribute_value
created_date
modified_date

Help!

I'll also note that running the generated SQL directly against the MySQL server actually gets me the rows I want.

SELECT `feature_attribute`.* FROM `feature_attribute` WHERE `feature_attribute`.`feature_id` = 24;

EDIT: I am so sorry. Next time I'll learn to check my database.yml. Evidently I was reading from a test database that had an identical feature table, but the feature_attribute table was totally empty.

I feel like a moron. Thanks for your help, everyone; I'm up voting you all for your troubles. I did like just about everybody's answer. (Can I down vote myself? :))

Upvotes: 4

Views: 7349

Answers (4)

Matt Connolly
Matt Connolly

Reputation: 9847

class Feature < ActiveRecord::Base
  self.table_name = 'legacy_table_name'
  self.primary_key = nil
end

Then query using Feature.find_by field:'value' or other ActiveRecord queries.

Upvotes: 2

Unixmonkey
Unixmonkey

Reputation: 18784

If it isn't possible to update the table to just add a autoincrement primary key, then you might be better served dealing with it manually:

class Feature < ActiveRecord::Base
  # get rid of has_many feature_attributes and use this instead
  def feature_attributes
    FeatureAttribute.find_by_sql(["select * from feature_attribute where feature_id = ?", self.id])
  end
end

Upvotes: 1

nathanvda
nathanvda

Reputation: 50057

If a certain attribute can occur only once for a feature, you assume attribute_name is the key, in combination with the feature_id. Rails does not support composite primary keys out of the box, but there is a gem called composite_primary_keys that supports just that.

In your model you can then write

set_primary_keys :feature_id, :attribute_name

If an attribute-name could occur multiple time for a single feature, the combination of feature_id, attribute_name and attribute_value is the key, and you should write

set_primary_keys :feature_id, :attribute_name, :attribute_value

Hope this helps.

[edit] Alternative approach:

The above clearly is not enough, so you could also do the following:

class Feature
  has_many :feature_attributes, :finder_sql => 'select * from feature_attributes where feature_id=\'#{id}\''
end

Hope that this does help :)

Upvotes: 4

Ion Br.
Ion Br.

Reputation: 2648

Try to also set the primary key:

class FeatureAttribute < ActiveRecord::Base
  set_table_name 'feature_attribute'
  set_primary_key 'feature_id'

  belongs_to :feature
end

UPDATE

I think your problem lies anywhere else. I just tested and ActiveRecords works fine with tables without a primary key:

For a simple table:

class CreateThings < ActiveRecord::Migration
  def change
    create_table :things, :id => false do |t|
      t.string :name

      t.timestamps
    end
  end
end

in console:

Loading development environment (Rails 3.1.1)
irb(main):001:0> Thing.create(:name=>'A name for the thing')
   (0.1ms)  BEGIN
  SQL (0.3ms)  INSERT INTO `things` (`created_at`, `name`, `updated_at`) VALUES ('2011-11-02 16:33:48', 'A name for the thing', '2011-11-02 16:33:48')
   (40.3ms)  COMMIT
=> #<Thing name: "A name for the thing", created_at: "2011-11-02 16:33:48", updated_at: "2011-11-02 16:33:48">
irb(main):002:0> Thing.first
  Thing Load (0.7ms)  SELECT `things`.* FROM `things` LIMIT 1
=> #<Thing name: "A name for the thing", created_at: "2011-11-02 16:33:48", updated_at: "2011-11-02 16:33:48">
irb(main):003:0>

UPDATE 2 Not very fine:

irb(main):003:0> Thing.create(:name=>'Another thing')
   (0.2ms)  BEGIN
  SQL (0.4ms)  INSERT INTO `things` (`created_at`, `name`, `updated_at`) VALUES ('2011-11-02 16:40:59', 'Another thing', '2011-11-02 16:40:59')
   (35.4ms)  COMMIT
=> #<Thing name: "Another thing", created_at: "2011-11-02 16:40:59", updated_at: "2011-11-02 16:40:59">
irb(main):004:0> Thing.first
  Thing Load (0.5ms)  SELECT `things`.* FROM `things` LIMIT 1
=> #<Thing name: "A name for the thing", created_at: "2011-11-02 16:33:48", updated_at: "2011-11-02 16:33:48">
irb(main):005:0> Thing.last
  Thing Load (11.8ms)  SELECT `things`.* FROM `things` ORDER BY `things`.`` DESC LIMIT 1
Mysql2::Error: Unknown column 'things.' in 'order clause': SELECT  `things`.* FROM `things`  ORDER BY `things`.`` DESC LIMIT 1
ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column 'things.' in 'order clause': SELECT  `things`.* FROM `things`  ORDER BY `things`.`` DESC LIMIT 1

Upvotes: 5

Related Questions