【Rails】ActiveRecordでSQLっぽくテーブル結合する方法

環境

Ruby 3.0.1
Rails 6.1.6
PostgreSQL

やりたいこと

Railsのメソッドを使ってテーブルを結合し、結合先の値を集計・並べ替えを行いたいと思います。
例えば、以下のようなデータベースがあるとします。

こちらは、ユーザーが持っている本をuser_booksに登録していくテーブルです。usersとbooksを多対多でアソシエーションを組んでいます。
これを使って、たくさん本を持っているユーザー順に並べ替えたいと思います。

準備

前述のER図を元にモデルを作成し、アソシエーションを組んで、コンソール等で必要なデータを作ります。詳細はトグルの中に記載しています。

作り方がわかる方は飛ばしてください。

詳細

モデルの用意

前述のER図を元に、User、Book、UserBookの3種類のモデルを用意します。

rails g scaffold User name:string
rails g model Book name:string
rails db:migrate
rails g model UserBook user:references　book:references

ここで、UserBookのマイグレーションファイルに、外部キー設定を追記します。

date_create_user_books.rb

class CreateUserBooks < ActiveRecord::Migration[6.1]
  def change
    create_table :user_books do |t|
      t.references :user, foreign_key: true
      t.references :book, foreign_key: true

      t.timestamps
    end
  end
end

それが終わったらマイグレーション。

rails db:migrate

アソシエーション

次に、アソシエーションを組みます。

model/book.rb

class Book < ApplicationRecord
  has_many :user_books, dependent: :destroy
  has_many :users, through: :user_books
end

model/user_book.rb

class UserBook < ApplicationRecord
  belongs_to :book
  belongs_to :user
end

model/user.rb

class User < ApplicationRecord
  has_many :user_books, dependent: :destroy
  has_many :books, through: :user_books
end

データを登録

rails cを使って、データベースに情報を保存します。

User作成

User.create!(name: "任意のユーザー名")

Book作成

Book.create!(title: "任意の本のタイトル")

UserBook作成

UserBook.create!(user_id: 1, book_id: 1)
# idの値は適宜変えてください

出来上がったものがこちら

※確認用に、一部データを書き換えています。
　以降使わないので、確認が終わったら消してください。

user_controller.rb

def index
  @users = User.joins(:books).select('users.*, books.title AS book_title')
end

users/index.html.erb

<% @users.each do |user| %>
      <tr>
            <td><%= user.name %></td>
            <td><%= user.book_title %></td> <%# 追加 %>
            <td><%= link_to 'Show', user %></td>
            <td><%= link_to 'Edit', edit_user_path(user) %></td>
            <td><%= link_to 'Destroy', user, method: :delete, data: { confirm: 'Are you sure?' } %></td>
      </tr>
<% end %>

コードだけ知りたい人向け

結論、以下のコードで実装できます。解説は次項から。

@users = User
  .select('users.*, COUNT(books.*) AS number_of_books')
  .group('users.id')
  .order('number_of_books DESC')

SQLで書くとどうなるか？

SQLを使ってたくさん本を持っているユーザー順に並べ替えるには、中間テーブルのuser_booksの要素数をユーザーid別にカウントすれば良いです。

シンプルにクエリを書くと、以下のようになります。

SQL_シンプルver

SELECT users.*, 
COUNT(user_books.*) 
AS number_of_books 
FROM "users" 
INNER JOIN "user_books" 
ON "user_books"."user_id" = "users"."id" 
GROUP BY "users"."id" 
ORDER BY number_of_books DESC;

ただActiveRecord上で結合する際は、中間テーブルを挟まずusersとbooksを直接結合できるようなので、最終的には以下のようになります。

SQL

SELECT users.*, 
COUNT(books.*) 
AS number_of_books 
FROM "users" 
INNER JOIN "user_books" 
ON "user_books"."user_id" = "users"."id" 
INNER JOIN "books" 
ON "books"."id" = "user_books"."book_id" 
GROUP BY "users"."id"
ORDER BY number_of_books DESC;

Railsで書くには？

joinsで結合する

joinメソッドを使って、テーブルを結合します。
アソシエーションを組む際にthroughを使用していれば、直接usersとbooks繋げることができます。

users_controller.rb

def index
  @user = User.joins(:books)
end

selectとgroupを使って、カウントした値を新たなカラムに格納する

ここが特にSQLっぽいところです。

selectメソッドでは、SQLのクエリを文字列としてそのまま埋め込むことができます。
これにより柔軟にクエリを構築できます。

users_controller.rb

def index
  @users = User.joins(:books)
    .select('users.*, COUNT(books.*) AS number_of_books') # 追加
    .group('users.id') # 追加
end

.select('users.*,で、結合したテーブルを含めたuserテーブルの全列を選択します。

COUNT(books.*)で、booksテーブルの要素数をカウントできます。.group('users.id')でユーザーIDごとにグループ化しているので、ユーザー毎に持っている本の数を集計できます。

集計した値はAS number_of_books')で作った新しいカラムに格納します。

確認用のveiwを作って見てみる

Countに本の数、Booksにタイトル一覧が表示されるようにviewを編集しました。
しっかり値が反映されていることがわかります。

veiwファイルのコードはこちら

users/index.html.erb

<%# 〜 省略 〜 %>
<% @users.each do |user| %>
      <tr>
        <td><%= user.name %></td>
        <%# -- ここから -- %>
        <td><%= user.number_of_books %></td>
        <td>
          <% user.books.each_with_index do |book, i| %>
            <% if i == user.books.size - 1 %>
              <%= book.title %>
            <% else %>
              <%= book.title %>,
            <% end %>
          <% end %>
        </td>
        <%# -- ここまで追加 -- %>
        <td><%= link_to 'Show', user %></td>
        <td><%= link_to 'Edit', edit_user_path(user) %></td>
        <td><%= link_to 'Destroy', user, method: :delete, data: { confirm: 'Are you sure?' } %></td>
      </tr>
    <% end %>
<%# 〜 省略 〜 %>

orderで並べ替える

最後に並べ替えです。

users_controller.rb

def index
  @users = User.joins(:books)
    .select('users.*, COUNT(books.*) AS number_of_books')
    .group('users.id')
    .order('number_of_books DESC') # 追加
end

orderメソッドは、カラムを指定して並べ替えるメソッドです。
order('カラム名 ソート方法')、またはorder(カラム名: :ソート方法)の形で使用します。

ソート方法は2種類あり、昇順の場合はASK、降順の場合はDESCを指定します。

これで完成です！

ログを確認

usersのindexページにアクセスして、ログを確認してみましょう。
よく見ると、しっかりSQLに変換されていますね。

おまけ

railsっぽく書く

railsっぽく、できるだけシンボルを使った記述を試してみました。
selectだけはうまく変更できませんでしたが、groupやorderは書き方を変えても問題なさそうです。

users_controller.rb

@users = User.joins(:books)
  .select('users.*, COUNT(books.*) AS number_of_books') # ここは変わらず
  .group(:id) # usersは省略できるらしい
  .order(number_of_books: :DESC)

まとめた経緯

selectメソッド内に直接SQLが書けると知った後、あれこれ調べてみたのですが関連記事が全く見つからなかったのでまとめてみました。あんまり主流じゃないのかも……。