Go to Qiita Advent Calendar 2024 Top
LoginSignup
101
81

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@yuyasat

ActiveRecordでサブクエリ（副問い合わせ）と内部結合

Last updated at Posted at 2018-06-09

0. はじめに

0-0. はじめに

以前、「Rails における内部結合、外部結合まとめ」や「ActiveRecordにおけるGROUP BYの使い方」という記事を書いたのですが、サブクエリに関して、特にサブクエリと結合するときにActiveRecordでどう記述すればいいのか逡巡したときがあったので、備忘録的に記述します。
また、Otemachi.rb#7にて、「いまさらサブクエリ」というタイトルでLTも行ったので、こちらも参考になるかもしれません。

0-1. RubyとRailsとPostgreSQLのバージョン

$ ruby -v
ruby 2.4.1p111 (2017-03-22 revision 58053) [x86_64-darwin16]
$ rails -v
Rails 5.2.0

=> SELECT version();
                                                   version
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.5.3 on x86_64-apple-darwin15.4.0, compiled by Apple LLVM version 7.3.0 (clang-703.0.31), 64-bit

0-2. 今回扱うERD

ERD 説明
女優テーブルがある。
女優テーブルは映画テーブルを持つ。
映画テーブルは、映画に関連するキーワードを入れたタグテーブルを持つ。

0-3. モデル

Actress
class Actress < ApplicationRecord
  has_many :movies
  has_many :tags, through: :movies
end
Movie
class Movie < ApplicationRecord
  has_many :tags
  belongs_to :actress
end
Tag
class Tag < ApplicationRecord
  belongs_to :movie
end

0-4. 今回扱うデータ

actress
SELECT id, name FROM actresses;
 id |    name
----+------------
  1 | 多部未華子
  2 | 佐津川愛美
  3 | 新垣結衣
  4 | 堀北真希
  5 | 吉高由里子
  6 | 悠城早矢
movies
SELECT id, actress_id, title, year FROM movies;
 id | actress_id |        title        | year
----+------------+---------------------+------
  1 |          2 | 蝉しぐれ            | 2005
  2 |          1 | 夜のピクニック      | 2006
  3 |          4 | ALWAYS 三丁目の夕日 | 2005
  4 |          2 | 忍道-SHINOBIDO-     | 2012
  5 |          2 | 貞子vs伽椰子        | 2016
  6 |          4 | 県庁おもてなし課    | 2013
  7 |          5 | 真夏の方程式        | 2013
tags
SELECT id, movie_id, key FROM tags;
 id | movie_id |    key
----+----------+------------
  1 |        1 | 時代劇
  2 |        1 | 子役
  3 |        3 | 昭和
  4 |        5 | ホラー
  5 |        7 | ミステリー
  6 |        7 | 夏
  7 |        6 | 公務員
  8 |        6 | 地方活性
  9 |        1 | 夏
 10 |        4 | 時代劇

1. where句にサブクエリを用いる

タグのある女優だけを取り出したい

（方法１・内部結合）INNER JOINしてDISTINCTをする

Actress.joins(:tags).distinct
=> [#<Actress:0x007f8001839798
  id: 2,
  name: "佐津川愛美",
  created_at: Sun, 26 Mar 2017 12:04:54 JST +09:00,
  updated_at: Sun, 26 Mar 2017 12:04:54 JST +09:00>,
 #<Actress:0x007f8001831b60
  id: 5,
  name: "吉高由里子",
  created_at: Sun, 26 Mar 2017 12:04:54 JST +09:00,
  updated_at: Sun, 26 Mar 2017 12:04:54 JST +09:00>,
 #<Actress:0x007f8001831a20
  id: 4,
  name: "堀北真希",
  created_at: Sun, 26 Mar 2017 12:04:54 JST +09:00,
  updated_at: Sun, 26 Mar 2017 12:04:54 JST +09:00>]

SELECT
  DISTINCT "actresses".*
FROM
  "actresses"
  INNER JOIN
    "movies"
  ON
    "movies"."actress_id" = "actresses"."id"
  INNER JOIN
    "tags"
  ON
    "tags"."movie_id" = "movies"."id"

（方法２・サブクエリ）INNER JOINしてactresses.idだけを先に取り出し、INを使う。

  • scopeの中で内部結合したり、DISTINCTは使いたくないというときもあるので、こちらの方法が良い場合もある。
  • 副問い合わせになるので、SQL発行は一回で行うことができる。
Actress.where(id: Actress.joins(:tags).select("actresses.id"))
=> [#<Actress:0x007f7fff7d8490
  id: 2,
  name: "佐津川愛美",
  created_at: Sun, 26 Mar 2017 12:04:54 JST +09:00,
  updated_at: Sun, 26 Mar 2017 12:04:54 JST +09:00>,
 #<Actress:0x007f7fff7d8300
  id: 4,
  name: "堀北真希",
  created_at: Sun, 26 Mar 2017 12:04:54 JST +09:00,
  updated_at: Sun, 26 Mar 2017 12:04:54 JST +09:00>,
 #<Actress:0x007f7fff7d8198
  id: 5,
  name: "吉高由里子",
  created_at: Sun, 26 Mar 2017 12:04:54 JST +09:00,
  updated_at: Sun, 26 Mar 2017 12:04:54 JST +09:00>]

SELECT
  "actresses".*
FROM
  "actresses"
WHERE
  "actresses"."id"
  IN (
    SELECT
      actresses.id
    FROM
      "actresses"
      INNER JOIN
        "movies"
      ON
        "movies"."actress_id" = "actresses"."id"
      INNER JOIN
        "tags"
      ON
        "tags"."movie_id" = "movies"."id"
  )

2. joinの中でサブクエリを用いる（サブクエリと内部結合）

Actressに紐づけられている映画が最も新しい順にActressを取り出したい

  • 今回用意したデータでは、2005年から2016年までの映画があるが、女優を取り出す時に、登録されている映画が最新のものから順にActressを取り出したい場合のクエリを考える。
  • 参考までに今回のデータを最初に示す。
# 今回のデータを表示するためのコード
Movie.all.order(year: :desc).eager_load(:actress).each { |m| puts "#{m.year}\t#{m.title}\t#{m.actress.name}" };nil

2016	貞子vs伽椰子	佐津川愛美
2013	真夏の方程式	吉高由里子
2013	県庁おもてなし課	堀北真希
2012	忍道-SHINOBIDO-	佐津川愛美
2006	夜のピクニック	多部未華子
2005	蝉しぐれ	佐津川愛美
2005	ALWAYS 三丁目の夕日	堀北真希

2013年が吉高由里子と堀北真希がかぶっていて分かりにくいが、2016年の「貞子vs伽椰子」に出演しているのは佐津川愛美なので、佐津川愛美から順に出力されれば良い。
今回以下に4つの方法を示す。

joins + eager_loadを使う（挙動が不自然）

Actress.joins(:movies).eager_load(:movies).order('movies.year desc')
#=> [#<Actress:0x007f8978525928
  id: 2,
  name: "佐津川愛美",
  created_at: Fri, 23 Mar 2018 01:03:30 JST +09:00,
  updated_at: Fri, 23 Mar 2018 01:03:30 JST +09:00>,
 #<Actress:0x007f897851d0c0
  id: 5,
  name: "吉高由里子",
  created_at: Fri, 23 Mar 2018 01:03:30 JST +09:00,
  updated_at: Fri, 23 Mar 2018 01:03:30 JST +09:00>,
 #<Actress:0x007f8972e37968
  id: 4,
  name: "堀北真希",
  created_at: Fri, 23 Mar 2018 01:03:30 JST +09:00,
  updated_at: Fri, 23 Mar 2018 01:03:30 JST +09:00>,
 #<Actress:0x007f897850fd58
  id: 1,
  name: "多部未華子",
  created_at: Fri, 23 Mar 2018 01:03:30 JST +09:00,
  updated_at: Fri, 23 Mar 2018 01:03:30 JST +09:00>]

SELECT
  "actresses"."id" AS t0_r0,
  "actresses"."name" AS t0_r1,
  "actresses"."created_at" AS t0_r2,
  "actresses"."updated_at" AS t0_r3,
  "movies"."id" AS t1_r0,
  "movies"."actress_id" AS t1_r1,
  "movies"."title" AS t1_r2,
  "movies"."year" AS t1_r3,
  "movies"."created_at" AS t1_r4,
  "movies"."updated_at" AS t1_r5
FROM
  "actresses"
INNER JOIN
  "movies"
ON
  "movies"."actress_id" = "actresses"."id"
ORDER BY
  movies.year desc

しかし、この挙動はいささか不自然である。なぜなら、内部結合しているにも関わらず、返り値にactressが重複していないからである。SQLの実行結果から考えると、堀北真希の次に佐津川愛美がくるべきである。
また、このクエリの場合、kaminariのgemを使うとうまく動いてくれない。例えば、今回のクエリで、per(4)とすると、

Rails
Actress.joins(:movies).eager_load(:movies).order('movies.year desc').page(nil).per(4).count
# => 3

結果が３つだけとなってしまう。これは、kaminariが自動的にクエリを２回に分けて発行してしまっているからである。
一つ目のクエリは

SELECT
  DISTINCT movies.year AS alias_0,
  "actresses"."id"
FROM
  "actresses"
INNER JOIN
  "movies"
ON
  "movies"."actress_id" = "actresses"."id"
ORDER BY
  movies.year desc
  LIMIT 4 OFFSET 0
;

であり、映画の公開年で降順に並べ替えたactresses.idを四つ取得している。
このクエリの結果は

 alias_0 | id
---------+----
    2016 |  2
    2013 |  5
    2013 |  4
    2012 |  2

となる。ポイントは、id=2が２回登場しているということである。この結果を２つ目のクエリのIN句に渡している。

SELECT
  COUNT(DISTINCT "actresses"."id")
FROM
  "actresses"
INNER JOIN
  "movies"
ON
  "movies"."actress_id" = "actresses"."id"
WHERE
  "actresses"."id" IN (2, 5, 4, 2)

IN句の中に重複があるので結果は3つとなってしまい、kaminariのpaginationが正しく動作しない。

find_by_sqlを用いる（返り値の型がArrayなので、使いにくい）

ここで、原点に戻ってSQLで考える。SQLで書くならば例えば次のようになる。

SELECT "actresses".*, MAX("movies"."year") as max_year
FROM actresses, movies
WHERE
  "actresses"."id" = "movies"."actress_id"
GROUP BY
  "actresses"."id",
  "movies"."actress_id"
ORDER BY
  max_year DESC
;

あるいは、FROM句に複数テーブルを記述するSQLはActiveRecordでは発行しにくい（できない？）ので、素直にINNER JOINを用いて次のように書く。

SELECT actresses.*
FROM actresses
INNER JOIN (
  SELECT
    "movies"."actress_id" as actress_id,
    MAX(year) as max_year
  FROM
    "movies"
  GROUP BY
    "movies"."actress_id"
) movies_max_year
ON actresses.id = movies_max_year.actress_id
ORDER BY max_year DESC
;

このSQLをActiveRecordから発行したい。
単純に思いつくのは、find_by_sqlを使う方法。

Rails
Actress.find_by_sql(%|
  SELECT "actresses".*
  FROM actresses
  INNER JOIN (
    SELECT
      "movies"."actress_id" as actress_id,
      MAX(year) as max_year
    FROM
      "movies"
    GROUP BY
      "movies"."actress_id"
  ) movies_max_year
  ON "actresses"."id" = "movies_max_year"."actress_id"
  ORDER BY max_year DESC
|)

しかし、find_by_sqlでは返り値の型がArrayになってしまう。やはり使い勝手の良さを考えるとActress::ActiveRecord_Relationで返ってきて欲しい。

Arelを用いる（賛否両論あり）

Arelを用いれば、Actress::ActiveRecord_Relationで返ってこさせることができる。
Arelは内部APIなのでバージョンが異なると仕様が異なるかもしれないので、ご利用の際はきちんとテストを書くことをおすすめ

Rails
actress_at = Actress.arel_table
movie_at = Movie.arel_table
movies_max_year = movie_at.project(
  Arel.sql(%|
    "movies"."actress_id" as actress_id,
    MAX(year) as max_year
  |)
).group("movies.actress_id").as("movies_max_year")

join_conds = actress_at.join(
  movies_max_year,
  Arel::Nodes::InnerJoin
).on(
  movies_max_year[:actress_id].eq(actress_at[:id])
).join_sources

Actress.joins(join_conds).order('movies_max_year.max_year DESC')

型を確認してみると、無事Actress::ActiveRecord_Relationにっているのがわかる。

Rails
Actress.joins(join_conds).order('movies_max_year.max_year DESC').class
# => Actress::ActiveRecord_Relation

ここまでくれば、無事kaminariを用いることができる。

joinsの中にSQLを直書きする方法（SQL直書きOK派の人にはおすすめ）

世の中には、Railsを使っていてSQLを直書きするなんてという人と、Arel使うぐらいならSQL直書きするという人がいるらしい。
最近は、こちらの記事の影響からか、Arel使わない勢の方が優勢かもしれない。
ポイントは、joinsを用いていればデフォルトで内部結合になるが、サブクエリを用いるがゆえに、内部結合でもSQLを直書きしている点。

Actress.joins(%|
  INNER JOIN (
    SELECT
      "movies"."actress_id" AS actress_id,
      MAX(year) AS max_year
    FROM
      "movies"
    GROUP BY
      "movies"."actress_id"
  ) AS movies_max_year
  ON actresses.id = movies_max_year.actress_id
|).order("max_year desc, actresses.id asc")

参考

101
81
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
101
81

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?