Ruby on Rails

【Rails】子供のレコードで親レコードを絞り込む方法 (GROUP BY, サブクエリ)

今回はActiveRecordでデータを絞り込む方法についてまとめていきたいと思います。
今回扱うケースは子供のレコードの値で親データを絞り込む方法です。

前提

PublisherモデルとBookモデルがあり、これらが1対多の関係にあるとします。今回は「地球の歴史」と「太陽の歴史」という名前の本を両方持っているPublisherを検索したいとします。

publishersテーブル

idname
1著者0
2著者1
3著者3
4著者4

booksテーブル

idnamepublisher_id
1地球の歴史1
2太陽の歴史1
3地球の歴史2
4太陽の歴史3

このようなデータを持っている場合、検索で取りたいデータは著者0のみになります。なお、生のSQLは書かずにORマッパーで書くことを目指します。

方法1

まずひとつ目の方法としてはINNER JOINでテーブルを結合し、GROUP BYを使ってグルーピングして、HAVING句を使って絞り込むやりかたでできました。SQLで書くと以下のようになります。

SELECT publishers.id FROM publishers INNER JOIN books ON books.publisher_id = publishers.id WHERE books.name IN ('地球の歴史', '太陽の歴史') GROUP BY companies.id HAVING COUNT(*) = 2

これをORマッパーで書くとこのようになりました。

Publiser.joins(:books).where(books: { name: ['地球の歴史', '太陽の歴史'] }).group('publishers.id').having('COUNT(*) = 2')
=> [#<Publisher:0x00007fa6a02318d0 id: 1, name: "著者0", created_at: Sun, 30 May 2021 23:09:05.909641000 UTC +00:00, updated_at: Sun, 30 May 2021 23:09:05.909641000 UTC +00:00>]

ただし、注意する点としては、publisher_idと本の名前の組み合わせは一意にしておく必要があります。でないと以下のように検索結果が以下のようになってしまい、結果的に一件もヒットしなくなってしまいます。

Book.create(name: '地球の歴史', publisher_id: 1)
Publisher.joins(:books).where(books: { name: ['地球の歴史', '太陽の歴史'] })
=> [#<Publisher:0x00007fa6a0383d78 id: 1, name: "著者0", created_at: Sun, 30 May 2021 23:09:05.909641000 UTC +00:00, updated_at: Sun, 30 May 2021 23:09:05.909641000 UTC +00:00>,
 #<Publisher:0x00007fa6a0383cb0 id: 1, name: "著者0", created_at: Sun, 30 May 2021 23:09:05.909641000 UTC +00:00, updated_at: Sun, 30 May 2021 23:09:05.909641000 UTC +00:00>,
 #<Publisher:0x00007fa6a0383b48 id: 2, name: "著者1", created_at: Sun, 30 May 2021 23:09:05.983101000 UTC +00:00, updated_at: Sun, 30 May 2021 23:09:05.983101000 UTC +00:00>,
 #<Publisher:0x00007fa6a0383a80 id: 3, name: "著者2", created_at: Sun, 30 May 2021 23:09:06.054952000 UTC +00:00, updated_at: Sun, 30 May 2021 23:09:06.054952000 UTC +00:00>,
 #<Publisher:0x00007fa6a03839b8 id: 1, name: "著者0", created_at: Sun, 30 May 2021 23:09:05.909641000 UTC +00:00, updated_at: Sun, 30 May 2021 23:09:05.909641000 UTC +00:00>]

方法2

2つ目の方法としては、サブクエリを使ってAND条件で繋げる方法です。本の名前が「地球の歴史」かつ、本の名前が「太陽の歴史」であるIDを持つ著者を検索しています。SQLとしては、以下のようになります。

SELECT publishers.* FROM publishers INNER JOIN books ON publishers.id = books.publisher_id WHERE books.name = '地球の歴史' AND publishers.id = (SELECT publishers.id FROM publishers INNER JOIN books ON publishers.id = books.publisher_id WHERE books.name = '太陽の歴史')

ORマッパーで書くと以下のようになります。

Publisher.joins(:books).where(books: {name: "地球の歴史"}).where(id: Publisher.joins(:books).where(books: {name: "太陽の歴史"}).select(:id))
=> [#<Publisher:0x00007fa6a3beb788 id: 1, name: "著者0", created_at: Sun, 30 May 2021 23:09:05.909641000 UTC +00:00, updated_at: Sun, 30 May 2021 23:09:05.909641000 UTC +00:00>]

こちらもpublisher_idと本の名前の組み合わせは一意にしておかないとこちらは重複して著者がヒットします。

Book.create(name: '地球の歴史', publisher_id: 1)
Publisher.joins(:books).where(books: {name: "地球の歴史"}).where(id: Publisher.joins(:books).where(books: {name: "太陽の歴史"}).select(:id))
=> [#<Publisher:0x00007fa6a290a6a0 id: 1, name: "著者0", created_at: Sun, 30 May 2021 23:09:05.909641000 UTC +00:00, updated_at: Sun, 30 May 2021 23:09:05.909641000 UTC +00:00>,
 #<Publisher:0x00007fa6a290a268 id: 1, name: "著者0", created_at: Sun, 30 May 2021 23:09:05.909641000 UTC +00:00, updated_at: Sun, 30 May 2021 23:09:05.909641000 UTC +00:00>]

ただしこちらはdistinctで重複削除すればよさそうですね。

Publisher.joins(:books).where(books: {name: "地球の歴史"}).where(id: Publisher.joins(:books).where(books: {name: "太陽の歴史"}).select(:id)).distinct
=> [#<Publisher:0x00007fa6a0a64c28 id: 1, name: "著者0", created_at: Sun, 30 May 2021 23:09:05.909641000 UTC +00:00, updated_at: Sun, 30 May 2021 23:09:05.909641000 UTC +00:00>]

さいごに

最初SQLの知識が薄い分かなり苦労しましたが、なんとか理解することができました。複雑なデータ操作だとGROUP BYやHAVINGで絞り込んだり、サブクエリを用いる必要があり難しく感じました。また、何かしら複雑なデータ操作をするときはこれらが使えないかを考えることにします。
ここまで読んでいただきありがとうございました!

ABOUT ME
sakai
東京在住の30歳。元々は車部品メーカーで働いていてましたが、プログラミングに興味を持ちスクールに通ってエンジニアになりました。 そこからベンチャー → メガベンチャー → 個人事業主になりました。 最近は生成 AI 関連の業務を中心にやっています。 ヒカルチャンネル(Youtube)とワンピースが大好きです!