Railsでいろいろ検索してみる

Ruby

seedで初期データを追加してみるの続き
（RailsのMySQLの環境にアクセスできるようにしておくも済ませておく）

Railsでいろいろ検索してみる。


現在、Userのテーブルには以下のデータが入っている状態。

+----+--------+-------+------+------------+---------------------+---------------------+
| id | name   | point | flag | day        | created_at          | updated_at          |
+----+--------+-------+------+------------+---------------------+---------------------+
|  1 | 佐藤   |   100 |    1 | 2001-01-01 | 2014-01-12 07:09:56 | 2014-01-12 07:09:56 |
|  2 | 鈴木   |   200 |    0 | 2002-02-02 | 2014-01-12 07:09:56 | 2014-01-12 07:09:56 |
|  3 | 高橋   |   300 |    1 | 2003-03-03 | 2014-01-12 07:09:56 | 2014-01-12 07:09:56 |
|  4 | 田中   |   400 |    0 | 2004-04-04 | 2014-01-12 07:09:56 | 2014-01-12 07:09:56 |
|  5 | 伊藤   |   500 |    1 | 2005-05-05 | 2014-01-12 07:09:56 | 2014-01-12 07:09:56 |
+----+--------+-------+------+------------+---------------------+---------------------+

検索した結果は、下記のような感じで、jsonで画面に出力して確認する。

class SampleController < ApplicationController
  def index
    results = User.all()
    render :json => results
  end
end

以後、User.・・・の部分だけ載せていく。

all

全件取得する。

User.all()

SQL：

SELECT `users`.* FROM `users`

結果：

[
{"id":1,"name":"佐藤","point":100,"flag":true,"day":"2001-01-01","created_at":"2014-01-12T07:09:56.000Z","updated_at":"2014-01-12T07:09:56.000Z"},
{"id":2,"name":"鈴木","point":200,"flag":false,"day":"2002-02-02","created_at":"2014-01-12T07:09:56.000Z","updated_at":"2014-01-12T07:09:56.000Z"},
{"id":3,"name":"高橋","point":300,"flag":true,"day":"2003-03-03","created_at":"2014-01-12T07:09:56.000Z","updated_at":"2014-01-12T07:09:56.000Z"},
{"id":4,"name":"田中","point":400,"flag":false,"day":"2004-04-04","created_at":"2014-01-12T07:09:56.000Z","updated_at":"2014-01-12T07:09:56.000Z"},
{"id":5,"name":"伊藤","point":500,"flag":true,"day":"2005-05-05","created_at":"2014-01-12T07:09:56.000Z","updated_at":"2014-01-12T07:09:56.000Z"}
]

※実際は画面に一行で表示されますが、見やすいように改行してます。

first

一件目を取得する。

User.first()

SQL：

SELECT `users`.* FROM `users` ORDER BY `users`.`id` ASC LIMIT 1

結果：

{"id":1,"name":"佐藤","point":100,"flag":true,"day":"2001-01-01","created_at":"2014-01-12T07:09:56.000Z","updated_at":"2014-01-12T07:09:56.000Z"}

where

条件を指定する。

User.where(:point => [100, 200, 300], :flag => true)

SQL：

SELECT `users`.* FROM `users` WHERE `users`.`point` IN (100, 200, 300) AND `users`.`flag` = 1

結果：

[
{"id":1,"name":"佐藤","point":100,"flag":true,"day":"2001-01-01","created_at":"2014-01-12T07:09:56.000Z","updated_at":"2014-01-12T07:09:56.000Z"},
{"id":3,"name":"高橋","point":300,"flag":true,"day":"2003-03-03","created_at":"2014-01-12T07:09:56.000Z","updated_at":"2014-01-12T07:09:56.000Z"}
]

比較演算子で条件を指定する。

User.where('point < 500')

SQL：

SELECT `users`.* FROM `users` WHERE (point < 500)

結果：

[
{"id":1,"name":"佐藤","point":100,"flag":true,"day":"2001-01-01","created_at":"2014-01-12T07:09:56.000Z","updated_at":"2014-01-12T07:09:56.000Z"},
{"id":2,"name":"鈴木","point":200,"flag":false,"day":"2002-02-02","created_at":"2014-01-12T07:09:56.000Z","updated_at":"2014-01-12T07:09:56.000Z"},
{"id":3,"name":"高橋","point":300,"flag":true,"day":"2003-03-03","created_at":"2014-01-12T07:09:56.000Z","updated_at":"2014-01-12T07:09:56.000Z"},
{"id":4,"name":"田中","point":400,"flag":false,"day":"2004-04-04","created_at":"2014-01-12T07:09:56.000Z","updated_at":"2014-01-12T07:09:56.000Z"}
]

select

取得するカラムを指定する。

User.select(:id, :name)

SQL：

SELECT id, name FROM `users`

結果：

[
{"id":1,"name":"佐藤"},
{"id":2,"name":"鈴木"},
{"id":3,"name":"高橋"},
{"id":4,"name":"田中"},
{"id":5,"name":"伊藤"}
]

order

順序を変更する。

User.order('id desc')

SQL：

SELECT `users`.* FROM `users` ORDER BY id desc

結果：

[
{"id":5,"name":"伊藤","point":500,"flag":true,"day":"2005-05-05","created_at":"2014-01-12T07:09:56.000Z","updated_at":"2014-01-12T07:09:56.000Z"},
{"id":4,"name":"田中","point":400,"flag":false,"day":"2004-04-04","created_at":"2014-01-12T07:09:56.000Z","updated_at":"2014-01-12T07:09:56.000Z"},
{"id":3,"name":"高橋","point":300,"flag":true,"day":"2003-03-03","created_at":"2014-01-12T07:09:56.000Z","updated_at":"2014-01-12T07:09:56.000Z"},
{"id":2,"name":"鈴木","point":200,"flag":false,"day":"2002-02-02","created_at":"2014-01-12T07:09:56.000Z","updated_at":"2014-01-12T07:09:56.000Z"},
{"id":1,"name":"佐藤","point":100,"flag":true,"day":"2001-01-01","created_at":"2014-01-12T07:09:56.000Z","updated_at":"2014-01-12T07:09:56.000Z"}
]

limit

検索結果を絞り込む。

User.limit(2)

SQL：

SELECT `users`.* FROM `users` LIMIT 2

結果：

[
{"id":1,"name":"佐藤","point":100,"flag":true,"day":"2001-01-01","created_at":"2014-01-12T07:09:56.000Z","updated_at":"2014-01-12T07:09:56.000Z"},
{"id":2,"name":"鈴木","point":200,"flag":false,"day":"2002-02-02","created_at":"2014-01-12T07:09:56.000Z","updated_at":"2014-01-12T07:09:56.000Z"}
]

まとめて指定する

まとめて指定する。

User.select('id, name').where('point < 500').order('id desc').limit(2)

SQL：

SELECT id, name FROM `users` WHERE (point < 500) ORDER BY id desc LIMIT 2

結果：

[
{"id":4,"name":"田中"},
{"id":3,"name":"高橋"}
]

count

件数を取得する。

User.count()

SQL：

SELECT COUNT(*) FROM `users`

結果：

5

maximum

最大値を取得する。
（最小値はminimumで取得できる）

User.maximum(:point)

SQL：

SELECT MAX(`users`.`point`) AS max_id FROM `users`

結果：

500

sum

合計値を取得する。

User.sum(:point)

SQL：

SELECT SUM(`users`.`point`) AS sum_id FROM `users`

結果：

1500

group

group byする。

User.group('flag').count()

SQL：

SELECT COUNT(*) AS count_all, flag AS flag FROM `users` GROUP BY flag

結果：

{"false":2,"true":3}