[Rails] Active Record Query（SQL Query & Model 資料查詢）

keywords: `SQL Query`, `scope`, `enum`, `find_or_create_by`

Active Record Query Interface @ RailsGuides

TL;DR

Post.where('is_published = ? AND publish_at <= ?', true, Time.zone.now)

取得資料（Retrieving Objects from the Database）

取得單一物件（Retrieving a Single Object）

keywords: `find`, `find_by`, `where`, `first`, `last`, `take`

# find: 透過 Primary Key 搜尋資料
product = Product.find(10)
products = Product.find([1, 10])      # 等同於 Client.find(1, 10)

# find_by: 根據欄位找出第一筆符合的資料
product = Product.find_by name: 'car'    # 找出第一筆欄位 name 中值為 car 的資料
product = Product.where(name: 'car').take   # 和上面的寫法相同

# first: 根據 id 排序，取得第一筆資料
product = Product.first            # 根據 id 排序取得第一筆資料
products = Product.first(3)          # 根據 id 排序取得前三筆資料

# last：根據 id 排序，取得最後一筆資料
product = Product.last            # 根據 id 排序取得最後一筆資料
products = Product.last(3)          # 根據 id 排序取得倒數後三筆資料

# take: 取得資料
product = Product.take            # 取得一筆 Product 的資料
products = Product.take(2)          # 取得兩筆 Product 的資料

# 其他
Candidate.count                                  # 計算資料筆數
Candidate.average(:age).to_f                     # 根據某資料平均
Candidate.sum(:age)                              # 根據某資料加總
Candidate.maximum(:age)                          # 取的最大值
Candidate.minimum(:age)                          # 取的最小值

分批取得多筆資料（Retrieving Multiple Objects in Batches）

keywords: `find_each`, `find_in_batches`

在取得多筆資料時我們很容易會使用：

Products.all.each do |product|
    #...
end

但這樣的做法，會一次將整個 table 的資料提取出來，並存放在記憶體中，因此這個做在當資料量很龐大時，很有可能會超過記憶體的負荷。

Rails 提供兩種方式來改善記憶體潰乏的問題，這兩種方式都對記憶體的使用更為友善，分別是 find_each 和 find_in_batches。

條件篩選（Conditions）

基本的條件篩選

keywords: `where`

# !!非常不建議這樣寫!!
# 使用純字串篩選（Pure String Conditions）
Client.where("orders_count = '2'")                # 非常不建議這樣寫
Client.where("first_name LIKE '%#{params[:first_name]}%'")    # 非常不建議這樣寫
Client.where("orders_count = #{params[:orders]}")          # 非常不建議這樣寫

# 使用 ? （Array Conditions）
# Active Record 會把第一個參數當作條件，後面的參數則會分別取代掉前面的 (?)
Client.where("orders_count = ?", params[:orders])              # 單一條件
Client.where("orders_count = ? AND locked = ?", params[:orders], false)    # 多個條件

# 使用 Symbol 定義變數，再透過 Hash 給值（placeholder conditions）
Client.where("created_at >= :start_date AND created_at <= :end_date",
  {start_date: params[:start_date], end_date: params[:end_date]})

# 使用 Hash
Client.where(locked: true)
Client.where('locked' => true)
Client.where(status: :active)      # 錯誤寫法，Hash 的值不能是 Symbol

注意： > 使用單純的字串來定義條件很容易陷入 SQL injection 的危險中。 > 千萬不要把「參數」直接帶入條件篩選的字串內！

因為參數的安全性，把變數直接帶到條件式中，等同於直接把使用者輸入的內容如實的帶入資料庫查詢中，因此即使使用者輸入非法字元也不會被跳脫掉，這將使得你的資料庫面臨危險。

搜尋功能

ILIKE 是 postgreSQL 的擴充功能，表示針對字串進行 insensitive（忽略大小寫）的比較。

# 非常不建議這樣寫
App::Completion.joins(:personal_info).where('name ILIKE ? OR country ILIKE ? OR city ILIKE ?', "%#{search}%", "%#{search}%", "%#{search}%")

# 非常不建議這樣寫
App::Completion.joins(:personal_info).where("name ILIKE '%tester%'").all

其他條件篩選

keywords: `BETWEEN … AND …`, `IN`, `NOT`

# 使用 .. （Range Conditions）：BETWEEN ... AND ...
Client.where(created_at: (Time.now.midnight - 1.day)..Time.now.midnight)
Client.where(created_at: Date.parse('2018-01-01')..Date.parse('2019-01-01'))

# 使用 [] （Subset Conditions）：IN
Client.where(orders_count: [1,3,5])

# 使用 NOT
Client.where.not(locked: true)
Client.where.not(payment_type: ['GAME', 'COST'])

資料排序（Ordering）

Client.order(:created_at)      # 等同於 Client.order("created_at")

Client.order(created_at: :desc)    # 等同於 Client.order("created_at DESC")
Client.order(created_at: :asc)    # 等同於 Client.order("created_at ASC")

Client.order(:orders_count, created_at: :desc)
Client.order(orders_count: :asc, created_at: :desc)
              # 等同於 Client.order("orders_count ASC, created_at DESC")
              # 等同於 Client.order("orders_count ASC", "created_at DESC")

# 使用 order chain
Client.order("orders_count ASC").order("created_at DESC")

選擇特定欄位（Selecting Specific Fields）

keywords: `SELECT`

預設的情況下，使用 Model.find 來選擇資料時，會透過 select * 來選擇所有欄位，如果只想撈出某些欄位呈現，可以使用 select 方法：

# 只選擇 viewable_by 和 locked 這兩個欄位
Client.select("viewable_by, locked")
Order.select("date(created_at) as ordered_date, sum(price) as total_price")

# 只選擇 name 欄位，而且只保留獨特值
Client.select(:name).distinct          # SELECT DISTINCT name FROM clients

使用 select 的時候要特別留意，因為表示這個 model 物件只會包含所選擇的欄位。

設定限制數量（limit）和從哪個開始取（offset）

keywords: `LIMIT`, `OFFSET`

透過 limit 可以限制要取出來的資料數目；offset 則可以指定要從哪一筆資料開始算起。

Client.limit(5).offset(30)    # 從第 31 筆開始取 5 筆資料

分群（Group）

keywords:`GROUP BY`

Order.select("date(created_at) as ordered_date, sum(price) as total_price").group("date(created_at)")

大量取出關連資料（Eager Loading Associations）

keywords: `includes`

Eager Loading Associations @ RailsGuides

N + 1 Queries Problem and Solution

當我們執行下面的程式時，我們只是想要找出 10 篇貼文的使用者（user）名稱：

# Post belongs_to User
posts = Post.limit(10)

posts.map(&:user)    # 會發動很多次的 query 去找關連的使用者

然而，上面的程式碼會執行：

1 （找出 10 筆貼文） + 10（每個貼文都去找關連它的使用者） = 總共 11 次查詢

這樣的問題被稱作 n + 1 queries problem。

要解決這樣的問題，在 Active Record 中可以讓你事先定義即將要使用到的關連，透過 includes 這個方法，Active Record 將會確保所有相關連的欄位被使用最少次的 queries 來載入：

# Post belongs_to User
# 同樣的內容，但這次多了 includes

posts = Post.includes(:user).limit(10)    # 發動兩次 query

posts.map(&:user)              # 不需要在 query

這次只會 query 兩次，而非 11 次：

SELECT * FROM posts LIMIT 10
SELECT users.* FROM users
  WHERE (user.id IN (1,2,3,4,5,6,7,8,9,10))

一次載入多個欄位（Eager Loading Multiple Associations）

Active Record 讓你可以在 includes 方法中，代入陣列、hash 或槽狀的 hash/array 當作參數，來在單一個 Model.find 中一次載入許多的關連：

# 先把和 Articles 有關連的 Category 和 Comments Model 都載入
Article.includes(:category, :comments)

# 這將會找到 id 為 1 的 category instance 並載入所有相關連的 Article，
# 以及和 Article 有關的 Tag 和 Comment
# 以及所有和 Comment 有關連的 Guest
Category.includes(articles: [{ comments: :guest }, :tags]).find(1)

和 join 的差別

雖然 includes 這個方法可以和 joins 一樣定義篩選的條件，但比較推薦的方式還是使用 joins，因為如果是透過 includes 來查詢的話，即時 Article 的 Comments 沒有資料仍然會載入；但如果是使用 joins 的話，join 的條件必須要先符合，否則沒有結果會回傳。

Scope

在 Rails 中 scope 可以讓你定義一些常會使到的 queries：

# define scope in MODEL

# 基本的 scope
class Article < ApplicationRecord
  scope :published, -> { where(published: true) }
end

# 在 scope 中帶入參數
class Candidate < ApplicationRecord
  scope :created_before, ->(time) { where("created_at < ?", time) }
  scope :limit_age, -> (age) { where("age > #{age}") }
  scope :limit_votes, -> (p) { where(["votes > ?", p]) }            # 兩種寫法都可以
end

# 定義條件
class Article < ApplicationRecord
  scope :created_before, ->(time) { where("created_at < ?", time) if time.present? }
end

# 在 scope 中可以帶入另一個 scope
class Candidate
  # scope 中可以代入另一個 scope
  scope :available, -> { limit_age(30).where('votes > 3') }
end

在 Controller 中只需使用：

# use scope in CONTROLLER

# 基本的使用
Article.published # => [published articles]

# 帶入參數
Article.created_before(Time.zone.now)

Default Scope and Unscope

keywords: `default_scope`, `unscoped`

在 Model 中我們也可以定義 default scope ，如果所有經過這個 model 內 query 都會套用到這個 default_scope：

# define default scope in model
class Client < ApplicationRecord
  default_scope { where("removed_at IS NULL") }
  default_scope { order(created_at: :desc) }
  default_scope { order('created_at DESC') }
end

default_scope 會在 creating/building 資料的時候套用，但不會在 updating 的時候呼叫到。

如果基於某些理由，你不希望套用 scope 時，可以使用 unscoped 這個方法，如此原本的 default scope 將不會被套用到：

# in CONTROLLER
Client.unscoped.load

Scope & Class Method

實際上 scope 的用法是透過定義 Class Method，因此上面 scope 的寫法等同於：

# 等同於 scope 的寫法
class Article < ApplicationRecord
  def self.published
    where(published: true)
  end
end

class Article < ApplicationRecord
  def self.created_before(time)
    where("created_at < ?", time)
  end
end

兩者不同之處在於，使用 scope 總是會回傳 ActiveRecord::Relation 物件，當篩選的條件不存在時，會回傳 false ，但是透過 class method 則是會回傳 nil。

Enum

keywords: `enum`

class Book < ApplicationRecord
  enum availability: [:available, :unavailable]
end

找到或建立新物件（Find or Build a New Object）

keywords: `find_or_create_by`

透過 find_or_create_by 方法可以檢驗一筆紀錄中某個屬性是否存在，如果存在則讀取；如果不存在的話，那麼就建立它：

Client.find_or_create_by(first_name: 'Andy')

可用方法參考

find
create_with
distinct
eager_load
extending
from
group
having
includes
joins
left_outer_joins
limit
lock
none
offset
order
preload
readonly
references
reorder
reverse_order
select
where

範例程式碼

處理時間搜尋

  def record
    @transactions = Transaction.all

    where_condition = {}

    arel_update_at = Transaction.arel_table[:updated_at]

    if params[:start_at].present?
      start_at_condition = arel_update_at.gteq(Date.parse(params[:start_at]).beginning_of_day)
    end

    if params[:end_at].present?
      end_at_condition = arel_update_at.lteq(Date.parse(params[:end_at]).end_of_day)
    end

    if params[:status].present?
      where_condition[:status] = params[:status]
    end

    if params[:payment_type].present?
      where_condition[:payment_type] = params[:payment_type]
    end

    @transactions = Transaction.where(start_at_condition).where(end_at_condition).where(where_condition)
  end

參考

Active Record Query Interface @ RailsGuides
Rails Active Record Basic - Model

keywords: SQL Query, scope, enum, find_or_create_by​

TL;DR​

目錄​

取得資料（Retrieving Objects from the Database）​

取得單一物件（Retrieving a Single Object）​

keywords: find, find_by, where, first, last, take​

分批取得多筆資料（Retrieving Multiple Objects in Batches）​

keywords: find_each, find_in_batches​

條件篩選（Conditions）​

基本的條件篩選​

keywords: where​

搜尋功能​

其他條件篩選​

keywords: BETWEEN … AND …, IN, NOT​

資料排序（Ordering）​

選擇特定欄位（Selecting Specific Fields）​

keywords: SELECT​

設定限制數量（limit）和從哪個開始取（offset）​

keywords: LIMIT, OFFSET​

分群（Group）​

keywords:GROUP BY​

大量取出關連資料（Eager Loading Associations）​

keywords: includes​

N + 1 Queries Problem and Solution​

一次載入多個欄位（Eager Loading Multiple Associations）​

和 join 的差別​

Scope​

Default Scope and Unscope​

keywords: default_scope, unscoped​

Scope & Class Method​

Enum​

keywords: enum​

找到或建立新物件（Find or Build a New Object）​

keywords: find_or_create_by​

可用方法參考​

範例程式碼​

處理時間搜尋​

參考​