Running SQL on a model that I got from a gem

Question

So, I'm working on a project that uses the acts_as_taggable_on_steroids gem. I've installed it as a gem, and it works great and all. Here's where I'm running into problems though - I want to generate a list of the top 25 used tags, and the number of times they've been used across all taggable items.

Well, as it happens, acts_as_taggable_on_steroids, creates a table called "taggings" which contains this information. When I run the following SQL command against the DB directly...

SELECT tags.name as tag_name, COUNT(*) as num_uses
FROM taggings
JOIN tags ON tags.id = taggings.tag_id
GROUP BY tag_id
ORDER BY num_uses DESC, tags.name
LIMIT 25;

I get the following example results, as expected, sorted in descending order by most used, and then sub-sorted in alphabetical order:

====================================
tag_name            | num_uses
====================================
gadgets               15
cars                  12
programming           12
quirky                12
2009                  7
.....
...etc., etc., up to 25 rows
.....

So, I want to build a view to list out this information, and it's not cooperating. I don't have an associated model for the "taggings" table, assuming there is one, and I think it might be because I installed acts_as_taggable_on_steroids as a gem, rather than a plugin?

For example, I tried doing this in my app:

@top_taggings = Taggings.find_by_sql("SELECT tags.name as tag_name, COUNT(*) as num_uses " + 
                                     "FROM  taggings " +
                                     "JOIN tags ON tags.id = taggings.tag_id " + 
                                     "GROUP BY tag_id " +
                                     "ORDER BY num_uses DESC, tags.name" + 
                                     "LIMIT 25")

...and I get an "uninitialized constants ListingsController::Taggings" error

============================

As a follow up question, I suppose I could create my own "Taggings" model and controller, which would make sense. If I do this, however, will I conflict with anything in the acts_as_taggable_on_steroids gem? If not, I guess that might just be the easy/better way to do this anyway!

Answer 1

One thing I see is there is no space between your final ORDER BY and your LIMIT. Put a space after tags.name in the order by and see what that does.

i.e.

@top_taggings = Taggings.find_by_sql("SELECT tags.name as tag_name, COUNT(*) as num_uses " + 
                                     "FROM  taggings " +
                                     "JOIN tags ON tags.id = taggings.tag_id " + 
                                     "GROUP BY tag_id " +
                                     "ORDER BY num_uses DESC, tags.name " + 
                                     "LIMIT 25")

Answer 2

SOLVED! thanks to this post on working with Rails + Legacy databases, here: http://sl33p3r.free.fr/tutorials/rails/legacy/legacy_databases.html

By thinking of the pre-existing table as part of a "legacy" database (even though technically it's not), simple created a new model called "TaggingSearch"...

class TaggingSearch < ActiveRecord::Base

  set_table_name "taggings"

end

...and by using the "set_table_name" method, I was able to connect this new model to the existing table, and execute the "find_by_sql" method as I would on any other ActiveRecord.

The accompanying controller was simple:

class TaggingSearchController < ApplicationController

  def top_25_tags
    @taggings = TaggingSearch.find_by_sql("SELECT tags.name as tag_name, COUNT(*) as num_uses " + 
                                          "FROM  taggings " +
                                          "JOIN tags ON tags.id = taggings.tag_id " + 
                                          "GROUP BY tag_id " +
                                          "ORDER BY num_uses DESC, tags.name " + 
                                          "LIMIT 25")

    respond_to do |format|
      format.html # show.html.erb
      format.xml  { render :xml => @listing }
    end
  end

end

Now I just have to be really careful not to add any destructive actions to that "taggings" table, since it's owned and maintained by the "acts_as_taggable_on_steroids" plugin. So long as I'm only doing "SELECT" statements, I should be fine though.

That was an interesting one!