CODEWITHSUNDEEP
ruby-on-railsactiverecorddatabase-relations
M2_
M2_

Reputation: 317

active record relations – who needs it?

Well, I`m confused about rails queries. For example:

Affiche belongs_to :place
Place has_many :affiches

We can do this now:

@affiches = Affiche.all( :joins => :place )

or

@affiches = Affiche.all( :include => :place )

and we will get a lot of extra SELECTs, if there are many affiches:

Place Load (0.2ms)  SELECT "places".* FROM "places" WHERE "places"."id" = 3 LIMIT 1
Place Load (0.3ms)  SELECT "places".* FROM "places" WHERE "places"."id" = 3 LIMIT 1
Place Load (0.8ms)  SELECT "places".* FROM "places" WHERE "places"."id" = 444 LIMIT 1
Place Load (1.0ms)  SELECT "places".* FROM "places" WHERE "places"."id" = 222 LIMIT 1
...and so on...

And (sic!) with :joins used every SELECT is doubled!

Technically we cloud just write like this:

@affiches = Affiche.all( )

and the result is totally the same! (Because we have relations declared). The wayout of keeping all data in one query is removing the relations and writing a big string with "LEFT OUTER JOIN", but still there is a problem of grouping data in multy-dimentional array and a problem of similar column names, such as id.

What is done wrong? Or what am I doing wrong?

UPDATE:

Well, i have that string Place Load (2.5ms) SELECT "places".* FROM "places" WHERE ("places"."id" IN (3,444,222,57,663,32,154,20)) and a list of selects one by one id. Strange, but I get these separate selects when I`m doing this in each scope:

 <%= link_to a.place.name, **a.place**( :id => a.place.friendly_id ) %>

the marked a.place is the spot, that produces these extra queries.

UPDATE 2:

And let me do some math. In console we have:

 Affiche Load (1.8ms)  SELECT affiches.*, places.name FROM "affiches" LEFT OUTER JOIN "places" ON "places"."id" = "affiches"."place_id" ORDER BY affiches.event_date DESC
 <VS>
 Affiche Load (1.2ms)  SELECT "affiches".* FROM "affiches"
 Place Load (2.9ms)  SELECT "places".* FROM "places" WHERE ("places"."id" IN (3,444,222,57,663,32,154,20))

Comes out: 1.8ms versus 4.1ms, pretty much, confusing...

Upvotes: 0

Views: 99

Answers (1)

KL-7
KL-7

Reputation: 47618

Something is really strange here because :include option is intended to gather place_id attribute from every affiche and then fetch all places at once using select query like this:

select * from places where id in (3, 444, 222)

You can check that in rails console. Just start it and run that snippet:

ActiveRecord::Base.logger = Logger.new STDOUT
Affiche.all :include => :place

You might be incidentally fetching affiches without actually including places somewhere in your code and than calling place for every affiche making rails to perform separate query for every one of them.

Upvotes: 2

Related Questions