Question on Active Record select and join

I'm learning how to use ActiveRecord. To work with an existing dataase I created the following classes

class Product < ActiveRecord::Base   set_primary_key("ProductID")   set_table_name("Product")   has_many :variants, :foreign_key => 'ProductID'   has_and_belongs_to_many :category, :foreign_key => 'ProductID', :join_table =>'ProductCategory', :association_foreign_key =>'CategoryID' end

class Category < ActiveRecord::Base   set_primary_key("CategoryID")   set_table_name("Category")   has_and_belongs_to_many :product, :foreign_key =>'CategoryID', :join_table =>'ProductCategory', :association_foreign_key =>'ProductID' end

class Variant < ActiveRecord::Base   set_primary_key("VariantID")   set_table_name("ProductVariant")   #Foreign keys in table ProductVariant   belongs_to :product, :foreign_key => 'ProductID'   belongs_to :capacity, :foreign_key => 'CapacityID'   belongs_to :material, :foreign_key => 'MaterialID' end

class Capacity < ActiveRecord::Base   set_primary_key("CapacityID")   has_many :variants, :foreign_key => 'CapacityID' end

class Material < ActiveRecord::Base   set_primary_key("MaterialID")   has_many :variants, :foreign_key => 'MaterialID' end

I am trying to create the following SQL query using ActiveRecord SELECT ProductVariant.Name, ProductVariant.Inventory, ProductVariant.Price, Capacities.Name AS CapName, Materials.Name AS MatName

FROM ProductVariant INNER JOIN Product ON ProductVariant.ProductID = Product.ProductID INNER JOIN Materials ON ProductVariant.MaterialID = Materials.MaterialID INNER JOIN Capacities ON ProductVariant.CapacityID = Capacities.CapacityId

WHERE Product.ProductID = 17

I tried the following ActiveRecord code widget=Product.find(17) widget.variants.find(:all, :select =>'ProductVariant.Name, Inventory, Price, Capacities.Name, Materials.Name', :include=> [:capacity, :material] ).each do |v|

puts v.Name.squeeze(" ").chop! + "\t\t In stock: \t" + v.Inventory.to_s + "\tPrice: \t" + v.Price.to_s + "\tCapacity: \t" + v.capacity.Name.to_s + "\t Material: \t" + v.material.Name.to_s

end

but ActiveRecord is still selecting all the ProductVariant fields instead of just the ones listed in the select paramater

I also tried widget.variants.find(:all, :select =>'ProductVariant.Name,Inventory, Price,Capacities.Name as CapName,Materials.Name as MatName', :joins=>'ProductVariant INNER JOIN Product ON ProductVariant.ProductID = Product.ProductID INNER JOIN Materials ON ProductVariant.MaterialID = Materials.MaterialID INNER JOIN Capacities ON ProductVariant.CapacityID = Capacities.CapacityId').each do |v|

but just got errors.

Any ideas would be greatly appreciated.

thanks

Luis

Check out your log/development.log, it will show you the queries that are actually getting executed.

I'm thinking that this is the way ActiveRecord is designed. When it retrieves a record object, it will only retireve a complete one. It doesn't make sense to grab an object and only populate half of it's fields.