Rails - Dec 2006 - 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


--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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.


--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Chris Mohr wrote:
> 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.
not exactly, that would defeat the purpose of :select, but from my own 
experiences with AR i noticed that :select will fail as soon any joins 
are used (manually or via :include).

you can either live with this performance impact through the fetching of 
unwanted data or otherwise you better resort to .find_by_sql


-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---