1
\$\begingroup\$

So I'm using the acts_as_taggable_on gem to tag 3 different models: Question, Blog, and Video

Each one has a site_id to distinguish which site they show up on (SaaS-ish).

I wanted to get all tags for a specific site without saving site_id right on a Tag or Tagging.

I came up with the following, but I feel like there might be something more efficient I could do.

ActsAsTaggableOn::Tag.class_eval do
  extend FriendlyId
  friendly_id :name, use: :slugged

  def self.find_by_site site, limit=0
    klasses = [Blog, Question, Video]
    sql = []
    site_ids = []
    klasses.each do |k|
      sql << "
        SELECT tags.*, (SELECT COUNT(*) FROM taggings t WHERE t.tag_id = tags.id) AS `count`
              FROM tags
              LEFT JOIN taggings
              ON (taggings.tag_id = tags.id)
              LEFT JOIN #{k.table_name} 
              ON (#{k.table_name}.id = taggings.taggable_id)
              WHERE taggings.taggable_type = '#{k.name}'
              AND #{k.table_name}.site_id = ?
      "
    end
    sql = sql.join(" UNION ")
    sql += " GROUP BY id "
    sql += " ORDER BY COUNT DESC "
    sql += "LIMIT #{limit}" if limit > 0
    klasses.count.times { site_ids << site.id }
    find_by_sql([sql, *site_ids])
  end
end

Here is the current functionality with timing:

irb(main):011:0> ActsAsTaggableOn::Tag.find_by_site Site.find(3); nil
  Site Load (0.4ms)  SELECT `sites`.* FROM `sites` WHERE `sites`.`id` = 3 LIMIT 1
  ActsAsTaggableOn::Tag Load (0.5ms)  
 SELECT tags.*, (SELECT COUNT(*) FROM taggings t WHERE t.tag_id = tags.id) AS `count`
 FROM tags
 LEFT JOIN taggings
 ON (taggings.tag_id = tags.id)
 LEFT JOIN blogs 
 ON (blogs.id = taggings.taggable_id)
 WHERE taggings.taggable_type = 'Blog'
 AND blogs.site_id = 3
 UNION 
 SELECT tags.*, (SELECT COUNT(*) FROM taggings t WHERE t.tag_id = tags.id) AS `count`
 FROM tags
 LEFT JOIN taggings
 ON (taggings.tag_id = tags.id)
 LEFT JOIN questions 
 ON (questions.id = taggings.taggable_id)
 WHERE taggings.taggable_type = 'Question'
 AND questions.site_id = 3
 UNION 
 SELECT tags.*, (SELECT COUNT(*) FROM taggings t WHERE t.tag_id = tags.id) AS `count`
 FROM tags
 LEFT JOIN taggings
 ON (taggings.tag_id = tags.id)
 LEFT JOIN videos 
 ON (videos.id = taggings.taggable_id)
 WHERE taggings.taggable_type = 'Video'
 AND videos.site_id = 3
 GROUP BY id ORDER BY COUNT DESC 
\$\endgroup\$
1
+150
\$\begingroup\$

Assuming these are all AR models with appropriate associations, I'd create a method on Site that iterates over a site instance's Blogs, Questions and Videos and collects their tags. Something like this maybe:

def get_site_tags
  tags = []
  self.blogs.each do |blog|
    (tags <<  blog.tags)
  end
  self.questions.each do |question|
    (tags <<  question.tags)
  end
  self.videos.each do |video|
    (tags <<  video.tags)
  end
  tags.flatten!
end

Would this work for you or am I missing something?

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ The one I have now works, it just feels like it could be done with better performance. Your solution is very AR friendly, but has many trips to the database (each time instance.tags is called) \$\endgroup\$ – Kyle Macey Jun 15 '13 at 23:08
  • 1
    \$\begingroup\$ this should be a map instead of each \$\endgroup\$ – tokland Jun 20 '13 at 16:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.