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