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!

Excellent question! I know this is an old post, but it saved me on a big project I am working on... so, thanks! — dennismonsewicz
– dennismonsewicz, Commented Mar 13, 2013 at 19:53

James A. Rosen · Accepted Answer · 2009-02-24 22:48:27Z

22

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

edited Feb 24, 2009 at 22:48

James A. Rosen

65.6k62 gold badges186 silver badges263 bronze badges

answered Feb 24, 2009 at 21:08

scottd

7,4941 gold badge27 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

James A. Rosen · Accepted Answer · 2009-02-25 15:49:49Z

10

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

edited Feb 25, 2009 at 15:49

answered Feb 24, 2009 at 16:36

James A. Rosen

65.6k62 gold badges186 silver badges263 bronze badges

2 Comments

Martin Dow Over a year ago

Hadn't spotted the counter cache, or indeed named scopes. Very nice -thanks for explaining.

dhofstet Over a year ago

Thanks for the idea with the counter cache, that seems to be a cleaner solution than using find_by_sql.

Collectives™ on Stack Overflow

Rails: find_by_sql and virtual column

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related