Development Blog: Viget Labs

Discovery

At a recent code review for a Pointless Corp project, FeedStitch, we came across this:

  has_many :group_feeds, :dependent => :destroy
  has_many :feeds, :through => :group_feeds

  has_many :entries, 
    :finder_sql =>  "SELECT * 
                    FROM entries
                    WHERE feed_id IN
                    (SELECT feed_id
                    FROM group_feeds
                    WHERE group_id=#{id})
                    ORDER BY published_at DESC",
    :counter_sql => "SELECT COUNT(*) FROM ...

Seeing that much SQL, and duplicated, we took a moment to diagram and discuss possible improvements. In the example above we were attempting to get at Entry through Feed which was already associated via the group_feeds.

At some point I suggested using an SQL view after the discussion had led us through creating a new table to de-normalize the link between a group and an entry. The initial reaction from the group was no. I chose to press the issue because I felt the table that would be created in de-normalizing would require management by the application. On the other hand, a view would appear the same to the application while being managed by a simple SQL select. After some discussion, we chose to give views a chance as an alternative solution.

How We Did It

Generate a migration. For our application it looks like this:

  class CreateGroupEntriesView < ActiveRecord::Migration
    def self.up
      sql = <<-EOS
        CREATE VIEW group_entries AS
        SELECT group_feeds.group_id, entries.id AS entry_id
        FROM group_feeds, entries
        WHERE group_feeds.feed_id = entries.feed_id
      EOS
    
      execute sql
    end

    def self.down
      sql = "DROP VIEW group_entries"
      execute sql
    end
  end

Update Note: I include the full SQL here to make the usage a bit more clear - our project is using the rails_sql_views plugin to aid in generating the database view. If you're writing tests for your application (and you should!), this plugin is required to create a valid  schema.rb file.

Finally, in our associated model classes we set up the has_many :through as if group_entries was a full-fledged table.

  has_many :group_entries
  has_many :entries, :through => :group_entries

Why NOT to Use Views

If your database schema changes, the fear is that the SQL defining the view would need to change; but, the developer or application would be left completely unawares. In addition, the temptation exists to create a view that collects a large amount of fields from a variety of tables to save the time of having to actually build associations. Be warned! Views are not replacements for proper relationships and good database design!

Finale

The specific circumstances surrounding this application lend themselves to a very simple use for a view. I feel this simple solution is appropriate for this type of problem. Please comment with your thoughts, especially on your solutions to similar problems or if you've used views with Rails before.

Check out the Pointless Corp introduction of FeedStitch, or get right into the thick of it on FeedStitch!