CODEWITHSUNDEEP
ruby-on-railserbrails-activerecord
user2985898
user2985898

Reputation: 1203

How to avoid database calls from the View in Rails?

Let's assume this example:

Model:

class User < ActiveRecord::Base
  has_many :posts
end

Controller:

def index
  @users = User.all
end

View:

<ul>
  <% @users.each do |u| %>
    <li>
      Username: <%= u.username %><br />
      <%= pluralize(u.posts.count, "post") %>
    </li>
  <% end %>
</ul>

From what I understand (by looking at WEBrick in the command line), it does not do a database call for u.username, but it does for u.posts.count for each cycle in the loop. I want to avoid that, so I store the "posts" in an instance variable in the controller (@posts = Post.all, for example) and replace the u.posts.count with @posts.where(:user_id => u.id).count, but it still does a database call for each cycle. Doesn't the application have all of the information stored in the @posts array in the controller?

Note: None of this is specific to my case (I am not displaying a list of users); I'm simply using this to serve as an example.

Upvotes: 1

Views: 1376

Answers (2)

fatfrog
fatfrog

Reputation: 2160

Another option is to do a SQL query with a subselect for your count. The following query assumes User.id = Post.user_id

@users = User.select("u.username").
select("count(p.id) AS post_count").
from("users u").
joins("LEFT JOIN posts p ON u.id = p.user_id").
group("u.id").
all

Then in your view:

<% @users.each do |u| %>
<li>
  Username: <%= u.username %><br />
  <%= pluralize(u[:post_count], "post") %>
</li>

This should be your output to the console:

Started GET "/reports" for 127.0.0.1 at 2013-11-28 16:33:31 -0700
Processing by ReportsController#index as HTML
  User Load (0.8ms)  SELECT u.username, count(p.id) AS post_total FROM users u LEFT JOIN posts p ON u.id = p.user_id GROUP BY u.id
  Rendered users/index.html.erb within layouts/application (0.5ms)
Completed 200 OK in 10ms (Views: 7.3ms | ActiveRecord: 1.0ms)

Upvotes: 1

zkcro
zkcro

Reputation: 4344

One way to deal with this is a counter_cache. You effectively add a new posts_count property to your User model, and update it every time you modify the user's posts. Rails makes this easy to do with the counter_cache association option:

class User < ActiveRecord::Base
  has_many :posts, counter_cache: true
end

With that setup, calling u.posts.size will instead return the count stored in the user model instead of hitting the database (make sure you use size, not count).

For more info on the :counter_cache option, check the Rails Association Basics guide (section 4.1.2.3). This blog post covers how to actually go about adding one in, including the migration and initialising the values.

The second way you could do this, would be to load all the posts in your controller method, as you attempted, but it could be a bit neater to simple eager load them instead (@users = User.includes(:posts).all). The reason it didn't work for you was because you were using count not size — count always hits the database with a SELECT COUNT(*) statement, whereas size is more intelligent and will avoid hitting the database if possible. (This is why you should make sure you use size when using a counter_cache too.)

This is valid too, but if you're not actually going to use the posts in any way though, it may be preferable to avoid pulling them all from the database, hence the appeal of the counter_cache approach.

Upvotes: 4

Related Questions