Rails Eager Loading Question Find(:all, :include => [:model])

Question

Hello, I have a Topic and a Project model. I have a Many-to-many ass between them (HABTM one).

In the Topic's Index Page, I want to display the number of projects that each topic have. So I have

@topics = Topic.all(:include => [:projects])

In my controller, and so far so good. The problem is that the Project Model is so big that the query is still really slow

Topic Load (1.5ms)   SELECT * FROM "topics" 

Project Load (109.2ms)   SELECT "projects".*, t0.topic_id as the_parent_record_id FROM "projects" INNER JOIN "projects_topics" t0 ON "projects".id = t0.project_id WHERE (t0.topic_id IN (1,2,3,4,5,6,7,8,9,10,11)) 

Is there a way to make the second query not to select * but just the name or the ID? Because the counter_cache is not supported by the HABTM Ass, and I don't really want to implement it by myself... so is there a way to make this second query faster?

I just need to pull the count without loading the whole project object...

Thanks in advance,

Nicolás Hock Isaza

Answer 1

Where is a :select option in an ActionRecord::Base.find method.

Answer 2

1. counter_cache is very easy to implement
2. you can convert habtm to double has_many, i.e. has_many :projects_topics in both project and topic model (and belongs_to in projects_topics) and then use counter_cache or do eager loading only on projects_topics
3. you can do :select => "count(projects_topics.id)", :group => "topics.id" but this won't work well with postgresql if you care about it...

The second option is the best IMO, I usually don't use habtm at all, only double has_many :)

Answer 3

To expand on Devenv's answer counter cache is what you would typically use for this kind of scenario.

From the api docs:

Caches the number of belonging objects on the associate class through the use of increment_counter and decrement_counter. The counter cache is incremented when an object of this class is created and decremented when it‘s destroyed. This requires that a column named #{table_name}_count (such as comments_count for a belonging Comment class) is used on the associate class (such as a Post class). You can also specify a custom counter cache column by providing a column name instead of a true/false value to this option (e.g., :counter_cache => :my_custom_counter.) Note: Specifying a counter cache will add it to that model‘s list of readonly attributes using attr_readonly.

Here is a screen cast from ryan bates' railscasts on counter_cache.

Here is an answer to a question I asked half a year ago where the solution was an easily implemented home-brew counter cache.