The availability of EagerLoadingOfAssociations (as of Rails 0.12) removes the need for Piggy-back queries in nearly all cases.
Consider this model:
class Post < \ActiveRecord::Base
has_one :author
end
class Author < \ActiveRecord::Base
belongs_to :post
end
The easy way of displaying the author’s name alongside each post would be:
for post in Post.find_all
puts post.title + " was written by " + post.author.name
end
Unfortunately, that’ll generate one additional SQL query per post looped over. Some times that doesn’t really matter, but if it’s a frequently used query and the number of posts you’re looping over is high, it can be a problem.
Piggy-backs to the rescue! Active Records are dynamic beasts that don’t mind carrying additional attributes with them. So what you do is create a specialized static finder method that grabs the author name along-side the rest of the post:
class Post < \ActiveRecord::Base
def self.find_all_with_author_name
find_by_sql(
"SELECT posts.*, authors.name as author_name " +
"FROM posts, authors " +
"WHERE posts.author_id = authors.id"
)
end
end
Now you can do the same loop referencing the author_name attribute:
for post in Post.find_all_with_author_name
puts post.title + " was written by " + post.author_name
end
Just a single SQL query!
One thing to be aware of—it appears that your additional attributes are always strings. I wanted to do something like this:
class Order < \ActiveRecord::Base
has_many :details
def self.find_with_subtotal
find_by_sql(
"SELECT o.*, sum(d.quantity * d.price) AS subtotal" +
" FROM orders o INNER JOIN details d" +
" ON d.order_id = o.id"
)
end
end
and then be able to use order.subtotal to do calculations. In this scenario, I have to use order.subtotal.to_f
Question: how to apply this technique when displaying for example a page containing all posts with all their respective comments? A post can have many comments. Basically retrieving a list of objects along with their collections in one sql?
The availability of EagerLoadingOfAssociations (as of Rails 0.12) removes the need for Piggy-back queries in nearly all cases.
Consider this model:
class Post < \ActiveRecord::Base
has_one :author
end
class Author < \ActiveRecord::Base
belongs_to :post
end
The easy way of displaying the author’s name alongside each post would be:
for post in Post.find_all
puts post.title + " was written by " + post.author.name
end
Unfortunately, that’ll generate one additional SQL query per post looped over. Some times that doesn’t really matter, but if it’s a frequently used query and the number of posts you’re looping over is high, it can be a problem.
Piggy-backs to the rescue! Active Records are dynamic beasts that don’t mind carrying additional attributes with them. So what you do is create a specialized static finder method that grabs the author name along-side the rest of the post:
class Post < \ActiveRecord::Base
def self.find_all_with_author_name
find_by_sql(
"SELECT posts.*, authors.name as author_name " +
"FROM posts, authors " +
"WHERE posts.author_id = authors.id"
)
end
end
Now you can do the same loop referencing the author_name attribute:
for post in Post.find_all_with_author_name
puts post.title + " was written by " + post.author_name
end
Just a single SQL query!
One thing to be aware of—it appears that your additional attributes are always strings. I wanted to do something like this:
class Order < \ActiveRecord::Base
has_many :details
def self.find_with_subtotal
find_by_sql(
"SELECT o.*, sum(d.quantity * d.price) AS subtotal" +
" FROM orders o INNER JOIN details d" +
" ON d.order_id = o.id"
)
end
end
and then be able to use order.subtotal to do calculations. In this scenario, I have to use order.subtotal.to_f
Question: how to apply this technique when displaying for example a page containing all posts with all their respective comments? A post can have many comments. Basically retrieving a list of objects along with their collections in one sql?