Rails ActiveRecord: deprecated table without primary key shows zero for result?

Question

I have a Rails application that will sit on top of an old database with some ugly tables that I have to deal with. One of them is the feature_attributes table related to features . The problem is that there is no primary key in this feature_attributes table. I would not think that this would be a problem, but obviously it is. I have my model name, which is different from the table name, but I use set_table_name to indicate the correct one.

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

As soon as I load a function that I know is associated with feature_attributes and calls feature.feature_attributes , I get nil . In fact, even FeatureAttribute.first gives me nil . Result of FeatureAttribute.any? returns false . I am worried that ActiveRecord does not read data from the table because there is no primary key. Is this what is going on here?

The feature_attribute table has the following columns.

 feature_id attribute_name attribute_value created_date modified_date 

Help!

I also want to note that running the generated SQL directly on the MySQL server actually gives me the rows I need.

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

EDIT : I'm so sorry. Next time I will learn to check my database.yml . Obviously, I read from a test database that had an identical function table, but the feature_attribute table was completely empty.

I feel like a moron. Thank you for all your help; I vote for you for your problems. I liked that everyone answered. (Can I vote for myself? :))

Answer 1

Try also setting 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 elsewhere. 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 good:

 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 

Answer 2

If a particular attribute can only happen once for a function, then you assume that attribute_name is the key in combination with 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 write

 set_primary_keys :feature_id, :attribute_name 

If an attribute name can occur several times for one function, 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 is clearly not enough, so you can also do the following:

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

Hope this helps :)

Answer 3

 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.

Answer 4

If it is not possible to update the table to just add the primary auto-increment key, then you might be better off serving 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