【初心者向け】RailsとSQLでデータ取得：本データ取得の実践例

はじめに

技術書のレビューアプリを開発している際に、本のデータを取得する方法に悩みました。ChatGPTなどのツールを活用して本を取得するコードは書けましたが、理解を深めるためにこの記事を書こうと思います。

今回取得したい具体的なデータは、平均難易度が1から3の本で、投稿数が多い順に並べた上位8件です。

この記事では、問題を解決する過程で学んだSQLとRailsについて解説します。実際のコード例を交えながら、具体的な実装手順を紹介します。

データベースの設計

データベースについて、主要なカラムを抜粋して説明します。今回のデータ取得には、以下の2つのテーブルを使用します。

ユーザーは本に対して5段階評価の難易度の投稿をすることができます。

`books`テーブル

カラム名	データ型	説明
id	integer	主キー
title	string	本のタイトル
author	string	著者名

`posts`テーブル

カラム名	データ型	説明
id	integer	主キー
book_id	integer	`books`テーブルへの外部キー
user_id	integer	ユーザーID
difficulty	integer	難易度（1〜5）
comment	text	コメント

モデルの設定

今回の設定では、BookとPostが1対多の関係になっています。

`app/models/book.rb`

class Book < ApplicationRecord
  has_many :posts, dependent: :destroy
end

`app/models/post.rb`

class Post < ApplicationRecord
  belongs_to :book
end

ポイント:

has_many :posts: 一冊の本には複数の投稿（レビュー）が紐づく関係を表現。
dependent: :destroy: 本が削除された際に関連する投稿も一緒に削除されるよう設定。
belongs_to :book: 各投稿が一冊の本に属する関係を表現。

必要なデータの取得

現在、以下の条件に合致する本のデータを取得したいと考えています。

本の平均難易度が1から3の間
投稿数が多い順に並べる
上位8件を取得する

この要件を満たすために、まずSQLでデータを取得する方法を考え、次にRailsで実装します。Railsにまだ慣れていないため、SQLから始めることにしました。

日本語で考える

まず、どのようなクエリが必要かを日本語で整理します。以下の手順で考えます。

テーブルの結合: booksテーブルとpostsテーブルをbook_idで結合する。
必要なデータの抽出: 各本に対して、平均難易度と投稿数を計算する。
条件の適用: 平均難易度が1から3の間の本に絞り込む。
並び替えと制限: 投稿数が多い順に並べ、上位8件を取得する。

SQLのコード

各ステップで行う処理について説明します。

1. テーブルの結合

SELECT *
FROM books
INNER JOIN posts ON posts.book_id = books.id;

説明: booksテーブルとpostsテーブルをbook_idで結合し、関連するデータを結びつけます。

2. 必要なデータの抽出

SELECT books.*, AVG(posts.difficulty) AS average_difficulty, COUNT(posts.id) AS posts_count
FROM books
INNER JOIN posts ON posts.book_id = books.id
GROUP BY books.id;

説明: 各本の平均難易度と投稿数を計算し、booksテーブルの全カラムと共に取得します。

3. 条件の追加

HAVING AVG(posts.difficulty) BETWEEN 1 AND 3;

説明: 平均難易度が1から3の範囲にある本に絞り込みます。

4. 並び替えと制限

ORDER BY posts_count DESC
LIMIT 8;

説明: 投稿数が多い順に並べ替え、上位8件を取得します。

完全なSQLクエリ

上記のステップを組み合わせた最終的なSQLクエリは以下の通りです。

SELECT
  books.*,
  AVG(posts.difficulty) AS average_difficulty,
  COUNT(posts.id) AS posts_count
FROM
  books
INNER JOIN
  posts ON posts.book_id = books.id
GROUP BY
  books.id
HAVING
  AVG(posts.difficulty) BETWEEN 1 AND 3
ORDER BY
  posts_count DESC
LIMIT 8;

クエリの説明:

SELECT: 必要なカラムと計算結果（平均難易度、投稿数）を選択。
INNER JOIN: booksとpostsをbook_idで結合。
GROUP BY: 各本ごとにグループ化。
HAVING: 平均難易度が1から3の間にある本に絞り込む。ちなみに実行順序の関係からHAVINGではaverage_difficultyというSELECTで定義したエイリアスは使いません。
ORDER BY: 投稿数が多い順に並べ替え。
LIMIT: 上位8件を取得。

Railsでの実装

上記のSQLクエリをRailsで実装すると以下のようになります。ActiveRecordを使用することで、SQLクエリをシンプルかつ読みやすく記述できます。

@books = Book
  .joins(:posts) # postsテーブルと結合
  .select('books.*, AVG(posts.difficulty) AS average_difficulty, COUNT(posts.id) AS posts_count') # 必要なカラムを選択
  .group('books.id') # books.idでグループ化
  .having('AVG(posts.difficulty) BETWEEN ? AND ?', 1, 3) # 平均難易度の条件を設定
  .order('posts_count DESC') # 投稿数の多い順に並び替え
  .limit(8) # 上位8件を取得

コードのポイント:

joins(:posts): booksとpostsを関連付けて結合します。
select: 必要なカラムと計算結果を選択します。
group('books.id'): 各本ごとにグループ化します。
having: 平均難易度の条件を適用します。
order: 投稿数が多い順に並べ替えます。
limit(8): 上位8件を取得します。

RailsのActiveRecordを活用することで、複雑なSQLクエリもシンプルに記述できます。

おわりに

今回の記事を書いて、SQLとRailsのクエリ作成について学び直すことができました。特に、日本語で要件を整理することで、必要なクエリの構造が明確になりました。今後の学習に活かしたいポイントは以下の通りです。

日本語での思考: コードを書く前に、日本語で要件を整理する作業は非常に有効です。これにより、複雑なクエリでも構造を把握しやすくなります。
SQLの復習: 応用的なクエリ作成には、SQLの基本を再確認することが重要です。特に、JOINやGROUP BY、HAVINGの使い方をしっかりと理解する必要があります。
Railsの活用: ActiveRecordを活用することで、複雑なクエリも簡潔に記述できます。

今後は、N+1問題のパフォーマンス改善や、モデルファイルに記述など、さらに深掘りして学んでいきたいと考えています。SQLとRailsのスキルをさらに向上させていきたいと思います。