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

# 環境
Ruby 3.0.1
Rails 6.1.6
PostgreSQL

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

![ActiveRecord_sql_sample_er.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3674409/4891d4f4-1c2b-136f-a410-8e700a12b59d.png)

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

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

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

<details>
<summary>詳細</summary>

### モデルの用意
前述のER図を元に、User、Book、UserBookの3種類のモデルを用意します。
```text
rails g scaffold User name:string
rails g model Book name:string
rails db:migrate
rails g model UserBook user:references　book:references
```
ここで、UserBookのマイグレーションファイルに、外部キー設定を追記します。
```Ruby: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
```
それが終わったらマイグレーション。
```text
rails db:migrate
```

### アソシエーション
次に、アソシエーションを組みます。
```Ruby:model/book.rb
class Book < ApplicationRecord
  has_many :user_books, dependent: :destroy
  has_many :users, through: :user_books
end
```
```Ruby:model/user_book.rb
class UserBook < ApplicationRecord
  belongs_to :book
  belongs_to :user
end
```
```Ruby:model/user.rb
class User < ApplicationRecord
  has_many :user_books, dependent: :destroy
  has_many :books, through: :user_books
end
```
### データを登録
rails cを使って、データベースに情報を保存します。
```text:User作成
User.create!(name: "任意のユーザー名")
```
```text:Book作成
Book.create!(title: "任意の本のタイトル")
```
```text:UserBook作成
UserBook.create!(user_id: 1, book_id: 1)
# idの値は適宜変えてください
```

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

![database.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3674409/3f856ea1-498a-1160-5b38-affee977b271.png)

※確認用に、一部データを書き換えています。
　以降使わないので、確認が終わったら消してください。
```ruby:user_controller.rb
def index
  @users = User.joins(:books).select('users.*, books.title AS book_title')
end
```
```erb: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 %>
```
</details>

# コードだけ知りたい人向け
結論、以下のコードで実装できます。解説は次項から。
```ruby
@users = User
  .select('users.*, COUNT(books.*) AS number_of_books')
  .group('users.id')
  .order('number_of_books DESC')
```

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

シンプルにクエリを書くと、以下のようになります。
```sql: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: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繋げることができます。
```ruby:users_controller.rb
def index
  @user = User.joins(:books)
end
```

## selectとgroupを使って、カウントした値を新たなカラムに格納する
ここが特にSQLっぽいところです。

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

```ruby: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を編集しました。
しっかり値が反映されていることがわかります。

<details>
<summary>veiwファイルのコードはこちら</summary>

```erb: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 %>
<%# 〜 省略 〜 %>
```

</details>

![before_order.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3674409/f9e4bb84-c517-9ce2-4484-ef4c95702341.png)

## orderで並べ替える
最後に並べ替えです。
```ruby: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`を指定します。

![result.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3674409/b8f1ced8-eece-7ef8-b2c2-37b20895fd1a.png)

これで完成です！

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

![ActiveRecord_SQL_log.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3674409/0e155cf6-6f04-ca2a-b9ca-7e8c60b31bb7.png)

# おまけ
### railsっぽく書く
railsっぽく、できるだけシンボルを使った記述を試してみました。
selectだけはうまく変更できませんでしたが、groupやorderは書き方を変えても問題なさそうです。
```ruby: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が書けると知った後、あれこれ調べてみたのですが関連記事が全く見つからなかったのでまとめてみました。あんまり主流じゃないのかも……。