Rails: find_by_sql and virtual column

Question

I want to display a list with tags plus the number of elements (in my example "Tasks") for each tag.

For this purpose I created the following method in my Tag model:

def self.find_with_count
  find_by_sql 'SELECT
                 Tag.name,
                 COUNT(Tag.name) AS taskcount
               FROM
                 tags AS Tag
                 INNER JOIN tags_tasks tt ON tt.tag_id = Tag.id
                 INNER JOIN tasks t ON tt.task_id = t.id
               WHERE
                 t.finished = 0
                 AND t.deleted = 0
               GROUP BY
                 Tag.name
               ORDER BY
                 Tag.name'
end

The method returns the correct tag names, but for some reason the taskcounts are not in the result. The result looks like

[#<Tag name: "hello">, #<Tag name: "world">]

As this approach doesn't seem to work, I'm wondering what the Rails-way is to accomplish such a task. Thanks!

Answer 1

The "Rails way" is to use a counter_cache:

class Tag < ActiveRecord::Base
  has_many :tag_tasks
  has_many :tasks, :through => :tag_tasks
end

# the join model
class TagTask < ActiveRecord::Base
  belongs_to :tag, :counter_cache => true
  belongs_to :task
end

class Task < ActiveRecord::Base
  has_many :tag_tasks
  has_many :tags, :through => :tag_tasks
end

This requires adding a tag_tasks_count column on your 'Tag' table.

If you add a named_scope to Tag like so:

class Tag ...
  named_scope :active, lambda { { :conditions => { 'deleted' => 0, 'finished' => 0 } } }
end

Then you can replace all Tag.find_by_count with Tag.active. Use it like this:

Tag.active.each do |t|
  puts "#{t.name} (#{t.tag_tasks_count})"
end

Answer 2

The count is there, you just can't see it since taskcount is not an attribute Rails creates for that class Task, because it isn't a column that it can see. You have to use the attributes call to find it. Sample:

class Tag < ActiveRecord::Base
  ...
  def taskcount
    attributes['taskcount']
  end
end

Tag.find_with_count.each do |t|
  puts "#{t.name}: #{t.taskcount}"
end