ActiveRecord使ってみた

## 参考

* [ActiveRecord入門 - Qiita](http://qiita.com/kimioka0/items/8c10e01def23fdbf3aa6)

## 初期database作成

```sql:import.sql 
create table posts(
    id integer primary key,
    title text,
    body text, 
    created_at,
    updated_at
);
```

```bash:投入
sqlite3 blog.db ".read import.sql"
```

```bash:確認
sqlite3 blog.db ".schema posts"
```

## databaseに書き込み

```rb:main.rb
require 'active_record'
ActiveRecord::Base.establish_connection(
    "adapter" =>"sqlite3",
    "database" => "./blog.db"
)
class Post < ActiveRecord::Base
end

post = Post.new(:title => "title1",:body => "hello" )
post.save
p Post.all
```

```bash:blog.dbの中身を確認
sqlite3 blog.db "select * from posts"
```

## 検索

```rb:find
p Post.all  #全取得
p Post.first  #先頭のレコード
p Post.last.title  #最後のレコードのtitle値
p Post.find(2)  #id=2
p Post.find_by_title("title2") #title検索。最初にヒットしたもので返す
p Post.find_by_title_and_id("title2",2) #titleとidで検索
```

```rb:where
p Post.where(:title => "title1", :id => 1)
p Post.where(":title = ? and id = ?", "title1",1)
p Post.where(":title = :title and id = :id",{:title => "title1",:id => 1})
p Post.where("body like ?", "hello%")
p Post.where(:id => 1..3) #1から3を取得
p Post.where(:id => [1,3]) #1と3を取得
```

```rb:whereでscopeを使う
class Post < ActiveRecord::Base
    scope :top3,->{order("created_at").limit(3)}
end
p Post.top3
p Post.order("id desc").limit(3)
```

```rb:sqlを表示
class Post < ActiveRecord::Base
end
Post.logger = Logger.new(STDOUT) #標準出力
Post.logger = Logger.new("query.log") #ファイルに保存
p Post.order("id desc").limit(3)
```

## first_or_create(抽出結果がなかったらレコードの新規作成)

```rb:
p Post.where(:title => "title4").first_or_create
Post.where(:title => "title6").first_or_create do |p|
    p.body = "hello6"
end
```

## update

```rb:
post = Post.find(1)
post.update_attribute(:title,"(new2)title")
post.update_attributes(:title=>"(new2)title",:body=>"")

Post.where(:id => 1..2).update_all(:title =>"",:body => "")
```

## delete

```rb:
Post.where(:id =>1..2).delete_all
Post.find(3).destroy
```

## save時にvalidation

```rb:
class Post < ActiveRecord::Base
    validates :title, :presence => true #必須
    validates :body, :length => {:minimum => 5} #5文字以上
end

post = Post.new(:title => "title",:body => "abc" )
post.save

if !post.save
  p post.errors.messages
end
```

## アソシエーション(関連するデータをまとめて扱う)

```sql:import.sql
create table posts( 
    id integer primary key,
    title text,
    body text,
    created_at,
    updated_at
);
insert into posts(id,title,body) values (1,"title1","本文1");
insert into posts(id,title,body) values (2,"title2","本文2");
insert into posts(id,title,body) values (3,"title3","本文3");


drop table if exists comments;
create table comments( 
    id integer primary key,
    post_id integer,
    title text,
    body text,
    created_at,
    updated_at
);
insert into comments(id,post_id,body) values (1,1,"コメント1");
insert into comments(id,post_id,body) values (2,1,"コメント2");
insert into comments(id,post_id,body) values (3,2,"コメント3");
insert into comments(id,post_id,body) values (4,2,"コメント4");
insert into comments(id,post_id,body) values (5,3,"コメント5");
insert into comments(id,post_id,body) values (6,3,"コメント6");
```

```bash:blog.dbを作成
sqlite3 blog.db ".read import.sql"
```

```rb:main.rb
require 'active_record'
ActiveRecord::Base.establish_connection(
    "adapter" =>"sqlite3",
    "database" => "./blog.db"
)
#postとcommentsの関係が一対多
class Post < ActiveRecord::Base 
    has_many :comment
end
class Comment < ActiveRecord::Base 
    belongs_to :post
end

post = Post.find(1)
post.comment.each do |comment|
    p comment.body
end
```

```bash:実行
$ ruby main.rb 
"コメント1"
"コメント2"
```

### postテーブルから削除するとcommentテーブルからも削除

```diff:main.rb
  class Post < ActiveRecord::Base
-   has_many :comment
+   has_many :comment, :dependent => :destroy
  end
  class Comment < ActiveRecord::Base
    belongs_to :post
  end

  p Post.find(1)
+ Post.find(1).destroy
```

```sh:id=1,2のレコードが消えたことを確認
$ sqlite3 blog.db "select * from comments"
3|2||コメント3||
4|2||コメント4||
5|3||コメント5||
6|3||コメント6||
```