Problem with active-record and sql

Question

Hi everybody, I have a little problem: I can't compose sql-query inside AR.

So, I have Project and Task models, Project has_many Tasks. Task has aasm-field (i.e. "status"; but it doesn't matter, i can be simple int or string field).

So, I want on my projects index page list all (last) projects and for every project I want count it's active, pending and resolved (for example) tasks.

Like this, just look:

• First project (1 active, 2 pending, 10 resolved)
• Second projects (4 active, 2 pending, 2 resolved)

So, sure I can do it with @projects = Project.all and then in view:

- @projects.each do |project| 
  = project.title
  = project.tasks(:conditions => {:status => "active"}).count #sure it should be in model, just for example
  = project.tasks(:conditions => {:status => "pending"}).count
  # ...
- end

This is good, but makes 1+N*3 (for 3 task statuses) queries, i want 1. The question is simple: how?.

Answer 1

I'd suggest using a counter cache in your project model to prevent needing to recount all tasks on each display of the index page - have an active_count, pending_count and resolved_count, and update them whenever the task changes state.

If you just want to modify your existing code, try:

project.tasks.count(:conditions => "status = 'active'")

You could also add a scope to your task model that would enable you to do something like:

project.tasks.active.count

EDIT

Ok so I'm half asleep - got the wrong impression from your question :/

Yep, you can do it in one query - use find_by_sql to get your projects along with the grouped counts for the tasks. You'll be able to access the group counts in the resulting array of projects.

Answer 2

You could do a find with grouping and counting. Something like:

status_counts = project.tasks.find(:all, 
                                   :group => 'status', 
                                   :select => 'status, count(*) as how_many')

This will return you a list of Task-like objects with status and how_many attributes which you can then use to give your summary. E.g.

<%= status_counts.map { |sc| "#{sc.how_many} #{sc.status} }.to_sentence %>

Answer 3

Maybe you could, in your Project controller:

1. Fetch all your projects: Project.all
2. Fetch all your tasks: Task.all

Then, create a hash with something like

@statuses = Hash.new
@tasks.each do |t|
  @statuses[:t.project_id][:t.status] += 1
end

And then use it in your view:

First project (<%= @statuses[:@project.object_id][:active] %> active)

This is not the prefect solution, but it is easy to implement and only use two (big) queries. Of course, this would re-create a hash every time, so you might want to look into database indexes or cache systems.

Also, named scopes would be interesting, like Task.active.

Answer 4

So, the right answer is:

Projects.all(:joins  => :tasks, 
             :select => 'projects.*, 
                         sum(tasks.status="pending") as pending_count, 
                         sum(tasks.status = "accepted") as accepted_count, 
                         sum(tasks.status = "rejected") as rejected_count', 
             :group  => 'projects.id')