Ruby: ambition(2)テーブル結合

Rubyのコレクション系のメソッドがそのまま意味的に等価なSQLを生成し、データベースにアクセスするAmbitionって面白いですね。テーブル結合はどうなるのだろうと思いビートルズのディスコグラフィーを作って試してみました。
まずはアルバムのデータ作成

create table albums(
  id integer not null auto_increment,
  name varchar(50),
  year smallint,
  primary key(id)
);


insert into albums(name, year)values('Please Please Me', 1963);
insert into albums(name, year)values('With the Beatles', 1963);
insert into albums(name, year)values('A Hard Day\'s Night', 1964);
insert into albums(name, year)values('Beatles for Sale', 1964);
insert into albums(name, year)values('Help!', 1965);
insert into albums(name, year)values('Rubber Soul', 1965);
insert into albums(name, year)values('Revolver', 1966);
insert into albums(name, year)values('Sgt. Pepper\'s Lonly Hears Club Band', 1967);
insert into albums(name, year)values('The Beatles', 1968);
insert into albums(name, year)values('Yellow Submarine', 1969);
insert into albums(name, year)values('Abbey Road', 1969);
insert into albums(name, year)values('Let it Be', 1970);

次は曲のデータ、「ビートルズがやってきた」までです。ぁ〜なつかしいな！！

create table songs(
  id integer not null auto_increment,
  name varchar(50),
  album_id integer not null,
  primary key(id),
  foreign key(album_id) references albums(id)
);

insert into songs(name, album_id)values('I Saw Her Standing There', 1);
insert into songs(name, album_id)values('Anna (Go to Him)', 1);
insert into songs(name, album_id)values('Misery', 1);
insert into songs(name, album_id)values('Boys', 1);
insert into songs(name, album_id)values('Ask Me Why', 1);
insert into songs(name, album_id)values('Please Please Me', 1);
insert into songs(name, album_id)values('Love Me Do', 1);
insert into songs(name, album_id)values('P.S. I Love Yoe', 1);
insert into songs(name, album_id)values('Baby It\'s You', 1);
insert into songs(name, album_id)values('Do You Want to Know a Secret', 1);
insert into songs(name, album_id)values('A Taste of Honey', 1);
insert into songs(name, album_id)values('There\'s a Place', 1);
insert into songs(name, album_id)values('Twist and Shout', 1);

insert into songs(name, album_id)values('It Won\'t Be Long', 2);
insert into songs(name, album_id)values('All I\'ve Got To Do', 2);
insert into songs(name, album_id)values('All My Loving', 2);
insert into songs(name, album_id)values('Don\'t Bother Me', 2);
insert into songs(name, album_id)values('Little Child', 2);
insert into songs(name, album_id)values('Till There Was You', 2);
insert into songs(name, album_id)values('Please Mister Postman', 2);
insert into songs(name, album_id)values('Roll Over Beethoven', 2);
insert into songs(name, album_id)values('Hold Me Tight', 2);
insert into songs(name, album_id)values('You Really Got A Hold On Me', 2);
insert into songs(name, album_id)values('I Wanna Be Your Man', 2);
insert into songs(name, album_id)values('Devil I Her Heart', 2);
insert into songs(name, album_id)values('Not A Second Time', 2);
insert into songs(name, album_id)values('Money', 2);

insert into songs(name, album_id)values('A Hard Day\'s Night', 3);
insert into songs(name, album_id)values('I Should Have Known Better', 3);
insert into songs(name, album_id)values('If I Fell', 3);
insert into songs(name, album_id)values('I\'m Happy Just to Dance With You', 3);
insert into songs(name, album_id)values('And I Love Her', 3);
insert into songs(name, album_id)values('Tell Me Why', 3);
insert into songs(name, album_id)values('Can\'t Buy Me Love', 3);
insert into songs(name, album_id)values('Any Time At All', 3);
insert into songs(name, album_id)values('I\'ll Cry Instead', 3);
insert into songs(name, album_id)values('Things We Said Today', 3);
insert into songs(name, album_id)values('When I Get Home', 3);
insert into songs(name, album_id)values('You Can\'t Do That', 3);
insert into songs(name, album_id)values('I\'ll Be Back', 3);

スクリプトを作成し実行してみます。

require 'rubygems'
require 'active_record'
require 'ambition'

ActiveRecord::Base.establish_connection(
  :adapter => 'mysql',
  :database => 'sampledb',
  :host => 'localhost',
  :username => 'myadmin',
  :password => 'xxxxxx'
)



# テーブルが複数形なのに対してクラスは単数形
class Album < ActiveRecord::Base
end

class Song < ActiveRecord::Base
  belongs_to :album
  def to_s
    return sprintf("%-30s [%s]", name, album.name)
  end
end


puts Song.select{|s| s.album.name =~ 'Please%'}
  #SELECT * FROM songs JOIN album WHERE albums.name LIKE 'Please%'
  # SQL変ですね!!

Song.select{|s| s.album.name =~ 'Please%'}.each{|s| print s, s.album.year, "\n"}
  #I Saw Her Standing There       [Please Please Me]1963
  #Anna (Go to Him)               [Please Please Me]1963
  #Misery                         [Please Please Me]1963
  #Boys                           [Please Please Me]1963
  #Ask Me Why                     [Please Please Me]1963
  #Please Please Me               [Please Please Me]1963
  #Love Me Do                     [Please Please Me]1963
...

to_sで出力されたSQLは変ですが、 DBへはきちんとアクセスしているみたいです???