Rails
ActiveRecord
初心者

Active Recordでいずれかの値に一致するデータを取得する(in句)

一つのカラムに対して複数の値でデータを取得したい時に、whereinを使います。

データ確認

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')