擷取常用的methods
Pluck
使用pluck 查詢想要的欄位資料，值已陣列回傳，值的類別同欄位型別(如果可以)
也可以傳入sql 指令，例如：Distinct
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Person.all.map(&:name)
# equal to
Person.pluck(:name)
# SELECT people.name FROM people
# => ["A", "B", "C"]
Person.pluck(:id, :name)
# SELECT people.id, people.name FROM people
# => [[1, 'David'], [2, 'Jeremy'], [3, 'Jose']]
Person.pluck('DISTINCT role')
# SELECT DISTINCT role FROM people
# => ['admin', 'member', 'guest']
Person.where(age: 21).limit(5).pluck(:id)
# SELECT people.id FROM people WHERE people.age = 21 LIMIT 5
# => [2, 3]
Person.pluck('DATEDIFF(updated_at, created_at)')
# SELECT DATEDIFF(updated_at, created_at) FROM people
# => ['0', '27761', '173']
與select 的差別在於，select 會取出model object，而pluck 只取出module value
使用Benchmark 測試一下兩者的執行時間
1
2
3
4
5
6
7
puts Benchmark.measure { User.select(:id).to_a }
# User Load (0.6ms) SELECT "users"."id" FROM "users"
# 0.010000 0.000000 0.010000 ( 0.014949)
puts Benchmark.measure { User.pluck(:id) }
# (0.8ms) SELECT "users"."id" FROM "users"
# 0.000000 0.000000 0.000000 ( 0.004965)
相較之下，pluck 的執行速度會比select 快
是因為select 還要把結果轉model object，但是pluck 單純把結果轉array
結論：如果只要單純取欄位的資料，請選擇pluck
Count
Count the records
1
2
3
4
5
6
7
8
9
10
11
Person.count
# => the total count of all people
Person.count(:age)
# => returns the total count of all people whose age is present in database
Person.count(:all)
# => performs a COUNT(*) (:all is an alias for '*')
Person.distinct.count(:age)
# => counts the number of different age values
使用group 會回傳Hash 值，並以key = column value, value = count amount 的方式表示
1
2
Person.group(:city).count
# => { 'Rome' => 5, 'Paris' => 3 }
如果group 多個欄位，那key 就是欄位的組合
1
2
3
Article.group(:status, :category).count
# => {["draft", "business"]=>10, ["draft", "technology"]=>4,
["published", "business"]=>0, ["published", "technology"]=>2}
使用select 則是回傳select 後的筆數
並非所有正確的select 都會產生正確的count。會根據database 而有所不同。
1
2
Person.select(:age).count
# => counts the number of different age values
Maximum/Minimum
Maximum 找出查詢欄位的最大值，查詢結果會根據欄位型別產生對應的物件類別，無資料則回傳nil
1
Person.maximum(:age) # => 93
同上，但是Minimum 查詢最小值
1
Person.minimum(:age) # => 7
Sum/Average
Sum 計算查詢欄位的總和，無資料則回傳0
1
Person.sum(:age) # => 4562
Average 計算欄位的平均值，無資料則回傳nil
1
Person.average(:age) # => 35.8
Reference