LoginSignup
2
1

More than 3 years have passed since last update.

@sakakidai(榊 大)

Rails SQLでデータを絞り込む

Last updated at Posted at 2019-07-11

はじめに

自分のポートフォリオ作成時に迷ったので書きました。
ユーザーを投稿の多い順番で並び替えたり出来るようになりました。

SQLの基礎はこれで勉強しました↓
SQLの基本を覚える【初心者向け】-Qiita
テーブル結合についての備忘録 その2-Qiita

Rails ActiveRecordのSQLはこれで勉強しました↓
Rails における内部結合、外部結合まとめ-Qiita
Rails ActiveRecord/SQL 小技集-Qiita

前提条件
deviseでUserモデルの作成済み
scaffoldでBlogモデルの作成済み
BlogとUserは１対多
bootstrapでちょっとデザインを付けてます。
seeds.rbでサンプルデータを作成しています。↓

seeds.rb

if Rails.env == "development"
  (1..10).each do |i|
    User.create!(email: "test#{i}@example.com", password: "password", password_confirmation: "password")
  end

  (1..5).each do |i|
    user = User.find(1)
    user.blogs.create!(title: "test#{i}", content: "test#{i}. test#{i}. test#{i}.")
  end

  (1..3).each do |i|
    user = User.find(2)
    user.blogs.create!(title: "test#{i}", content: "test#{i}. test#{i}. test#{i}.")
  end

  user = User.find(3)
  user.blogs.create!(title: "test1", content: "test1. test1. test1.")
end

allメソッド

SQLのWHEREの条件なしのため、idの１番から順番に全件取り出す。

users/index.html.erb

<h1>ユーザー一覧</h1>
<div class="row">
  <% @users.each do |user| %>
    <div class='col-3'>
      <div class="card mb-5">
        <h1 class='text-center'>ID : <%= user.id %></h1>
        <div class="card-body">
          <p class="card-text"><%= user.email %></p>
          <p class="card-text">書いた記事の数<%= user.blogs.count %></p>
        </div>
      </div>
    </div>
  <% end %>
</div>
controllers/users_controller.rb

class UsersController < ApplicationController
  def index
    @users = User.all
  end
end

アクセスした時のログ↓


User Load (0.3ms)  SELECT "users".* FROM "users"

ページにアクセスするとこんな感じで表示されます。↓

スクリーンショット 2019-07-11 16.19.59.png

orderメソッド

SQLのORDER BY節を作ります。
creted_atの降順(desc)で並び替えてます。

controllers/users_controller.rb

class UsersController < ApplicationController
  def index
    @users = User.order(created_at: :desc)
  end
end

アクセスした時のログ↓


User Load (1.1ms)  SELECT "users".* FROM "users" ORDER BY "users"."created_at" DESC

ページにアクセスするとこんな感じで表示されます。↓

スクリーンショット 2019-07-11 20.54.06.png

joinsメソッド

SQLのJOIN節（INNER JOIN)をつくる。
引数のモデルと内部結合出来るみたいです。

controllers/users_controller.rb

class UsersController < ApplicationController
  def index
    @users = User.order(created_at: :desc)
    @writer = User.joins(:blogs) #追加しました。
  end
end

アクセスした時のログ↓


User Load (1.5ms)  SELECT "users".* FROM "users" INNER JOIN "blogs" ON "blogs"."user_id" = "users"."id"
users/index.html.erb

<h1>ユーザー一覧</h1>
<div class="row">
  <% @users.each do |user| %>
    <div class='col-3'>
      <div class="card mb-5">
        <h1 class='text-center'>ID : <%= user.id %></h1>
        <div class="card-body">
          <p class="card-text"><%= user.email %></p>
          <p class="card-text">書いた記事の数<%= user.blogs.count %></p>
        </div>
      </div>
    </div>
  <% end %>
</div>

<!-- -----------ここから下を追加しました。----------------- -->
<h1>ブログを書いたユーザー</h1>
<div class="row">
  <% @writer.each do |user| %>
    <div class='col-3'>
      <div class="card mb-5">
        <h1 class='text-center'>ID : <%= user.id %></h1>
        <div class="card-body">
          <p class="card-text"><%= user.email %></p>
          <p class="card-text">書いた記事の数<%= user.blogs.count %></p>
        </div>
      </div>
    </div>
  <% end %>
</div>

ページにアクセスするとこんな感じで表示されます。↓

スクリーンショット 2019-07-11 21.51.38.png

ユーザーが持っているブログの数だけユーザーが重複してます。

distinctメソッド

distinctメソッドで重複をなくします。
SQLのSELECT節にDISTINCTを追加出来ます。

controllers/users_controller.rb

class UsersController < ApplicationController
  def index
    @users = User.order(created_at: :desc)
    @writer = User.joins(:blogs).distinct
  end
end

アクセスした時のログ↓


User Load (3.3ms)  SELECT DISTINCT "users".* FROM "users" INNER JOIN "blogs" ON "blogs"."user_id" = "users"."id"

groupメソッド

また、groupメソッドでも同じ結果になります。
こっちは引数の同じ値同士をグループ化します。
SQLのGROUP BY節をつくります。

controllers/users_controller.rb

class UsersController < ApplicationController
  def index
    @users = User.order(created_at: :desc)
    @writer = User.joins(:blogs).group(:user_id)
  end
end

アクセスした時のログ↓


User Load (0.7ms)  SELECT "users".* FROM "users" INNER JOIN "blogs" ON "blogs"."user_id" = "users"."id" GROUP BY "user_id"

スクリーンショット 2019-07-11 22.09.40.png

これでブログを書いたユーザーの重複がなくなりました。

書いた記事の数が現状たまたまIDが若いほど増えて行ってしまっていて並び変わったか分かりづらくなってしまったのでID = 1のユーザーの記事の数とID = 2のユーザーの記事の数を入れ変えました。すいません。

orderメソッドでSQLを使う

次にユーザーの並びを記事を書いた数の多い順番に変更します。
orderの引数をSQLで書きます。（この表現あってるかわかんないです）

controllers/users_controller.rb

class UsersController < ApplicationController
  def index
    @users = User.order(created_at: :desc)
    @writer = User.joins(:blogs).group(:user_id).order('count(user_id) desc')
  end
end

ログを見るとちゃんとORDER BY count(user_id) descと書かれています。（ただ警告が出てます。後で説明します。）

アクセスした時のログ↓


DEPRECATION WARNING: Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s): "count(user_id) desc". Non-attribute arguments will be disallowed in Rails 6.0. This method should not be called with user-provided values, such as request parameters or model attributes. Known-safe values can be passed by wrapping them in Arel.sql(). (called from <main> at (pry):4)
  User Load (0.8ms)  SELECT "users".* FROM "users" INNER JOIN "blogs" ON "blogs"."user_id" = "users"."id" GROUP BY "user_id" ORDER BY count(user_id) desc

スクリーンショット 2019-07-11 22.53.20.png

ビューも反映されて　user_id = 1　と　user_id = 2　のユーザーの順番が入れ替わってます。

警告について

参考リンク↓
Arel.sqlを付けるだけじゃダメ!? Railsで"Dangerous query method …”の警告が出たときの対応方法

今回のケースではorderのSQL文は入力される値でないため、SQLインジェクションの危険性はないと考え、Arel.sqlを単純につけました。

controllers/users_controller.rb

class UsersController < ApplicationController
  def index
    @users = User.order(created_at: :desc)
    @writer = User.joins(:blogs).group(:user_id).order(Arel.sql('count(user_id) desc'))
  end
end

アクセスした時のログ↓


User Load (2.1ms)  SELECT "users".* FROM "users" INNER JOIN "blogs" ON "blogs"."user_id" = "users"."id" GROUP BY "user_id" ORDER BY count(user_id) desc

警告がきえました。
Arel.sql自体はSQLインジェクションに対して安全を確保するものではなく、安全を確認しましたという宣言みたいなものみたいです。

ほかにもいろいろ試してみる

limitメソッド

SQLのLIMIT節をつくる。
数を制限したいときに便利。

コンソール↓（コンソールに変更しました）


[6] pry(main)> User.order(created_at: :desc).limit(5)
  User Load (0.4ms)  SELECT  "users".* FROM "users" ORDER BY "users"."created_at" DESC LIMIT ?  [["LIMIT", 5]]
=> [#<User id: 10, email: "test10@example.com", created_at: "2019-07-11 13:08:06", updated_at: "2019-07-11 13:08:06">,
 #<User id: 9, email: "test9@example.com", created_at: "2019-07-11 13:08:06", updated_at: "2019-07-11 13:08:06">,
 #<User id: 8, email: "test8@example.com", created_at: "2019-07-11 13:08:06", updated_at: "2019-07-11 13:08:06">,
 #<User id: 7, email: "test7@example.com", created_at: "2019-07-11 13:08:05", updated_at: "2019-07-11 13:08:05">,
 #<User id: 6, email: "test6@example.com", created_at: "2019-07-11 13:08:05", updated_at: "2019-07-11 13:08:05">]
[7] pry(main)>

10件あったデータを５件に絞ることが出来ました。

findメソッド

SQLのWHEREでidのレコードを取得します。
見つからない場合は例外が発生します。


[7] pry(main)> User.find(1)
  User Load (0.3ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
=> #<User id: 1, email: "test1@example.com", created_at: "2019-07-11 13:08:05", updated_at: "2019-07-11 13:08:05">

複数のidを取得することも出来ます。

[8] pry(main)> User.find([1,2,3])
  User Load (0.6ms)  SELECT "users".* FROM "users" WHERE "users"."id" IN (?, ?, ?)  [["id", 1], ["id", 2], ["id", 3]]
=> [#<User id: 1, email: "test1@example.com", created_at: "2019-07-11 13:08:05", updated_at: "2019-07-11 13:08:05">,
 #<User id: 2, email: "test2@example.com", created_at: "2019-07-11 13:08:05", updated_at: "2019-07-11 13:08:05">,
 #<User id: 3, email: "test3@example.com", created_at: "2019-07-11 13:08:05", updated_at: "2019-07-11 13:08:05">]

ちょっと応用でfindでブログを持ったユーザーの情報を取ったりも出来ます。


User.find(User.joins(:blogs).group(:user_id).pluck(:user_id))
   (0.2ms)  SELECT "user_id" FROM "users" INNER JOIN "blogs" ON "blogs"."user_id" = "users"."id" GROUP BY "user_id"
  User Load (0.5ms)  SELECT "users".* FROM "users" WHERE "users"."id" IN (?, ?, ?)  [["id", 1], ["id", 2], ["id", 3]]
=> [#<User id: 1, email: "test1@example.com", created_at: "2019-07-11 13:08:05", updated_at: "2019-07-11 13:08:05">,
 #<User id: 2, email: "test2@example.com", created_at: "2019-07-11 13:08:05", updated_at: "2019-07-11 13:08:05">,
 #<User id: 3, email: "test3@example.com", created_at: "2019-07-11 13:08:05", updated_at: "2019-07-11 13:08:05">]

最後に

まだまだ、調べながら付け加えて行こうと思います。

2
1
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
2
1