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
http://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 http://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 http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---