How do you rewrite this with the :group option?

rubyonrails-talk
1

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.

2

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.