27 August 2010

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?

2 comments:

  1. …until the day you want to add a record… and then you remember that's a view and you can't add anything in it.
    But that's true it can be useful for some stats and other read-only data.

    PS: I have seen views based on union from several views, each of them based on some other views. When some data are wrong in the final result and start trying finding out why, you end up with a high desire to murder the guy who did that ;-).

    ReplyDelete
  2. totally agree ... you as the developer need to be aware it's a view and not a table so adding records won't work :)

    As for views based on unions of views based on other views ... well ... as soon as you find a programming language that disallows complexity, please let me know!!

    ReplyDelete