module ActiveRecord
  module Acts #:nodoc:
    module Taggable #:nodoc:
      def self.included(base)
        base.extend(ClassMethods)  
      end
      
      module ClassMethods
        def acts_as_taggable(options = {})
          write_inheritable_attribute(:acts_as_taggable_options, {
            :taggable_type => ActiveRecord::Base.send(:class_name_of_active_record_descendant, self).to_s,
            :from => options[:from]
          })
          
          class_inheritable_reader :acts_as_taggable_options

          has_many :taggings, :as => :taggable, :dependent => true
          has_many :tags, :through => :taggings

          include ActiveRecord::Acts::Taggable::InstanceMethods
          extend ActiveRecord::Acts::Taggable::SingletonMethods          
        end
      end
      
      module SingletonMethods
        def find_tagged_with(list, options = {})
          local_options = { :limit => 1000, :offset => 0 }.merge(options)
          find_by_sql([
            "SELECT DISTINCT #{table_name}.* FROM #{table_name}, tags, taggings " +
            "WHERE #{table_name}.#{primary_key} = taggings.taggable_id " +
            "AND taggings.taggable_type = ? " +
            "AND taggings.tag_id = tags.id AND tags.name IN (?) #{"AND (#{local_options[:conditions]})" if local_options[:conditions]} LIMIT ? OFFSET ?",
            acts_as_taggable_options[:taggable_type], list, local_options[:limit], local_options[:offset]
          ])
        end
        
        def count_tagged_with(list, options = {})
          local_options = {}.merge(options)
          find_by_sql([
            "SELECT COUNT(DISTINCT #{table_name}.#{primary_key}) AS cnt FROM #{table_name}, tags, taggings " +
            "WHERE #{table_name}.#{primary_key} = taggings.taggable_id " +
            "AND taggings.taggable_type = ? " +
            "AND taggings.tag_id = tags.id AND tags.name IN (?) #{"AND (#{local_options[:conditions]}) " if local_options[:conditions]}",
            acts_as_taggable_options[:taggable_type], list
          ]).first.cnt.to_i
        end

        def find_tagged_with_intersecting(list, options = {})
          local_options = { :limit => 1000, :offset => 0 }.merge(options)
          find_by_sql([
            "SELECT DISTINCT #{table_name}.* FROM #{table_name}, tags, taggings " +
            "WHERE #{table_name}.#{primary_key} = taggings.taggable_id " +
            "AND taggings.taggable_type = ? " +
            "AND taggings.tag_id = tags.id AND tags.name IN (?) #{"AND (#{local_options[:conditions]}) " if local_options[:conditions]} GROUP BY #{table_name}.id HAVING COUNT(#{table_name}.id) = #{list.size} LIMIT ? OFFSET ?",
            acts_as_taggable_options[:taggable_type], list, local_options[:limit], local_options[:offset]
          ])
        end

        def count_tagged_with_intersecting(list, options = {})
          local_options = {}.merge(options)
          find_by_sql([
            "SELECT COUNT(*) AS cnt FROM (SELECT #{table_name}.#{primary_key} AS cnt FROM #{table_name}, tags, taggings " +
            "WHERE #{table_name}.#{primary_key} = taggings.taggable_id " +
            "AND taggings.taggable_type = ? " +
            "AND taggings.tag_id = tags.id AND tags.name IN (?) " +
            "#{"AND (#{local_options[:conditions]})" if local_options[:conditions]} " +
            "GROUP BY taggings.taggable_id HAVING COUNT(taggings.taggable_id) = #{list.size}) AS x",
            acts_as_taggable_options[:taggable_type], list
          ]).first.cnt.to_i
        end               
      end
      
      module InstanceMethods
        def tag_with(list)
          Tag.transaction do
            taggings.destroy_all

            Tag.parse(list).each do |name|
              if acts_as_taggable_options[:from]
                send(acts_as_taggable_options[:from]).tags.find_or_create_by_name(name).on(self)
              else
                Tag.find_or_create_by_name(name).on(self)
              end
            end
          end
        end

        def tag_list
          tags.collect { |tag| tag.name.include?(" ") ? "'#{tag.name}'" : tag.name }.join(" ")
        end
      end
    end
  end
end

assert_equal 2, Post.find_tagged_with_intersecting(%w{TagOne TagTwo}).size
assert_equal 2, Post.count_tagged_with_intersecting(%w{TagOne TagTwo})
assert_equal 1, Post.find_tagged_with_intersecting(%w{TagOne TagTwo}, :conditions => 'status = 1').size
assert_equal 1, Post.count_tagged_with_intersecting(%w{TagOne TagTwo}, :conditions => 'status = 1')
p = Post.find_tagged_with("TagOne", :conditions => 'status = 1')

(6.months.ago.to_date..1.year.ago.to_date).to_s(:db)
=> "BETWEEN '2005-07-27' AND '2005-01-22'"

REPAIR TABLE tbl_name QUICK;

def find_all_with_count
    self.find_by_sql("SELECT t.*, COUNT(pt.post_id) AS count FROM tags t, posts_tags pt WHERE pt.tag_id = t.id GROUP BY t.id ORDER BY count DESC;")
end

@tag_info = Tag.find_all_with_count
@tag_info.each { |t| .. you now have t.name, t.count, etc .. }

SELECT p.*, COUNT(pt2.post_id) AS count FROM posts p, posts_tags pt, tags t, posts_tags pt2 WHERE pt.post_id=#{postid} AND t.id = pt.tag_id AND pt2.post_id != pt.post_id AND pt2.tag_id=pt.tag_id AND p.id = pt2.post_id GROUP BY pt2.post_id ORDER BY count DESC LIMIT #{num};")

SELECT *, COUNT(pt.post_id) AS count FROM posts_tags pt, tags t WHERE pt.post_id IN (SELECT pt.post_id FROM posts_tags pt, tags t WHERE pt.tag_id = t.id AND (t.name IN ('rails', 'ruby')) GROUP BY pt.post_id HAVING COUNT(pt.post_id)=2) AND t.id = pt.tag_id GROUP BY pt.tag_id ORDER BY count DESC;

SELECT p.* FROM posts_tags pt, posts p, tags t WHERE pt.tag_id = t.id AND (t.name IN ('tag1', 'tag2', 'tag3')) AND p.id=pt.post_id GROUP BY p.id HAVING COUNT(p.id) = 3;

@posts = Post.find_by_sql ("SELECT p.* FROM posts_tags pt, posts p, tags t WHERE pt.tag_id = t.id AND (t.name = '" + tags.uniq.join ('\' OR t.name=\'') + "') AND p.id=pt.post_id GROUP BY p.id HAVING COUNT(p.id) = " + tags.uniq.length.to_s)

def paginate_from_sql(model, sql, total, per_page)
  @object_pages = Paginator.new self, total, per_page, @params['page']
  @objects = model.find_by_sql(sql + " LIMIT #{per_page} " +
                               "OFFSET #{@object_pages.current.to_sql[1]}")
end