CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
0xSina
0xSina

Reputation: 21593

Activerecord query to include count of associations

I have a model User and it has many posts. To query first 5 users, I do:

User.all :limit => 5

which returns a user which is good, but when getting the count of # of posts each user has, it queries the DB again:

a_user.posts.count

Is there a way I can avoid this query and get the count with the first query ? I am looping through users and it blocks each time it query for user's post's count.

Upvotes: 2

Views: 4872

Answers (2)

John Furtado
John Furtado

Reputation: 555

Rails has a built in method, that has to be set to true when declaring associations like has_many, and also, you must add a column to the database.

set counter_cache: true when you declare the has_many association

add #{table_name}_count to the database and it will be incremented and decremented automatically, so you can select this column directly when you query for the first five users.

More info on:

http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html

http://railscasts.com/episodes/23-counter-cache-column

Upvotes: 4

quandrum
quandrum

Reputation: 1646

The method your looking for the the rails eager loading #includes, which will still create two queries (but not the 6 if you do User.all, :limit=>5 and then user.posts on each), but will load the posts in one query:

User.includes(:posts).limit(5)

which should create the SQL:

SELECT * FROM users LIMIT 5
SELECT posts.* FROM posts
  WHERE (posts.user_id IN (1,2,3,4,5)) # Numbers are found ID

You can find this information from Active Record Query Guide

Upvotes: 2

Related Questions