SELECT系SQLでできることとかActiveRecordでできること - p4dでの資料

p4d-20120129.txt

SELECT系SQLでできることとかActiveRecordでできること

# people

* name : 名前（文字列）
* age : 年齢（整数）

| id | name | age |
|  1 | John |  18 |
|  2 |  Ben |  27 |
|  3 | Holy |   8 |


SELECT * FROM people;

| id | name | age |
|  1 | John |  18 |
|  2 |  Ben |  27 |
|  3 | Holy |   8 |



people = Person.all
`SELECT * FROM people;`
#=> 
  [
    Person(name: "John", age: 18),
    Person(name: "Ben",  age: 27),
    Person(name: "Holy", age: 8)
  ]

people[0]
#=> Person(name: "John", age: 18)

people[0].name
#=> "John"


people = Person.order("age DESC").all
#=> 
  [
    Person(name: "Ben",  age: 27),
    Person(name: "John", age: 18),
    Person(name: "Holy", age: 8)
  ]




■ ORDER BY

# DBにお任せ（速い）
people = Person.order("age DESC").all
`SELECT * FROM people ORDER BY age DESC;`

# Rubyでがんばる
people = Person.all.sort_by { |x| x.age }
`SELECT * FROM people;`



■ WHERE

# DBにお任せ（速い）
people = Person.where("age > 20").all
`SELECT * FROM people WHERE age > 20;`
#=> 
  [
    Person(name: "Ben",  age: 27)
  ]

# Rubyでがんばる
people = Person.all.select { |x| x.age > 20 }
`SELECT * FROM people;`



■ LIMIT

people = Person.limit(2).all
`SELECT * FROM people LIMIT 2;`
#=>
  [
    Person(name: "John", age: 18),
    Person(name: "Ben",  age: 27)
  ]


■ LIMIT x ORDER BY
people = Person.limit(2).order("age DESC").all
people = Person.order("age DESC").limit(2).all
`SELECT * FROM people ORDER BY age DESC LIMIT 2;`
#=>
  [
    Person(name: "Ben",  age: 27),
    Person(name: "John", age: 18)
  ]

※ まずORDER BYで並び替えてからLIMITの制限がかかる


■ SQL句の書く順番

* SELECT
* FROM
* JOIN
* WHERE
* GROUP BY
* HAVING
* ORDER BY
* LIMIT

※ 実行される順番とは別


■ 制限をかけてから並び替えるには…？

#1 サブクエリを使う

取得した値をもう一度DB内で操作する（重いらしい）

SELECT t.*
  FROM (SELECT * FROM people LIMIT 10) AS t
  ORDER BY t.age DESC;

people = Person.find_by_sql("
  SELECT t.*
    FROM (SELECT * FROM people LIMIT 10) AS t
    ORDER BY t.age DESC;
")


#2 Rubyでがんばる

LIMITかけてるならレコード数少なくてRubyでも速いんじゃないか？

people = Person.limit(10).all.sort_by { |x| x.age }


■ 別モノをまとめてとってくるには…？

* name : 名前（文字列）
* age : 年齢（整数）

# men

| id | name | age |
|  1 | John |  18 |
|  2 |  Ben |  27 |

# women

| id | name | age |
|  1 | Beth |  30 |

それぞれ取って来てから…
men = Men.all
`SELECT * FROM men;`

women = Women.all
`SELECT * FROM women;`

混ぜる
men + women

でもSQLが2回発行されて効率が悪い…
（SQLはなんだかんだいって遅いので少なくしたい）

# people

似たものはひとつのテーブルにまとめて、
一気に扱えるようにしたほうが効率的

| id | name | age |  sex   |
|  1 | John |  18 |   male |
|  2 |  Ben |  27 |   male |
|  3 | Beth |  30 | female |

用途別にSQLを発行したほうが効率的だし、
できるだけ少ない回数でデータを取り出せるように設計したほうが良い（難しい…）

men = Person.where(:sex => "male).all
women = Person.where(:sex => "female").all




■ JOINでテーブルをつなげる

# posts

| id | title | text | category_id |
|  1 |   A   |  aa  |           1 |
|  2 |   B   |  bb  |           2 |
|  3 |   C   |  cc  |           1 |


# categories

| id |  name   |
|  1 | "movie" |
|  2 | "music" |
|  3 | "food"  |



class Post < ActiveRecord::Base
  belongs_to :category
end

class Category < ActiveRecord::Base
  has_many :posts
end

posts = Post.join(:category).all

SELECT *
  FROM posts
  INNER JOIN categories ON categories.id = posts.category_id;
  
| id | title | text | category_id | id | name  |
|  1 |   A   |  aa  |           1 |  1 | movie |
|  2 |   B   |  bb  |           2 |  2 | music |
|  3 |   C   |  cc  |           1 |  2 | movie |

JOINすることで2つ以上のテーブルをつないで、WHEREで絞り込んだり、
関連するテーブルのデータを取得できる。


posts = Post.join(:category, :account).all
posts = Post.join([:category, { :account => :role }]).
          where(:role => { :name => "admin" }).all

SELECT *
  FROM posts
  INNER JOIN categories ON categories.id = posts.category_id
  INNER JOIN accounts ON account.id = posts.account_id
  INNER JOIN roles ON role.id = accounts.role_id
  WHERE role.name = "admin";


取得するカラム名にASで名前をつけることで、カラム名の重複を避けることができる。

posts = Post.joins(:category).select("posts.*, categories.id AS category_id, categories.name AS category_name")
SELECT posts.*, categories.id AS category_id, categories.name AS category_name
  FROM posts
  INNER JOIN categories ON categories.id = posts.category_id;
  
| id | title | text | category_id | category_id | category_name |
|  1 |   A   | "aa" |           1 |           1 |         movie |
|  2 |   B   | "bb" |           2 |           2 |         music |
|  3 |   C   | "cc" |           1 |           2 |         movie |

posts[0].category_name
#=> "movie"


 


■ 正規化

頻出するデータは別テーブルにする
-> 同じデータを一箇所にまとめることで、変更を容易にする

* postsに対するカテゴリ名（posts.category_name -> categories.nameに切り分ける）
* accountに対する役割名（accounts.role_name -> roles.nameに切り分ける）



■ カテゴリの下位のサブカテゴリとかどうやる？

#1 カテゴリとサブカテゴリを別テーブルにする

# categories

| id |  name |
|  1 | movie |
|  2 | music |

# subcategories

| id | category_id |  name   |
|  1 |           1 | drama   |
|  2 |           1 | mystery |
|  3 |           2 | rock    |

すべてのカテゴリを取得

class Category < AR::Base
  has_many :subcategories
end

class Subcategory < AR::Base
  belongs_to :category
end

categories = Category.all
`SELECT * FROM categories;`

categories.each do |category|
  puts "<li>#{category.name}<ul>"
  category.subcategories.each do |subcategory|
  `SELECT * FROM subcategories WHERE category_id = #{subcategory.category_id};`
  
    puts "<li>#{subcategory.name}</li>"
  end
  puts "</ul></li>"
end

↑の方法だとSQLが1+categories数だけ発行される。
発行回数を減らすにはActiveRecordのincludesを使う。


categories = Category.includes(:subcategories).all
`SELECT * FROM categories;`
`SELECT * FROM subcategories;` 

この時点でcategory個々にsubcategoriesの配列を結びつけてしまう。

categories.each do |category|
  puts "<li>#{category.name}<ul>"
  category.subcategories.each do |subcategory|
    puts "<li>#{subcategory.name}</li>"
  end
  puts "</ul></li>"
end


#2 カテゴリからカテゴリを参照する

子、孫、ひ孫カテゴリまで自由につくれる。
ただ再帰的な処理が必要なのが難しい。

# categories

| id | parent |   name  |
|  1 |   NULL |   movie |
|  2 |   NULL |   music |
|  3 |      1 |   drama |
|  4 |      1 | mystery |
|  4 |      2 |    rock |

すべてのカテゴリを取得
Category.all

親カテゴリのみを取得
categories = Category.where(:parent => nil).all
`SELECT * FROM categories WHERE parent IS NULL;`
#=>
  [
    Category(id: 1, parent: nil, name: "movie"),
    Category(id: 2, parent: nil, name: "music")
  ]


movie = categories[0]
#=> Category(id: 1, parent: nil, name: "movie"),

# 子カテゴリを取得する
children = Category.where(:parent => movie.id).all
#=>
  [
    Category(id: 3, parent: 1, name: "drama"),
    Category(id: 4, parent: 1, name: "mystery")
  ]

間違っている点などがあればご指摘いただければと思います><

ARはjoinじゃなくjoinsでは？
posts = Post.join(:category).all　→　posts = Post.joins(:category).all など。

あと、
posts = Post.join(:category).allの結果はPostの内容しか取れない気がする…。