概要
Ruby on Rails で DB から重複しているレコードを取得したい時がありました。
MySQL で重複しているレコードを取得する方法は分かるのですが、Ruby on Rails で同じ処理を書く場合、どうしたら良いのか分からなかったので調べて見ました。
MySQL で書く場合と、Ruby on Rails で書く場合の 2 パターン用意しています。
はじめに
今回利用するテーブルの中身を確認します。
ユーザ名 aaa が 3 件重複、bbb が 2 件重複、ccc が重複無しとなっています。
mysql> select * from users;
+----+------+---------------------+---------------------+------------+
| id | name | created_at | updated_at | deleted_at |
+----+------+---------------------+---------------------+------------+
| 1 | aaa | 2017-10-21 06:30:06 | 2017-10-21 06:30:06 | NULL |
| 2 | aaa | 2017-10-21 06:30:07 | 2017-10-21 06:30:07 | NULL |
| 3 | aaa | 2017-10-21 06:30:09 | 2017-10-21 06:30:09 | NULL |
| 4 | bbb | 2017-10-21 06:30:12 | 2017-10-21 06:30:12 | NULL |
| 5 | bbb | 2017-10-21 06:30:13 | 2017-10-21 06:30:13 | NULL |
| 6 | ccc | 2017-10-21 06:30:16 | 2017-10-21 06:30:16 | NULL |
+----+------+---------------------+---------------------+------------+
6 rows in set (0.00 sec)重複しているレコードを取得
これは単に重複しているレコードを知りたい場合に利用する事が多いです。
今回はユーザー名 aaa と bbb が重複している名前だと言う事だけが分かります。
MySQL の場合
group by 句 と having 句を利用することで、重複しているレコードを取得することができます。
mysql> select * from users group by name having count(*) >= 2;
+----+------+---------------------+---------------------+------------+
| id | name | created_at | updated_at | deleted_at |
+----+------+---------------------+---------------------+------------+
| 1 | aaa | 2017-10-21 06:30:06 | 2017-10-21 06:30:06 | NULL |
| 4 | bbb | 2017-10-21 06:30:12 | 2017-10-21 06:30:12 | NULL |
+----+------+---------------------+---------------------+------------+
2 rows in set (0.00 sec)
Ruby on Rails の場合
Ruby on Rails でも group と having と言う、MySQL と似たようなメソッドがあることが分かりました。
irb(main):001:0> User.group(:name).having('count(*) >= 2')
=> #<ActiveRecord::Relation [
#<User id: 1, name: "aaa", created_at: "2017-10-21 06:30:06", updated_at: "2017-10-21 06:30:06", deleted_at: nil>,
#<User id: 4, name: "bbb", created_at: "2017-10-21 06:30:12", updated_at: "2017-10-21 06:30:12", deleted_at: nil>]>重複しているレコードを全て取得
重複している全てのレコードを取得することができます。
この結果からユーザ名 aaa が 3 回重複、bbb が 2 回重複していることも分かります。
MySQL の場合
先ほどの sql 文をサブクエリで扱い、where in 句でそれと一致するユーザ名を取得しています。
mysql> select * from users where name in (
-> select name from users group by name having count(*) >= 2);
+----+------+---------------------+---------------------+------------+
| id | name | created_at | updated_at | deleted_at |
+----+------+---------------------+---------------------+------------+
| 1 | aaa | 2017-10-21 06:30:06 | 2017-10-21 06:30:06 | NULL |
| 2 | aaa | 2017-10-21 06:30:07 | 2017-10-21 06:30:07 | NULL |
| 3 | aaa | 2017-10-21 06:30:09 | 2017-10-21 06:30:09 | NULL |
| 4 | bbb | 2017-10-21 06:30:12 | 2017-10-21 06:30:12 | NULL |
| 5 | bbb | 2017-10-21 06:30:13 | 2017-10-21 06:30:13 | NULL |
+----+------+---------------------+---------------------+------------+
5 rows in set (0.00 sec)Ruby on Rails の場合
先ほどのワンライナーに pluck( :name ) を混ぜてユーザ名だけのリストを作ります。
ユーザ名だけのリストを where 文の引数に使用して条件検索を行います。
irb(main):001:0> duplicate_user_names = User.group(:name).having('count(*) >= 2').pluck(:name)
=> ["aaa", "bbb"]
irb(main):002:0> User.where(name: duplicate_user_names)
=> #<ActiveRecord::Relation [
#<User id: 1, name: "aaa", created_at: "2017-10-21 06:30:06", updated_at: "2017-10-21 06:30:06", deleted_at: nil>,
#<User id: 2, name: "aaa", created_at: "2017-10-21 06:30:07", updated_at: "2017-10-21 06:30:07", deleted_at: nil>,
#<User id: 3, name: "aaa", created_at: "2017-10-21 06:30:09", updated_at: "2017-10-21 06:30:09", deleted_at: nil>,
#<User id: 4, name: "bbb", created_at: "2017-10-21 06:30:12", updated_at: "2017-10-21 06:30:12", deleted_at: nil>,
#<User id: 5, name: "bbb", created_at: "2017-10-21 06:30:13", updated_at: "2017-10-21 06:30:13", deleted_at: nil>]>
おまけ
条件を満たさない重複レコードを削除する
重複しているレコードを取得した後に何がしたいか考えると、ある条件を満たさない場合に削除（又は更新）することだと思います。
今回はレコードの作成日時が最新のレコード以外は論理削除するサンプルを用意しました。
MySQL の場合
はじめに、where 句を繋げてユーザ名が重複しているレコードの中で、作成日時が最新のレコード以外を取得します。
MySQL ではサブクエリの from 句と更新のターゲットの両方に同じテーブルを使用することができません。
参考：MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.10.9 サブクエリーのエラー
そのため、最初に出てくるサブクエリのテーブルを as 句を使って一時的に tmp テーブルに置き換えて、テーブルの更新（論理削除）を行います。（ id も user_id に置き換えます）
mysql> update users set deleted_at=now() where id in (
-> select user_id from (
-> select id as user_id from users where name in (
-> select name from users group by name having count(*) >= 2)
-> and created_at not in (
-> select max(created_at) from users group by name having count(*) >= 2)
-> ) as tmp);
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
# 論理削除されたのか確認(deleted_at にタイムスタンプが挿入されれば OK)
mysql> select * from users;
+----+------+---------------------+---------------------+---------------------+
| id | name | created_at | updated_at | deleted_at |
+----+------+---------------------+---------------------+---------------------+
| 1 | aaa | 2017-10-21 06:30:06 | 2017-10-21 06:30:06 | 2017-10-21 14:43:41 |
| 2 | aaa | 2017-10-21 06:30:07 | 2017-10-21 06:30:07 | 2017-10-21 14:43:41 |
| 3 | aaa | 2017-10-21 06:30:09 | 2017-10-21 06:30:09 | NULL |
| 4 | bbb | 2017-10-21 06:30:12 | 2017-10-21 06:30:12 | 2017-10-21 14:43:41 |
| 5 | bbb | 2017-10-21 06:30:13 | 2017-10-21 06:30:13 | NULL |
| 6 | ccc | 2017-10-21 06:30:16 | 2017-10-21 06:30:16 | NULL |
+----+------+---------------------+---------------------+---------------------+
6 rows in set (0.00 sec)
Ruby on Rails の場合
はじめに maximum( :created_at ) を使って、重複しているユーザ名で作成日時が最新のレコードを取得します。
出力した hash を key と value のリストに変換して where 文に挿入します。
取得した user_ids 以外のレコードを destroy_all で論理削除します。
irb(main):001:0> hash = User.group(:name).having('count(*) >= 2').maximum(:created_at)
=> {"aaa"=>Sat, 21 Oct 2017 06:30:09 UTC +00:00, "bbb"=>Sat, 21 Oct 2017 06:30:13 UTC +00:00}
irb(main):002:0> user_ids = User.where(name: hash.keys, created_at: hash.values).pluck(:id)
=> [3, 5]
irb(main):003:0> User.where(name: hash.keys).where.not(id: user_ids).destroy_all
=> [
#<User id: 1, name: "aaa", created_at: "2017-10-21 06:30:06", updated_at: "2017-10-21 15:46:31", deleted_at: "2017-10-21 15:46:31">,
#<User id: 2, name: "aaa", created_at: "2017-10-21 06:30:07", updated_at: "2017-10-21 15:46:31", deleted_at: "2017-10-21 15:46:31">,
#<User id: 4, name: "bbb", created_at: "2017-10-21 06:30:12", updated_at: "2017-10-21 15:46:31", deleted_at: "2017-10-21 15:46:31"> ]>
ちなみに、Ruby on Rails はデフォルトでは論理削除にならないので、事前に論理削除用の gem を導入することをお勧めします。
まとめ
Ruby on Rails で DB から重複するレコードを取得する方法をまとめました。
MySQL では基本的に group by 句と having 句を利用することで、重複するレコードが取得できます。
Ruby on Rails でも group と having と言う、MySQL と似たようなメソッドが用意されていることが分かりました。
最後のおまけの sql 文はサブクエリを使いまくっているので、データ量の多い DB では処理が重いかもしれません。もっと良い方法あれば教えてください m(_ _)m