LoginSignup
31
29

More than 5 years have passed since last update.

@coe401_(Misaki Shioi)

Progate SQLコースに登場するSQL文をActiveRecordに翻訳してみました

Last updated at Posted at 2018-09-02

はじめに

こんにちは！
この度、SQL全然分からない ＼(^o^)／状態を克服しようと、みんな大好きProgateのSQLコースにて勉強を始めましたWebエンジニア一年生（三十超え）です。
しかしながら、普段自分が仕事で使用しているRuby on Railsには、ActiveRecordという強力なORマッパーが標準装備されております。
そこで、今回の勉強をRailsでの開発に生かすことができるよう、ProgateのSQLコースに登場する構文をActiveRecordに翻訳するという試みを行ってみました。

※※※※※ 著作権に配慮し、テーブルの中身等は差し替えております ※※※※※

準備

今回の実行環境はこちら。
- Ruby 2.4.0
- Rails 5.2.1
- SQLite 3.8.2

先にこんなテーブルを用意しました。

db/scheme.rb
ActiveRecord::Schema.define(version: 2018_09_01_071344) do
  create_table "books", force: :cascade do |t|
    t.string "title"
    t.integer "price"
    t.string "author"
    t.string "genre"
    t.date "published_at"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end
end

ここに、こちらのseedデータを投入。

db/seeds.rb
100.times do |n|
  title = Faker::Book.title,
  author = Faker::Book.author,
  price = [nil, 1000, 2000, 3000].sample,
  genre = Faker::Book.genre,
  published_at = Faker::Date.between(1.years.ago, Date.today)

  Book.create!(
    title: title,
    author: author,
    price: price,
    genre: genre,
    published_at: published_at
  )
end

準備ができたところで、早速翻訳していきます！

select

booksテーブルからtitleカラムを検索

# SQL
SELECT title FROM books;
# ActiveRecord
Book.select(:title)

# => Book Load (0.3ms)  SELECT  "books"."title" FROM "books" LIMIT ?  [["LIMIT", 11]]

booksテーブルからtitleカラムとpriceカラムを検索

# SQL
SELECT title, price FROM books;
# ActiveRecord
Book.select(:title, :price)

# => Book Load (0.3ms)  SELECT  "books"."title", "books"."price" FROM "books" LIMIT ?  [["LIMIT", 11]]

Bookテーブルから全レコードを検索

# SQL
SELECT * FROM books;
# ActiveRecord
Book.all

# => Book Load (0.4ms)  SELECT  "books".* FROM "books" LIMIT ?  [["LIMIT", 11]]

基本のwhere（条件検索）

booksテーブルからgenreが「Classic」のレコードを検索

# SQL
SELECT * FROM books WHERE genre = "Classic";
# ActiveRecord
Book.where(genre: "Classic")

# => Book Load (0.5ms)  SELECT  "books".* FROM "books" WHERE "books"."genre" = ? LIMIT ?  [["genre", "Classic"], ["LIMIT", 11]]

booksテーブルからpriceが「1000」のレコードを検索

# SQL
SELECT * FROM books WHERE price = 1000;
# ActiveRecord
Book.where(price: 1000)

# => Book Load (0.5ms)  SELECT  "books".* FROM "books" WHERE "books"."price" = ? LIMIT ?  [["price", 1000], ["LIMIT", 11]]

booksテーブルからpublished_atが「2018-07-01」のレコードを検索

# SQL
SELECT * FROM books WHERE published_at = "2018-07-01";
# ActiveRecord
Book.where(published_at: Date.new(2018, 7, 1))

# => Book Load (0.5ms)  SELECT  "books".* FROM "books" WHERE "books"."published_at" = ? LIMIT ?  [["published_at", "2018-07-01"], ["LIMIT", 11]]

booksテーブルからpriceが「2000」以上のレコードを検索

# SQL
SELECT * FROM books WHERE price >= 2000;
# ActiveRecord
Book.where('price >= ?', 2000)
# Book.where(price: 2000..Float::INFINITY) という表現もあるそう。

# => Book Load (0.5ms)  SELECT  "books".* FROM "books" WHERE (price >= 2000) LIMIT ?  [["LIMIT", 11]]

booksテーブルからpublished_atが「2018-07-01」以前のレコードを検索

# SQL
SELECT * FROM books WHERE published_at <= "2018-07-01";
# ActiveRecord
Book.where('published_at <= ?', "Date.new(2018, 7, 1)")

# => Book Load (0.5ms)  SELECT  "books".* FROM "books" WHERE (published_at <= 'Date.new(2018, 7, 1)') LIMIT ?  [["LIMIT", 11]]

ちょっと複雑なwhere（条件検索）

booksテーブルからtitleに「Time」を含むレコードを検索

# SQL
SELECT * FROM books WHERE title LIKE "%Time%";
# ActiveRecord
Book.where('title LIKE ?', "%Time%")

# => Book Load (0.5ms)  SELECT  "books".* FROM "books" WHERE (title LIKE '%Time%') LIMIT ?  [["LIMIT", 11]]

# booksテーブルからtitleが「Time」で始まるレコード
# SQL
SELECT * FROM books WHERE title LIKE "Time%";
# ActiveRecord
Book.where('title LIKE ?', "Time%")

# => Book Load (0.5ms)  SELECT  "books".* FROM "books" WHERE (title LIKE 'Time%') LIMIT ?  [["LIMIT", 11]]

# booksテーブルからtitleカラムが「Time」で終わるレコード
# SQL
SELECT * FROM books WHERE title LIKE "%Time";
# ActiveRecord
Book.where('title LIKE ?', "%Time")

# => Book Load (0.5ms)  SELECT  "books".* FROM "books" WHERE (title LIKE '%Time') LIMIT ?  [["LIMIT", 11]]

ただし、前方一致と後方一致については、SQLは発行されているのですがデータは空の状態でした。なぜだ…

booksテーブルからauthorが「Raven King」以外のレコードを検索

# SQL
SELECT * FROM books WHERE NOT author = "Raven King";
# ActiveRecord
Book.where.not(author: "Raven King")

# => Book Load (0.5ms)  SELECT  "books".* FROM "books" WHERE "books"."author" != ? LIMIT ?  [["author", "Raven King"], ["LIMIT", 11]]

booksテーブルからtitleに「Time」を含まないレコードを検索

# SQL
SELECT * FROM books WHERE NOT title LIKE "%Time%";
# ActiveRecord
Book.where.not('title LIKE ?', "%Time%")

# => Book Load (0.5ms)  SELECT  "books".* FROM "books" WHERE NOT (title LIKE '%Time%') LIMIT ?  [["LIMIT", 11]]

booksテーブルからpriceが「null」になっているレコードを検索

# SQL
SELECT * FROM books WHERE price IS NULL;
# ActiveRecord
Book.where(price: nil)

# => Book Load (0.4ms)  SELECT  "books".* FROM "books" WHERE "books"."price" IS NULL LIMIT ?  [["LIMIT", 11]]

booksテーブルからpriceが「null」になっていないレコードを検索

# SQL
SELECT * FROM books WHERE price IS NOT NULL;
# ActiveRecord
Book.where.not(price: nil)

# => Book Load (0.4ms)  SELECT  "books".* FROM "books" WHERE "books"."price" IS NOT NULL LIMIT ?  [["LIMIT", 11]]

booksテーブルからgenreが「Classic」かつauthorが「Raven King」のレコードを検索

# SQL
SELECT * FROM books WHERE genre = "Classic" AND author = "Raven King"
# ActiveRecord
Book.where(genre: "Classic", author: "Raven King")

# => Book Load (0.5ms)  SELECT  "books".* FROM "books" WHERE "books"."genre" = ? AND "books"."author" = ? LIMIT ?  [["genre", "Classic"], ["author", "Raven King"], ["LIMIT", 11]]

booksテーブルからgenreが「Classic」またはauthorが「Raven King」のレコードを検索

# SQL
SELECT * FROM books WHERE genre = "Classic" OR author = "Raven King";
# ActiveRecord
Book.where(genre: "Classic").or(Book.where(author: "Raven King"))

# => Book Load (0.5ms)  SELECT  "books".* FROM "books" WHERE ("books"."genre" = ? OR "books"."author" = ?) LIMIT ?  [["genre", "Classic"], ["author", "Raven King"], ["LIMIT", 11]]

# 今回はシンプルな構文ですが、条件が複雑化して来た際、結合する順番によっては意図していない結果が表示されることがあるそう。

order(並び替え)

booksテーブルのレコードをpublished_atを基準に降順並び替え

# SQL
SELECT * FROM books ORDER BY published_at DESC;
# ActiveRecord
Book.order(published_at: :desc)
# => もしくは Book.order(:published_at).reverse_order

# => Book Load (1.5ms)  SELECT  "books".* FROM "books" ORDER BY "books"."published_at" DESC LIMIT ?  [["LIMIT", 11]]
# ちなみに昇順並び替えの場合はBook.order(:published_at)でOK

booksテーブルのうちauthorが「Raven King」のレコードを、priceを基準に昇順並び替え

# SQL
SELECT * FROM books WHERE author = "Raven King" ORDER BY price ASC;
# ActiveRecord
Book.where(author: "Raven King").order(:price)

# => Book Load (0.5ms)  SELECT  "books".* FROM "books" WHERE "books"."author" = ? ORDER BY "books"."price" ASC LIMIT ?  [["author", "Raven King"], ["LIMIT", 11]]

limit（絞り込み）

booksテーブルからレコードを5件取得

# SQL
SELECT * FROM books LIMIT 5;
# ActiveRecord
Book.limit(5)

# => Book Load (0.3ms)  SELECT  "books".* FROM "books" LIMIT ?  [["LIMIT", 5]]

booksテーブルからauthorが「Raven King」のレコードを5件取得

# SQL
SELECT * FROM books WHERE author = "Raven King" LIMIT 5;
# ActiveRecord
Book.where(author: "Raven King").limit(5)

# => Book Load (0.4ms)  SELECT  "books".* FROM "books" WHERE "books"."author" = ? LIMIT ?  [["author", "Raven King"], ["LIMIT", 5]]

おさらい

booksテーブルからpublished_atが「201８-0７-01」以前のレコードを取得

# SQL
SELECT * FROM books WHERE published_at <= "2018-0７-01";
# ActiveRecord
Book.where('published_at <= ?', "Date.new(2018, ７, 1)")

# => Book Load (0.5ms)  SELECT  "books".* FROM "books" WHERE (published_at <= 'Date.new(2018, ７, 1)') LIMIT ?  [["LIMIT", 11]]

booksテーブルからtitleに「Time」を含むレコードを取得

# SQL
SELECT * FROM books WHERE title LIKE "%Time%";
# ActiveRecord
Book.where('title LIKE ?', "%Time%")

# => Book Load (0.8ms)  SELECT  "books".* FROM "books" WHERE (title LIKE '%Time%') LIMIT ?  [["LIMIT", 11]]

booksテーブルからauthorが「Reven King」以外のレコードを取得

# SQL
SELECT * FROM books WHERE NOT author =  "Raven King";
# ActiveRecord
Book.where.not(author: "Raven King")

# => Book Load (0.3ms)  SELECT  "books".* FROM "books" WHERE "books"."author" != ? LIMIT ?  [["author", "Raven King"], ["LIMIT", 11]]

booksテーブルからpriceが「null」のレコードを取得

# SQL
SELECT * FROM books WHERE price IS NULL;
# ActiveRecord
Book.where(price: nil)

# => Book Load (0.4ms)  SELECT  "books".* FROM "books" WHERE "books"."price" IS NULL LIMIT ?  [["LIMIT", 11]]

booksテーブルからgenreが「Classic」かつauthorが「Raven King」のレコードを取得

# SQL
SELECT * FROM books WHERE genre = "Classic" AND author = "Raven King";
# ActiveRecord
Book.where(genre: "Classic", author: "Raven King")

# => Book Load (0.3ms)  SELECT  "books".* FROM "books" WHERE "books"."genre" = ? AND "books"."author" = ? LIMIT ?  [["genre", "Classic"], ["author", "Raven King"], ["LIMIT", 11]]

booksテーブルをpriceを基準に降順並び替えし、最初の5件のレコードを取得

# SQL
SELECT * FROM books ORDER BY price DESC LIMIT 5;
# ActiveRecord
Book.order(published_at: :desc).limit(5)

# => Book Load (1.2ms)  SELECT  "books".* FROM "books" ORDER BY "books"."published_at" DESC LIMIT ?  [["LIMIT", 5]]

最後に

調べてみると、ほとんどの条件がActiveRecordで用意されていることが分かりました。
whereメソッドを使用して生のSQLを記述することも可能ですが、Railsが最初から用意してくれているメソッドについては、可能な限りレールに乗っていきたいものです。
お疲れ様でした！

参考

この記事を読んでいただいた方へ

「この記事のここがけしからん！」「もっと良い方法があるよ〜」など、ご感想をいただけましたら、お手数ですがコメント欄にてご指摘・アドバイスをいただけると嬉しいです。
どうぞよろしくお願いいたします！

31
29
6

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
31
29