Go to Qiita Advent Calendar 2024 Top
LoginSignup
11
6

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.

@jaramon

ActiveRecordに限界を感じArelでサブクエリを頑張って書いてみた

Last updated at Posted at 2018-04-23

はじめに

railsを触り始めて4ヶ月が経ち、「ActiveRecordってSQLを知らなくても書けるってすごいなぁ」
と思っていた自分に変化が訪れました。その変化とは、「何でActiveRecordって全部纏めちゃうんだよ。。。」
という気持ちが芽生えたことです。
今私が扱っている案件では、「複数のテーブルから、ある条件のレコードの件数を、タイムアウトなしで取得したい」
という状態になっています。
一例ではありますが、以下のようなサブクエリを使用しないscopeを複数チェーンして、各条件の件数を取得していました。

scope :select_page_views_count, -> do
  left_joins(:page_views)
    .select("COUNT(DISTINCT page_views.id) AS page_views_count")
    .group("#{table_name}.id")
end

scope :select_microposts_count, -> do
  left_joins(:microposts)
    .select("COUNT(DISTINCT microposts.id) AS microposts_count")
    .group("#{table_name}.id")
end

そしてタイムアウトが発生し、SQLの闇であるチューニングの世界に入り込みました。
早く検索できるSQL(サブクエリ有)を作成し、いざActiveRecordで書いてみよう！！！と思い書いてみると、

「こいつ...groupの条件とか全部纏めるやん...サブクエリ発行してくれないやん...」

というわけで、今回はArelという、ActiveRecordの内部で使用されるSQL生成ライブラリを使用して、
複雑なサブクエリを実現したいと思います。

※ただし、ArelはRailsのプライベートAPIのため、利用しない方がいいらしいので、ご利用は計画的にお願いします。

Arelとは

gitHub: https://github.com/rails/arel を参照

Arelを使ってみる

実際にArelを使ってみましょう。
Modelは以下の通りです。

app/models/user.rb
class User < ApplicationRecord
  has_many :microposts, dependent: :destroy
  .
  .
  .
end
app/models/micropost.rb
class Micropost < ApplicationRecord
  belongs_to :user
  .
  .
  .
end

Arelへの変換

まずは既存のModelをArelに変換します。

>> User.arel_table
=> #<Arel::Table:0x00000003bac8d8 @name="users", @type_caster=#<ActiveRecord::TypeCaster::Map:0x00000003bac928 @types=User (call 'User.connection' to establish a connection)>, @table_alias=nil>

>> Micropost.arel_table
=> #<Arel::Table:0x000000027d4388 @name="microposts", @type_caster=#<ActiveRecord::TypeCaster::Map:0x000000027d43d8 @types=Micropost(id: integer, content: text, user_id: integer, created_at: datetime, updated_at: datetime, picture: string, likes_count: integer)>, @table_alias=nil>


>> users = User.arel_table
=> #<Arel::Table:0x00000003bac8d8 @name="users", @type_caster=#<ActiveRecord::TypeCaster::Map:0x00000003bac928 @types=User(id: integer, name: string, email: string, created_at: datetime, updated_at: datetime, password_digest: string, remember_digest: string, admin: boolean, activation_digest: string, activated: boolean, activated_at: datetime, reset_digest: string, reset_sent_at: datetime)>, @table_alias=nil>

>> microposts = Micropost.arel_table
=> #<Arel::Table:0x000000027d4388 @name="microposts", @type_caster=#<ActiveRecord::TypeCaster::Map:0x000000027d43d8 @types=Micropost(id: integer, content: text, user_id: integer, created_at: datetime, updated_at: datetime, picture: string, likes_count: integer)>, @table_alias=nil>

簡単なクエリの発行

Arel、ActiveRecordで簡単なクエリを発行してみましょう。

SELECT

usersテーブルからidを取得

# ActiveRecord
>> User.select(:id).to_sql
=>
"SELECT
  \"users\".\"id\"
FROM
  \"users\""

# Arel
>> users.project(users[:id]).to_sql
=>
"SELECT
  \"users\".\"id\"
FROM
  \"users\""

WHERE

usersテーブルから、名前がmojamojaで、作成日が昨日の今の時間から今日の今の時間までの全てのカラムを取得

# ActiveRecord
>> User.where(name: "mojamoja").where(created_at: Time.zone.now-1.day..Time.zone.now).to_sql
=>
"SELECT
  \"users\".*
FROM
  \"users\"
WHERE
  \"users\".\"name\" = 'mojamoja'
AND
  (\"users\".\"created_at\" BETWEEN '2018-03-08 03:17:47.474153' AND '2018-03-09 03:17:47.474396')"

# Arel
>> users.project(users[Arel.sql('*')])
     .where(users[:name].eq("mojamoja"))
     .where(users[:created_at].between(Time.zone.now-1.day..Time.zone.now)).to_sql
=>
"SELECT
  \"users\".*
FROM
  \"users\"
WHERE
  \"users\".\"name\" = 'mojamoja'
AND
  \"users\".\"created_at\" BETWEEN '2018-03-08 03:22:09.787989' AND '2018-03-09 03:22:09.788328'"

JOIN

usersテーブルとmicropostsテーブルを内部結合し、usersテーブルの全てのカラムを取得

# ActiveRecord
>> User.joins(:microposts).to_sql
=>
"SELECT
  \"users\".*
FROM
  \"users\"
INNER JOIN
  \"microposts\"
ON
  \"microposts\".\"user_id\" = \"users\".\"id\""

# Arel
>> users.project(users[Arel.sql('*')])
     .join(microposts)
     .on(microposts[:user_id].eq(users[:id])).to_sql
=>
"SELECT
  \"users\".*
FROM
  \"users\"
INNER JOIN
  \"microposts\"
ON
  \"microposts\".\"user_id\" = \"users\".\"id\""

...この辺までのクエリは、ActiveRecordのコードの方が分かりやすいですね。

複雑なクエリ（サブクエリ）の発行

次に、Arel、ActiveRecordでサブクエリを発行してみましょう。

FROMでのサブクエリ

# ActiveRecord
>> # 実現不可能でした。。。知っている方いたらご教示ください。

# Arel
>> Arel::Table.new(nil)
     .project(Arel.sql('*'))
     .from(
       users.project(Arel.sql('*'))
     ).to_sql
=>
"SELECT
  *
FROM (
  SELECT
    *
  FROM
    \"users\"
)"

WHEREでのサブクエリ

# ActiveRecord
>> User.where(id: Micropost.select(:user_id).where(id: 1)).to_sql
=>
"SELECT
  \"users\".*
FROM
  \"users\"
WHERE
  \"users\".\"id\" IN (
    SELECT
      \"microposts\".\"user_id\"
    FROM
      \"microposts\"
    WHERE
      \"microposts\".\"id\" = 1
  )"

# Arel
>> users.project(users[Arel.sql('*')])
     .where(users[:id].eq(
       microposts.project(microposts[:user_id])
         .where(microposts[:id].eq('1'))
     )).to_sql
=>
"SELECT
  \"users\".*
FROM
  \"users\"
WHERE
  \"users\".\"id\" = (
    SELECT
      \"microposts\".\"user_id\"
    FROM
      \"microposts\"
    WHERE
      \"microposts\".\"id\" = 1
  )"

ActiveRecordでは発行できなかったり、サブクエリにモデルが必要だったりと、ActiveRecordの限界が見えてきましたね。

超複雑なクエリ（たくさんのサブクエリとグルーピングと件数算出）の発行

ここで本題です。実現したい内容は以下となります。

  • 要望内容
    • ユーザの活動状況を分析したいため、ユーザ一覧に他のユーザページを見た件数、ユーザが投稿した件数を表示したい
    • ユーザ一覧で期間を選択すると、その該当期間での上記の件数を表示したい
  • 実現内容
    • ユーザ毎のあるページの閲覧数(page_view)、投稿数(micropost)のある期間でのレコードの件数
    • 上記のレコードをscopeとしたい

Modelは以下の通りです。

app/models/user.rb
class User < ApplicationRecord
  has_many :page_views, dependent: :destroy
  has_many :microposts, dependent: :destroy
  .
  .
  .
  scope :select_page_views_count, -> do
    page_views_table = PageView.arel_table
    count_page_views_table = page_views_table
                               .project(Arel.sql("count(*) as page_views_cnt, user_id as page_views_user_id"))
                               .where(page_views_table[:created_at].between(Time.zone.now-1..Time.zone.now))
                               .group(page_views_table[:user_id])
                               .as("count_page_views")
    count_page_views_arel_table = arel_table.join(count_page_views_table, Arel::Nodes::OuterJoin)
                                    .on(count_page_views_table[:page_views_user_id].eq(arel_table[:id]))
    select("count_page_views.page_views_cnt as views_count").joins(count_page_views_arel_table.join_sources)
  end

  scope :select_microposts_count, -> do
    microposts_table = Micropost.arel_table
    count_microposts_table = microposts_table
                               .project(Arel.sql("count(*) as microposts_cnt, user_id as microposts_user_id"))
                               .where(microposts_table[:created_at].between(Time.zone.now-1..Time.zone.now))
                               .group(microposts_table[:user_id])
                               .as("count_microposts")
    count_microposts_arel_table = arel_table.join(count_microposts_table, Arel::Nodes::OuterJoin)
                                    .on(count_microposts_table[:microposts_user_id].eq(arel_table[:id]))
    select("count_microposts.microposts_cnt as microposts_count").joins(count_microposts_arel_table.join_sources)
  end
end
app/models/page_view.rb
class PageView < ApplicationRecord
  belongs_to :user
  .
  .
  .
end
app/models/micropost.rb
class Micropost < ApplicationRecord
  belongs_to :user
  .
  .
  .
end

これをarelで実現すると以下のようになります。

>> User.select_page_views_count.to_sql
=>
"SELECT
  count_page_views.page_views_cnt as views_count
FROM
  `users`
LEFT OUTER JOIN (
  SELECT
    count(*) as page_views_cnt,
    user_id as page_views_user_id
  FROM
    `page_views`
  WHERE
    `page_views`.`created_at` BETWEEN '2018-04-13 01:51:40' AND '2018-04-13 01:51:41'
  GROUP BY
    `page_views`.`user_id`
  ) count_page_views
ON
  count_page_views.`page_views_user_id` = `users`.`id`"

>> User.select_microposts_count.to_sql
=>
"SELECT
  count_microposts.microposts_cnt as microposts_count
FROM
  `users`
LEFT OUTER JOIN (
  SELECT
    count(*) as microposts_cnt,
    user_id as microposts_user_id
  FROM
    `microposts`
  WHERE
    `microposts`.`created_at` BETWEEN '2018-04-13 01:49:40' AND '2018-04-13 01:49:41'
  GROUP BY
    `microposts`.`user_id`
  ) count_microposts
ON
  count_microposts.`microposts_user_id` = `users`.`id`"

>> User.select_page_views_count.select_microposts_count.to_sql
=>
"SELECT
  count_page_views.page_views_cnt as views_count,
  count_microposts.microposts_cnt as microposts_count
FROM
  `users`
LEFT OUTER JOIN (
  SELECT
    count(*) as page_views_cnt,
    user_id as page_views_user_id
  FROM
    `page_views`
  WHERE
    `page_views`.`created_at` BETWEEN '2018-04-13 01:52:59' AND '2018-04-13 01:53:00'
  GROUP BY
    `page_views`.`user_id`
  ) count_page_views
ON
  count_page_views.`page_views_user_id` = `users`.`id`"
LEFT OUTER JOIN (
  SELECT
    count(*) as microposts_cnt,
    user_id as microposts_user_id
  FROM
    `microposts`
  WHERE
    `microposts`.`created_at` BETWEEN '2018-04-13 01:52:59' AND '2018-04-13 01:53:00'
  GROUP BY
    `microposts`.`user_id`
  ) count_microposts
ON
  count_microposts.`microposts_user_id` = `users`.`id`

GROUP BYが各サブクエリの中で発行されていますね。
ActiveRecordの場合、サブクエリ内でGROUP BYを使用するにはjoin関数で生SQLを書かなくてはなりません。
これを知らずに、group関数を各scopeに追加しすると、最後にまとめてGROUP BYしてしまい、予期せぬ結果が取得される恐れがあります。

まとめ

Arelで記載したコードがとても綺麗とは思えないですが、汎用性は高い気はします。
ですが、結局メンテナンス性が落ちるので、実際に使用することはありませんでした。
これが公式のAPIになってくれたら、がっつり使って行きたいですね。
※何度も言いますが、ArelはRailsのプライベートAPIのため、利用しない方がいいらしいので、ご利用は計画的にお願いします。

11
6
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
11
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?