Rails Nested has_many :through With SQL Views

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!