Hello all and thank you for reading this message. I am new to RoR, and ruby too, but i am quite excited about it. I am developing a RoR project and with active record associations in the following manner # Tables (all tables have only one record) CREATE TABLE `webcars_development`.`cars` ( `id` int(11) NOT NULL auto_increment, `model_id` int(11) NOT NULL default ''0'', `photo` blob, `created_at` datetime default NULL, `updated_at` datetime default NULL, PRIMARY KEY (`id`), KEY `FK_CARS_MODELS` (`model_id`), CONSTRAINT `FK_CARS_MODELS` FOREIGN KEY (`model_id`) REFERENCES `models` (`id`) ); CREATE TABLE `webcars_development`.`models` ( `id` int(11) NOT NULL auto_increment, `name` varchar(64) NOT NULL default '''', `brand_id` int(11) NOT NULL default ''0'', `created_at` datetime default NULL, `updated_at` datetime default NULL, PRIMARY KEY (`id`), UNIQUE KEY `name_idx` (`nome`), KEY `FK_MODELS_BRAND` (`brand_id`), CONSTRAINT `FK_MODELS_BRAND` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ); CREATE TABLE `webcars_development`.`brands` ( `id` int(11) NOT NULL auto_increment, `name` varchar(16) NOT NULL default '''', `created_at` datetime default NULL, `updated_at` datetime default NULL, PRIMARY KEY (`id`), UNIQUE KEY `name_idx` (`name`) ); # Models class Car < ActiveRecord::Base belongs_to :model has_one :brand, :through => :models validates_presence_of :model_id end class Model < ActiveRecord::Base belongs_to :brand has_one :car, :through => :models validates_associated :brand validates_presence_of :brand, :message => ''is unknown'' end class Marca < ActiveRecord::Base has_one :model, :through => :brands end # Controller (only index action needed for the example) class CarsController < ApplicationController def index @cars = Cars.find(:all) end end # The view <table> <tr> <th>Brand</th> <th>Model</th> <th>Photo</th> </tr> <% for car in @cars %> <tr> <td><%=h car.model.brand.name %></td> <td><%=h car.model.name %></td> <td><%=h car.photo %></td> </tr> <% end %> Ok, so my question is this. When i went trough the MySQL logs i saw that active record calls each table on its time instead of joining them. It runs the following queries: SELECT * FROM `cars` SHOW FIELDS FROM `cars` SHOW FIELDS FROM `models` SELECT * FROM `models` WHERE (`models`.`id` = 1) SHOW FIELDS FROM `brands` SELECT * FROM `brands` WHERE (`brands`.`id` = 1) should it not be running a query similar to this one? SHOW FIELDS FROM `cars` SHOW FIELDS FROM `models` SHOW FIELDS FROM `brands` SELECT * FROM `cars` INNER JOIN `models` ON `models`.`id` = `cars`.`model_id` AND `models`.`id` = 1 INNER JOIN `brands` ON `brands`.`id` = `models`.`brand_id` AND `brands`.`id` = 1 What am i doing wrong? Is it the tables, the associations? Or does active record simply does not behave like this. It seems a little excessive to execute 3 queries when it could all be done just in one query with joins. I am still new to RoR, but this would make since would it not? If you have a heavily loaded server, and with a lot of associations this would probaly slowdown the overall performance because of too many call to the SQL engine. Any help on this matter would be very great, and i would be very thankful! Thank for the help! CrazyMenConnected --~--~---------~--~----~------------~-------~--~----~ 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 groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Thank you for your answer, i will look in to eager-loading. But please clarify one thing for me. You said that its excessive to run the joined query if im not going to access the associations, but in the views when i type "cars.model.brand" isn''t that accessing the AR associations? (it''s a simple example i know) Thanks again for the help On Jul 22, 1:07 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On Jul 22, 12:34 pm, CrazyMenConnected <CrazyMenConnec...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > wrote: > > > > > them. It runs the following queries: > > > SELECT * FROM `cars` > > SHOW FIELDS FROM `cars` > > SHOW FIELDS FROM `models` > > SELECT * FROM `models` WHERE (`models`.`id` = 1) > > SHOW FIELDS FROM `brands` > > SELECT * FROM `brands` WHERE (`brands`.`id` = 1) > > > should it not be running a query similar to this one? > > > SHOW FIELDS FROM `cars` > > SHOW FIELDS FROM `models` > > SHOW FIELDS FROM `brands` > > SELECT > > * > > FROM > > `cars` > > INNER JOIN `models` ON > > `models`.`id` = `cars`.`model_id` AND > > `models`.`id` = 1 > > INNER JOIN `brands` ON > > `brands`.`id` = `models`.`brand_id` AND > > `brands`.`id` = 1 > > > What am i doing wrong? Is it the tables, the associations? Or does > > active record simply does not behave like this. > > It seems a little excessive to execute 3 queries when it could all be > > done just in one query with joins. > > True, but it would be excessive to run 1 query with 3 joins if you > weren''t going to access the associations at all (and would be even > worse if you had lots of associations). > Have a read through the api docs on eager-loading (the :include > option). > > Fred--~--~---------~--~----~------------~-------~--~----~ 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@googlegroups.com For more options, visit this group at groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Jul 22, 2:35 pm, CrazyMenConnected <CrazyMenConnec...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Thank you for your answer, i will look in to eager-loading. > > But please clarify one thing for me. > You said that its excessive to run the joined query if im not going to > access the associations, but in the views when i type > "cars.model.brand" isn''t that accessing the AR associations? (it''s a > simple example i know)Yes you are. My point was that at the point at which you do find :all, rails doesn''t know that you''re going to do that. Fred> > Thanks again for the help > > On Jul 22, 1:07 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > wrote: > > > On Jul 22, 12:34 pm, CrazyMenConnected <CrazyMenConnec...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > wrote: > > > > them. It runs the following queries: > > > > SELECT * FROM `cars` > > > SHOW FIELDS FROM `cars` > > > SHOW FIELDS FROM `models` > > > SELECT * FROM `models` WHERE (`models`.`id` = 1) > > > SHOW FIELDS FROM `brands` > > > SELECT * FROM `brands` WHERE (`brands`.`id` = 1) > > > > should it not be running a query similar to this one? > > > > SHOW FIELDS FROM `cars` > > > SHOW FIELDS FROM `models` > > > SHOW FIELDS FROM `brands` > > > SELECT > > > * > > > FROM > > > `cars` > > > INNER JOIN `models` ON > > > `models`.`id` = `cars`.`model_id` AND > > > `models`.`id` = 1 > > > INNER JOIN `brands` ON > > > `brands`.`id` = `models`.`brand_id` AND > > > `brands`.`id` = 1 > > > > What am i doing wrong? Is it the tables, the associations? Or does > > > active record simply does not behave like this. > > > It seems a little excessive to execute 3 queries when it could all be > > > done just in one query with joins. > > > True, but it would be excessive to run 1 query with 3 joins if you > > weren''t going to access the associations at all (and would be even > > worse if you had lots of associations). > > Have a read through the api docs on eager-loading (the :include > > option). > > > Fred--~--~---------~--~----~------------~-------~--~----~ 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@googlegroups.com For more options, visit this group at groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---