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