一つのカラムに対して複数の値でデータを取得したい時に、where
とin
を使います。
データ確認
Table "public.test_users"
Column | Type | Modifiers
------------+-----------------------------+---------------------------------------------------------
id | integer | not null default nextval('test_users_id_seq'::regclass)
name | character varying |
email | character varying |
created_at | timestamp without time zone | not null
updated_at | timestamp without time zone | not null
Indexes:
"test_users_pkey" PRIMARY KEY, btree (id)
> TestUser.all
TestUser Load (0.5ms) SELECT "test_users".* FROM "test_users"
=> [#<TestUser:0x007f968e99a5f8 id: 1, name: "Tina", email: "Tina@co.jp", created_at: Mon, 04 Dec 2017 18:57:03 JST +09:00, updated_at: Mon, 04 Dec 2017 18:57:03 JST +09:00>,
#<TestUser:0x007f968e998f28 id: 2, name: "Rokku", email: "Rokku@co.jp", created_at: Mon, 04 Dec 2017 18:57:31 JST +09:00, updated_at: Mon, 04 Dec 2017 18:57:31 JST +09:00>,
#<TestUser:0x007f968e998d48 id: 3, name: "Edgar", email: "Edgar@co.jp", created_at: Mon, 04 Dec 2017 18:57:50 JST +09:00, updated_at: Mon, 04 Dec 2017 18:57:50 JST +09:00>,
#<TestUser:0x007f968e998af0 id: 4, name: "Masshu", email: "Masshu@co.jp", created_at: Mon, 04 Dec 2017 18:58:14 JST +09:00, updated_at: Mon, 04 Dec 2017 18:58:14 JST +09:00>,
#<TestUser:0x007f968e998848 id: 5, name: "Banan", email: "Banan@co.jp", created_at: Mon, 04 Dec 2017 18:58:47 JST +09:00, updated_at: Mon, 04 Dec 2017 18:58:47 JST +09:00>]
一つのカラムに対してin句
# 配列を渡してやればOK
> TestUser.where(id: [1,2,3])
TestUser Load (0.6ms) SELECT "test_users".* FROM "test_users" WHERE "test_users"."id" IN (1, 2, 3)
=> [#<TestUser:0x007f968ce60ff8 id: 1, name: "Tina", email: "Tina@co.jp", created_at: Mon, 04 Dec 2017 18:57:03 JST +09:00, updated_at: Mon, 04 Dec 2017 18:57:03 JST +09:00>,
#<TestUser:0x007f968ce60e90 id: 2, name: "Rokku", email: "Rokku@co.jp", created_at: Mon, 04 Dec 2017 18:57:31 JST +09:00, updated_at: Mon, 04 Dec 2017 18:57:31 JST +09:00>,
#<TestUser:0x007f968ce60d28 id: 3, name: "Edgar", email: "Edgar@co.jp", created_at: Mon, 04 Dec 2017 18:57:50 JST +09:00, updated_at: Mon, 04 Dec 2017 18:57:50 JST +09:00>]
> TestUser.where(name: ["Tina","Edgar"])
TestUser Load (1.4ms) SELECT "test_users".* FROM "test_users" WHERE "test_users"."name" IN ('Tina', 'Edgar')
=> [#<TestUser:0x007f9688e65e30 id: 1, name: "Tina", email: "Tina@co.jp", created_at: Mon, 04 Dec 2017 18:57:03 JST +09:00, updated_at: Mon, 04 Dec 2017 18:57:03 JST +09:00>,
#<TestUser:0x007f9688e65c00 id: 3, name: "Edgar", email: "Edgar@co.jp", created_at: Mon, 04 Dec 2017 18:57:50 JST +09:00, updated_at: Mon, 04 Dec 2017 18:57:50 JST +09:00>]
あれこそ
%w
使うと配列が簡単に作れる
hoge = %w(1 2 3)
=> ["1", "2", "3"]
文字列の配列と数値の配列だと、キーにする項目の型を勝手に判断してSQLは発行されるようだ
> string = %w(1 2 3)
=> ["1", "2", "3"]
> integer = [1,2,3]
=> [1, 2, 3]
# idをキーにすると数値の配列
> TestUser.where(id: string)
TestUser Load (0.6ms) SELECT "test_users".* FROM "test_users" WHERE "test_users"."id" IN (1, 2, 3)
> TestUser.where(id: integer)
TestUser Load (0.5ms) SELECT "test_users".* FROM "test_users" WHERE "test_users"."id" IN (1, 2, 3)
# nameをキーにすると文字列の配列
> TestUser.where(name: string)
TestUser Load (0.5ms) SELECT "test_users".* FROM "test_users" WHERE "test_users"."name" IN ('1', '2', '3')
> TestUser.where(name: integer)
TestUser Load (0.5ms) SELECT "test_users".* FROM "test_users" WHERE "test_users"."name" IN ('1', '2', '3')