Active Record入門メモ

Active Record 入門

参考元

概要

• Ruby on RailsでRubyのオブジェクトのようにdbを扱える
• OR Mapper (Object <> RDB)
  ドキュメント（非公式）
  https://railsguides.jp/active_record_basics.html

テーブルを作る

• データの構造（スキーマ）を作る
• ドメイン固有言語　マイグレーション

このときテーブル名は、小文字で、複数形にする（テーブルは複数のデータを保有してるから）
Active Recordは大文字小文字の判別に非常に敏感。

sqlite3 でsqlの流し込みをしようぜ

まずはsqlファイルを作る

import.sql

create table users(
id integer primary key,
name text,
age integer,
created_at,
updated_at
);

[vagrant@localhost activerecord_lessons]$ sqlite3 myapp.db < import.sql //流し込み
[vagrant@localhost activerecord_lessons]$ sqlite3 myapp.db 
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema
CREATE TABLE users( --テーブル名は必ず小文字
    id integer primary key,
    name text,
    age integer,
    created_at,
    updated_at
);
sqlite> .exit

コメントまで取り込まれてしまった…

設定

main.rb

require 'active_record'
require 'pp'

Time.zone_default = Time.find_zone! 'Tokyo'
ActiveRecord::Base.default_timezone =:local

ActiveRecord::Base.establish_connection(
    "adapter" => "sqlite3",
    "database" => "./myapp.db” 
)
#初期設定

class User < ActiveRecord::Base #テーブル作成は、これを書くだけ。超簡単。
end

レコード挿入

• Userクラスのレコードを、userインスタンスとして扱うことができる

Rubyファイルからインスタンスとして、レコードを扱う

main.rb

require 'active_support/all' #これがないとTimezoneの設定ができない
require 'active_record' #active_recordの読み込み
require 'pp'

#タイムゾーンを設定
Time.zone_default = Time.find_zone! 'Tokyo'
ActiveRecord::Base.default_timezone =:local

#データベースの接続設定
ActiveRecord::Base.establish_connection(
"adapter" => "sqlite3",
"database" => "./myapp.db"
)

class User < ActiveRecord::Base
end

user = User.new
user.name ="tanaka"
user.age = 23
user.save #レコードを保存

#ハッシュで値を渡す
user = User.new(name: "hayashi", age:25)
user.save

User.create(name: "hoshi",age: 22)

[vagrant@localhost activerecord_lessons]$ ruby main.rb //rubyの実行
[vagrant@localhost activerecord_lessons]$ sqlite3 myapp.db //sqlを見てみよう
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from users;
1|tanaka|23|2018-08-29 17:24:28.990856|2018-08-29 17:24:28.990856
2|hayashi|25|2018-08-29 17:24:29.014230|2018-08-29 17:24:29.014230
3|hoshi|22|2018-08-29 17:24:29.019802|2018-08-29 17:24:29.019802
4|tanaka|23|2018-08-30 02:25:02.047396|2018-08-30 02:25:02.047396
5|hayashi|25|2018-08-30 02:25:02.060214|2018-08-30 02:25:02.060214
6|hoshi|22|2018-08-30 02:25:02.065049|2018-08-30 02:25:02.065049
sqlite> 

2回入れてしまったのは内緒な。　消した

main.rb

user1 = User.find_by(id:4);
user2 = User.find_by(id:5);
user3 = User.find_by(id:6);
user1.destroy;
user2.destroy;
user3.destroy;

Log出力

• SELECT以外のデータをいじる3つの命令をトランザクションという（ActiveRecordでは？他では全然意味が違った）

[vagrant@localhost activerecord_lessons]$ ruby main.rb
D, [2018-08-30T02:49:10.314531 #22954] DEBUG -- :    (0.7ms)  begin transaction
D, [2018-08-30T02:49:10.322429 #22954] DEBUG -- :   User Create (1.0ms)  INSERT INTO "users" ("name", "age", "created_at", "updated_at") VALUES (?, ?, ?, ?)  [["name", "mochizuki"], ["age", 18], ["created_at", "2018-08-30 02:49:10.315379"], ["updated_at", "2018-08-30 02:49:10.315379"]]
D, [2018-08-30T02:49:10.343394 #22954] DEBUG -- :    (19.8ms)  commit transaction
[vagrant@localhost activerecord_lessons]$ 

抽出

• pretty print gemを使って見やすく取得してみる

ruby.rb

class User < ActiveRecord::Base
end

User.delete_all #初期化
User.create(name: "gorilla",age: 18)
User.create(name: "giraf",age: 53)
User.create(name: "elephant",age: 50)
User.create(name: "dolphin",age: 23)
User.create(name: "cat",age: 4)
User.create(name: "turtle",age: 10000)

pp User.all #表示しやすく

pp User.select("id, name, age").first(3) #最初の三件
pp User.select("id,name,age").last #最後一件

[vagrant@localhost activerecord_lessons]$ ruby main.rb 
D, [2018-08-30T03:39:23.726743 #23297] DEBUG -- :   User Destroy (17.4ms)  DELETE FROM "users"
D, [2018-08-30T03:39:23.735139 #23297] DEBUG -- :    (0.1ms)  begin transaction
D, [2018-08-30T03:39:23.736869 #23297] DEBUG -- :   User Create (0.6ms)  INSERT INTO "users" ("name", "age", "created_at", "updated_at") VALUES (?, ?, ?, ?)  [["name", "gorilla"], ["age", 18], ["created_at", "2018-08-30 03:39:23.735336"], ["updated_at", "2018-08-30 03:39:23.735336"]]
D, [2018-08-30T03:39:23.744838 #23297] DEBUG -- :    (4.6ms)  commit transaction
D, [2018-08-30T03:39:23.745756 #23297] DEBUG -- :    (0.1ms)  begin transaction
D, [2018-08-30T03:39:23.747541 #23297] DEBUG -- :   User Create (0.4ms)  INSERT INTO "users" ("name", "age", "created_at", "updated_at") VALUES (?, ?, ?, ?)  [["name", "giraf"], ["age", 53], ["created_at", "2018-08-30 03:39:23.746012"], ["updated_at", "2018-08-30 03:39:23.746012"]]
D, [2018-08-30T03:39:23.751554 #23297] DEBUG -- :    (3.7ms)  commit transaction
D, [2018-08-30T03:39:23.751944 #23297] DEBUG -- :    (0.1ms)  begin transaction
D, [2018-08-30T03:39:23.753472 #23297] DEBUG -- :   User Create (0.4ms)  INSERT INTO "users" ("name", "age", "created_at", "updated_at") VALUES (?, ?, ?, ?)  [["name", "elephant"], ["age", 50], ["created_at", "2018-08-30 03:39:23.752055"], ["updated_at", "2018-08-30 03:39:23.752055"]]
D, [2018-08-30T03:39:23.757547 #23297] DEBUG -- :    (3.8ms)  commit transaction
D, [2018-08-30T03:39:23.757938 #23297] DEBUG -- :    (0.1ms)  begin transaction
D, [2018-08-30T03:39:23.758684 #23297] DEBUG -- :   User Create (0.2ms)  INSERT INTO "users" ("name", "age", "created_at", "updated_at") VALUES (?, ?, ?, ?)  [["name", "dolphin"], ["age", 23], ["created_at", "2018-08-30 03:39:23.758049"], ["updated_at", "2018-08-30 03:39:23.758049"]]
D, [2018-08-30T03:39:23.763548 #23297] DEBUG -- :    (4.6ms)  commit transaction
D, [2018-08-30T03:39:23.764136 #23297] DEBUG -- :    (0.1ms)  begin transaction
D, [2018-08-30T03:39:23.765817 #23297] DEBUG -- :   User Create (0.3ms)  INSERT INTO "users" ("name", "age", "created_at", "updated_at") VALUES (?, ?, ?, ?)  [["name", "cat"], ["age", 4], ["created_at", "2018-08-30 03:39:23.764826"], ["updated_at", "2018-08-30 03:39:23.764826"]]
D, [2018-08-30T03:39:23.770231 #23297] DEBUG -- :    (4.1ms)  commit transaction
D, [2018-08-30T03:39:23.771161 #23297] DEBUG -- :    (0.2ms)  begin transaction
D, [2018-08-30T03:39:23.772713 #23297] DEBUG -- :   User Create (0.4ms)  INSERT INTO "users" ("name", "age", "created_at", "updated_at") VALUES (?, ?, ?, ?)  [["name", "turtle"], ["age", 10000], ["created_at", "2018-08-30 03:39:23.771417"], ["updated_at", "2018-08-30 03:39:23.771417"]]
D, [2018-08-30T03:39:23.777426 #23297] DEBUG -- :    (4.4ms)  commit transaction
D, [2018-08-30T03:39:23.778689 #23297] DEBUG -- :   User Load (0.2ms)  SELECT "users".* FROM "users"
[#<User:0x007f89ded52568
  id: 1,
  name: "gorilla",
  age: 18,
  created_at: "2018-08-30 03:39:23.735336",
  updated_at: "2018-08-30 03:39:23.735336">,
 #<User:0x007f89ded45a98
  id: 2,
  name: "giraf",
  age: 53,
  created_at: "2018-08-30 03:39:23.746012",
  updated_at: "2018-08-30 03:39:23.746012">,
 #<User:0x007f89ded45958
  id: 3,
  name: "elephant",
  age: 50,
  created_at: "2018-08-30 03:39:23.752055",
  updated_at: "2018-08-30 03:39:23.752055">,
 #<User:0x007f89ded457a0
  id: 4,
  name: "dolphin",
  age: 23,
  created_at: "2018-08-30 03:39:23.758049",
  updated_at: "2018-08-30 03:39:23.758049">,
 #<User:0x007f89ded45638
  id: 5,
  name: "cat",
  age: 4,
  created_at: "2018-08-30 03:39:23.764826",
  updated_at: "2018-08-30 03:39:23.764826">,
 #<User:0x007f89ded454a8
  id: 6,
  name: "turtle",
  age: 10000,
  created_at: "2018-08-30 03:39:23.771417",
  updated_at: "2018-08-30 03:39:23.771417">]
D, [2018-08-30T03:39:23.784054 #23297] DEBUG -- :   User Load (0.2ms)  SELECT  id, name, age FROM "users" ORDER BY "users"."id" ASC LIMIT ?  [["LIMIT", 3]]
[#<User:0x007f89ded24f00 id: 1, name: "gorilla", age: 18>,
 #<User:0x007f89ded24dc0 id: 2, name: "giraf", age: 53>,
 #<User:0x007f89ded24c58 id: 3, name: "elephant", age: 50>]
D, [2018-08-30T03:39:23.785203 #23297] DEBUG -- :   User Load (0.1ms)  SELECT  id,name,age FROM "users" ORDER BY "users"."id" DESC LIMIT ?  [["LIMIT", 1]]
#<User:0x007f89ded0f5b0 id: 6, name: "turtle", age: 10000>

条件指定

• findする

main.rb

pp User.select("id,name,age").find(3) #id = 3のものだけ
pp User.select("id,name,age").find_by(name: "gorilla") #名前がゴリラのものだけ
pp User.select("id,name,age").find_by_name! "dog" #存在しないものにはエラーを返す

[vagrant@localhost activerecord_lessons]$ ruby main.rb 
D, [2018-08-30T03:45:08.817304 #23478] DEBUG -- :   User Load (0.2ms)  SELECT  id,name,age FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 3], ["LIMIT", 1]]
#<User:0x007fe7792388a0 id: 3, name: "elephant", age: 50>
D, [2018-08-30T03:45:08.826433 #23478] DEBUG -- :   User Load (0.2ms)  SELECT  id,name,age FROM "users" WHERE "users"."name" = ? LIMIT ?  [["name", "gorilla"], ["LIMIT", 1]]
#<User:0x007fe779220200 id: 1, name: "gorilla", age: 18>
[vagrant@localhost activerecord_lessons]$ 

エラー部分

/home/vagrant/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.2.1/lib/active_record/core.rb:216:in `find_by!': Couldn't find User (ActiveRecord::RecordNotFound)
 	from /home/vagrant/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.2.1/lib/active_record/dynamic_matchers.rb:66:in `find_by_name!'
 	from /home/vagrant/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.2.1/lib/active_record/dynamic_matchers.rb:20:in `method_missing'
 	from /home/vagrant/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.2.1/lib/active_record/relation/delegation.rb:84:in `public_send'
 	from /home/vagrant/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.2.1/lib/active_record/relation/delegation.rb:84:in `block in method_missing'
 	from /home/vagrant/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.2.1/lib/active_record/relation.rb:281:in `scoping'
 	from /home/vagrant/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.2.1/lib/active_record/relation/delegation.rb:84:in `method_missing'
 	from main.rb:34:in `<main>'
[vagrant@localhost activerecord_lessons]$ 

whereする

main.rb

#where検索
User.select("id, name, age").where("age >= 20 and age < 30") #and検索
User.select("id, name, age").where("age <= 20 or age >= 30") #or検索
pp User.select("id, name, age").where.not(id: 3) #not検索

プレースホルダー

値を変数で管理することもある
その時絶対にやってはいけないことがある
min = 20
max = 30

main.rb

#where検索
User.select("id, name, age").where("age >= #{min} and age < #{max}") #and検索

• 値を直接文字列に入れてはいけない。悪意のあるコードが紛れ込む可能性がある。
  これではなく、プレースホルダーを使う

min = 20
max = 30

main.rb

#where検索
User.select("id, name, age").where("age >= ? and age < ?",min,max) #?に代入したい値を埋め込む

？の代わりに判読性を高めるためハッシュを使うことも
#？の数が増えてきたらハッシュで渡す

pp User.select("id, name, age").where("age >= :min and age < :max",{min: min, max:max}) #and検索

like文

#特定の文字列で始まる or 終わるものを返す
pp User.select("id,name,age").where("age >= ?","%n")

ORDER句

#特定の文字列で始まる or 終わるものを返す
pp User.select("id,name,age").order(:age)#昇順
pp User.select("id,name,age").order(age: :desc)#逆順

limit offset

pp User.select("id, name, age").order(:age).limit(3).offset(1)

抽出条件の登録

• 長くなってきた抽出条件は変数として管理したい
• クラスにまとめておけば良い

user.rb

メソッド部分にまとめる
class User < ActiveRecord::Base
#class method
    def self.top3 #selfを忘れずに
        select("id, name, age").order(:age).limit(3).offset(1)
    end
end

main.rb

pp User.top3 #呼び出す

スコープにまとめる

user.rb

class User < ActiveRecord::Base
#scopeで、引数を与える
scope :top, -> (num1,num2) { select("id, name, age").order(:age).limit(num1).offset(num2)}
end

main.rb

pp User.top(3,1)

スコープとは、クエリをメソッドのように定義できるRails固有機能

FIND_OR_CREATE_BY

• 抽出するが、なかったら作る

存在している場合

main.rb

user = User.find_or_create_by(name: "dolphin")

pp user

 [vagrant@localhost activerecord_lessons]$ ruby main.rb 
D, [2018-08-30T05:04:34.997278 #24697] DEBUG -- :   User Load (0.2ms)  SELECT  "users".* FROM "users" WHERE "users"."name" = ? LIMIT ?  [["name", "dolphin"], ["LIMIT", 1]]
#<User:0x007f875ebf65e8
 id: 4,
 name: "dolphin",
 age: 23,
 created_at: "2018-08-30 03:39:23.758049",
 updated_at: "2018-08-30 03:39:23.758049">
[vagrant@localhost activerecord_lessons]$ 

存在しない場合

main:rb

user = User.find_or_create_by(name: “dog")

pp user

 [2018-08-30T05:05:51.927273 #24767] DEBUG -- :   User Load (0.3ms)  SELECT  "users".* FROM "users" WHERE "users"."name" = ? LIMIT ?  [["name", "dog"], ["LIMIT", 1]]
D, [2018-08-30T05:05:51.936406 #24767] DEBUG -- :    (0.1ms)  begin transaction
D, [2018-08-30T05:05:51.938534 #24767] DEBUG -- :   User Create (0.5ms)  INSERT INTO "users" ("name", "created_at", "updated_at") VALUES (?, ?, ?)  [["name", "dog"], ["created_at", "2018-08-30 05:05:51.936600"], ["updated_at", "2018-08-30 05:05:51.936600"]]
D, [2018-08-30T05:05:51.945275 #24767] DEBUG -- :    (6.4ms)  commit transaction
#<User:0x007f596a9931b0
 id: 7,
 name: "dog",
 age: nil,
 created_at: 2018-08-30 05:05:51 +0900,
 updated_at: 2018-08-30 05:05:51 +0900>
[vagrant@localhost activerecord_lessons]$ 

しかしageはnilのままなので、他の値も埋める
ブロックを使用

user = User.find_or_create_by(name: "rat") do |u| #ねずみさんは存在しない
    u.age = 6 #新しく作るなら、6歳でよろしく
end

pp user

UPDATE

main.rb

User.where("age >= 20").update_all("age = age + 2")
#20歳以上のものを2歳追加

User.where(name: "elephant").update(age: 60)

pp User.select("id, name, age").all

データ削除

• delete 単一　早い
• destroy 高機能　遅い

User.delete(2) #id =2 を消す

• destroy.all
  全件削除
  Validation

レコード更新や挿入にルールをつけよう

user.rb

class User < ActiveRecord::Base
validates :name,:age, presence: true #空っぽダメよ
validates :name, length: {minimum : 3} #最低３文字よ
end

user = User.new(name: nil, age:nil)
#守ってないユーザ登録
user.save

当然エラーが出る

[vagrant@localhost activerecord_lessons]$ ruby main.rb 
main.rb:23: syntax error, unexpected ':', expecting =>
    validates :name, length: {minimum : 3} #最低３文字よ
                                       ^
main.rb:27: syntax error, unexpected end-of-input, expecting keyword_end
#守ってないユーザ登録
                               ^
[vagrant@localhost activerecord_lessons]$ 

エラーメッセージを取得するにはif文

main.rb

if !user.save
pp user.errors.messages
end

Callback

• 特定の処理の前後に処理をかます

user.rb

class User < ActiveRecord::Base
#before_destoryとafter_destroyはActive Recordで定義済
before_destroy :print_before_msg #まずはフィールドを設定
after_destroy :print_after_msg

protected #メソッドとして設定
    def print_before_msg
        puts "#{self.name} will be deleted"
    end
    def print_after_msg
        puts "#{self.name} deleted"
    end
end

#callback
#自動処理を考慮するのはdestroy
#destroyの前後に↑２つが呼び出される
User.where("age >=20" ).destroy_all

[vagrant@localhost activerecord_lessons]$ ruby main.rb 
D, [2018-08-30T05:44:24.877824 #25643] DEBUG -- :   User Load (1.2ms)  SELECT "users".* FROM "users" WHERE (age >=20)
D, [2018-08-30T05:44:24.886564 #25643] DEBUG -- :    (0.1ms)  begin transaction
elephant will be deleted
D, [2018-08-30T05:44:24.894354 #25643] DEBUG -- :   User Destroy (0.5ms)  DELETE FROM "users" WHERE "users"."id" = ?  [["id", 3]]
elephant deleted
D, [2018-08-30T05:44:24.907588 #25643] DEBUG -- :    (12.8ms)  commit transaction
D, [2018-08-30T05:44:24.908008 #25643] DEBUG -- :    (0.1ms)  begin transaction
dolphin will be deleted
D, [2018-08-30T05:44:24.909248 #25643] DEBUG -- :   User Destroy (0.7ms)  DELETE FROM "users" WHERE "users"."id" = ?  [["id", 4]]
dolphin deleted
D, [2018-08-30T05:44:24.914439 #25643] DEBUG -- :    (4.7ms)  commit transaction
D, [2018-08-30T05:44:24.914784 #25643] DEBUG -- :    (0.1ms)  begin transaction
turtle will be deleted
D, [2018-08-30T05:44:24.915529 #25643] DEBUG -- :   User Destroy (0.3ms)  DELETE FROM "users" WHERE "users"."id" = ?  [["id", 6]]
turtle deleted
D, [2018-08-30T05:44:24.919651 #25643] DEBUG -- :    (3.9ms)  commit transaction
[vagrant@localhost activerecord_lessons]$ 

Association

• 複数のテーブルやオブジェクトを関連づける
• →INNER JOINのことか？
• 全然違った。交差して取得できる。

まずはコメントテーブルを作る
ただし、すでにUsersテーブルは存在するので、存在していたらDropするようにする

sql;import.sql

drop table if exists users;
create table users(
    id integer primary key,
    name text,
    age integer,
    created_at,
    updated_at
);

drop table if exists comments;
create table comments(
    id integer primary key,
    user_id integer,
    body text,
    created_at,
    updated_at
);

comments = Comment.includes(:user).all
comments.each do |c|
  puts "#{c.body} by #{c.user.name}"
end

[vagrant@localhost activerecord_lessons]$ ruby main.rb 
D, [2018-08-30T08:50:44.739292 #27703] DEBUG -- :   Comment Load (0.9ms)  SELECT "comments".* FROM "comments"
D, [2018-08-30T08:50:44.756943 #27703] DEBUG -- :   User Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" IN (?, ?)  [["id", 1], ["id", 2]]
hello-1 by tanaka
hello-2 by tanaka
hello-1 by takahashi
[vagrant@localhost activerecord_lessons]$ 

関連情報を削除

• ユーザーを削除したらコメントも削除

user.rb

class User < ActiveRecord::Base
    has_many :comments, dependent: :destroy
    #userが削除された時関連オブジェクトも削除する
end

↓

main.rb

User.find(1).destroy
pp Comment.select("id","user_id","body").all

→消える