ActiveRecord on Rails - MemoMemo

検索(SELECT)

SELECT * FROM people;

peopleにはPersonオブジェクが格納された配列で返される。

people = Person.find :all

SELECT * FROM people LIMIT 1;

person = Person.find :first

SELECT * FROM people WHERE id = 1;

people = Person.find :all, :conditions=>["id=?",1]

people = Person.find :all, :conditions=>["id=:id",{:id=>1}]

SELECT * FROM people ORDER BY id DESC;

people = Person.find :all, :order=>"id DESC"

SELECT * FROM people LIMIT 10;

people = Person.find :all, :limit=>10

SELECT name AS shimei FROM people;

people = Person.find :all, :select=>"name AS shimei"

SELECT company_id, count(*) AS cnt FROM people GROUP BY company_id;

people = Person.find :all :select=>"id, count(*) AS cnt", :group=>"company_id"

SELECT * FROM people LEFT OUTER JOIN companies ON people.company_id=companies.id;

people = Person.find :all :joins=>"LEFT OUTER JOIN companies ON people.comapny_id=companies.id"

1対1、1対多、多対多の関係設定をしている場合

people = Preson.find :all :include=>:company

直接SQLを入力

Person.find_by_sql ["SELECT * FROM people WHERE id=:id", :id=>1]

Person.find_by_sql [<<-EOF, id=>1]
  SELECT *
  FROM people
  WHERE id=:id
EOF

挿入(INSERT)

INSERT INTO people (name) VALUES ('山田太郎');

A. saveメッソドを実行するまでDBに書き込まれない方法

person = Person.new
person.name = '山田太郎'
person.save

B.即座にDBに書き込む方法

Person.create :name=>'山田太郎'

更新(UPDATE)

UPDATE people SET name = '山田花子' WHERE id = 1;

person = Person.find 1
person.name '山田花子'
person.save

Person.update 1, :name=>4

person = Person.find 1
person.update_attribute :name, '山田花子'

person = Person.find 1
person.update_attributes :name=>'山田花子'

Person.update_all "name = '山田花子'", "id = 1"

UPDATE people SET name = '山田花子'

Person.update_all "name = '山田花子'"

削除(DELETE)

削除には通常destroyメソッドを使用する。deleteメソッドではRailsの整合性チェックを無視して削除できる。

DELETE FROM people WHERE id = 1;

Person.destroy 1

Person.delete 1

person = Person.find 1
person.destroy

person = Person.find 1
person.delete

Person.destroy_all "id=1"

Person.delete_all "id=1"

DELETE FROM people;

Person.destroy_all

Person.delete_all

関係（リレーション）

【１対１】

people		profiles
id	->	id
name		person_id
		job

class Person < ActiveRecord::Base
  has_one :profile
end

class Profile < ActiveRecord::Base
  belongs_to :person
end

【１対多】

companies		people
id	->	id
name		company_id
		name

class Company < ActiveRecord::Base
  has_many :people
end

class Person < ActiveRecord::Base
  belongs_to :company
end

【多対多】

結合テーブルは、内部で利用するだけで表示する必要がない場合（結合テーブルclubs_peopleにid列は不要）：

clubs		clubs_people		people
id	->	club_id	<-	id
name		person_id		name

class Club < ActiveRecord::Base
  has_and_belongs_to_many :people
end

class Person < ActiveRecord::Base
  has_and_belongs_to_many :clubs
end

結合テーブルも、画面に表示する必要がある場合（結合テーブルlinksにid列が必要）：

clubs		links		people
id	->	id	<-	id
name		club_id		name
		person_id

class Club < ActiveRecord::Base
  has_many :links
end

class Person < ActiveRecord::Base
  has_many :links
end

class Link < ActiveRecord::Base
  belongs_to :club
  belongs_to :person
end

上記の両方の性質を持った特殊な利用法（結合テーブルlinksにid列が必要）

clubs		links		people
id	->	id	<-	id
name		club_id		name
		person_id

class Book < ActiveRecord::Base
  has_many :links
  has_many :people, :through => :links
end

class Person < ActiveRecord::Base
  has_many :links
  has_many :clubs, :through => :links
end

class Link < ActiveRecord::Base
  belongs_to :club
  belongs_to :person
end