How do you rewrite this with the :group option?

Hello,

I would like to rewrite this find_by_sql query in terms
of :group, :limit, etc. options. I don’t know how to handle the
count(*) as count part though. Does anybody know how to make a more
Ruby-like implementation of the following function?

class Tag < ActiveRecord::Base
def self.find_top_tags(options = {})
options[:order] ||= ‘count desc, name asc’
options[:limit] ||= 100
query = “select tags.id, name, count(*) as count”
query << " from taggings, tags"
query << " where tags.id = tag_id"
query << " and context = ‘#{options[:on]}’" if options[:on] != nil
query << " group by tag_id"
query << " order by #{options[:order]}" if options[:order] != nil
query << " limit #{options[:limit]}" if options[:limit] != nil
tags = Tag.find_by_sql(query)
end
end

P.S. I am using acts_as_taggable_on, and finding that this function
would be nice to have.

Do you mean you don’t want to use find_by_sql?

First off, in terms of valid SQL statements, if you want to use count
(or any other grouping function), you have to group by all the non-
grouped fields in your select statement. So you would need to have
“group by tags.id, name”. Use a sql console and try out your
statements until you get what you want.

you could do something like

find(:join=>:taggings,
:select=>'select tags.id, tags.name, count(taggings.id) as
tag_count ‘,
:limit=>options[:limit] || 100,
:order=>options[:order] || ’ count(taggins.id) desc’)

maybe add :having=>count(tagging.id) > 1 or whatever.

It might be easier to use named scopes for adding the conditions.