Skip to content

Instantly share code, notes, and snippets.

@a2ikm
Created January 29, 2012 08:23
  • Star 7 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save a2ikm/1697867 to your computer and use it in GitHub Desktop.
SELECT系SQLでできることとかActiveRecordでできること - p4dでの資料
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")
]
@a2ikm
Copy link
Author

a2ikm commented Jan 29, 2012

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

@croudsky
Copy link

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

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment