【Ruby on Rails】重複しているレコードを取得する【MySQL】

概要

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 を導入することをお勧めします。

kyamanak.hatenablog.com

まとめ

Ruby on Rails で DB から重複するレコードを取得する方法をまとめました。

MySQL では基本的に group by 句と having 句を利用することで、重複するレコードが取得できます。

Ruby on Rails でも group と having と言う、MySQL と似たようなメソッドが用意されていることが分かりました。

最後のおまけの sql 文はサブクエリを使いまくっているので、データ量の多い DB では処理が重いかもしれません。もっと良い方法あれば教えてください m(_ _)m