lrlebron-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
2006-Dec-09 20:08 UTC
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 -~----------~----~----~----~------~----~------~--~---