Active Record objects backed by Database Views

"Views" are a powerful feature of relational databases - a view presents the result of a query as if it was a simple table. You can select from a view or join it in another query. You can even use a view in the definition of another view. Using a view, you can hide some nasty, complicated, but useful SQL behind what appears to be a plain old table.

Here's a simple example, using mysql:

CREATE TABLE users (id integer primary key,
                    username varchar(16) not null,
                    crypted_password varchar(128) not null,
                    etc etc);

CREATE TABLE posts (id integer primary key,
                    user_id integer not null,
                    content text);

Suppose you like to keep track of posts-per-user, but you want users.username, not users.id in your results, because you are not the database and you like humanly recognisable names.

SELECT select u.username AS username, 
       (select count(*) from posts p where p.user_id = u.id) as total 
  from users u;

Depending on your specific needs, you might tack on "order by total desc limit 10" or "where u.username = 'me_again_999". It's an awful lot of stuff to type each time. Try this instead:

CREATE VIEW post_count AS
     select u.username AS username, 
            (select count(*) from posts p where p.user_id = u.id) as total
       from users u;

And now:

mysql> select * from post_count where username = 'me_again_999';
+--------------+-------+
| username     | total |
+--------------+-------+
| me_again_999 |    16 |
+--------------+-------+


mysql> select * from post_count order by total desc limit 3;
+--------------+-------+
| username     | total |
+--------------+-------+
| verbose      |   256 |
| eloquacious  |    64 |
| me_again_999 |    16 |
+--------------+-------+

Easier, no?

Now, the cool thing is that this works no matter how ugly, incomprehensible, or complicated your query is. And the über-cool thing is: you can create a Rails model on top of your view, ActiveRecord doesn't care that it's not a table.

We need a view that contains user_id instead of username so that active_record can make the join:

CREATE VIEW `post_count` AS 
      select u.id AS user_id, 
             (select count(*) from posts p where p.user_id = u.id) as total
        from users u;

class PostCount < ActiveRecord::Base
  belongs_to :user
end

class User < ActiveRecord::Base
  has_one :post_count
end

And now:

User.all.each { |u| puts "#{u.username} #{u.post_count.total}"}
verbose 256
eloquacious 64
me_again_999 16

So is that über-cool or what?