Hello, 大家好！

岔題研究完了CollectionProxy 後，接著回頭繼續研究query methods 吧~

今天要研究的主題是Active Record Query Interface，我們先來看看他的介紹：

If you're used to using raw SQL to find database records, then you will generally find that there are better ways to carry out the same operations in Rails. Active Record insulates you from the need to use SQL in most cases.

Active Record will perform queries on the database for you and is compatible with most database systems, including MySQL, MariaDB, PostgreSQL, and SQLite. Regardless of which database system you're using, the Active Record method format will always be the same.

**翻譯蒟蒻：如同先前提過的， Active Record 提供了些黑魔法讓query 使用起來更容易（而不用寫Raw SQL），而黑魔法其中之一就是Query Interface 啦！

Finder methods that return a collection, such as where and group, return an instance of ActiveRecord::Relation. Methods that find a single entity, such as find and first, return a single instance of the model.

**翻譯蒟蒻：query methods 根據方法所屬的層級回傳資料，回傳一筆entity 的為FinderMethods，回傳一個collection 的則是ActiveRecord::QueryMethods

我們挑個方法來試試看：

irb(main):048:0> Post.fifth
  Post Load (0.7ms)  SELECT "posts".* FROM "posts" ORDER BY "posts"."id" ASC LIMIT $1 OFFSET $2  [["LIMIT", 1], ["OFFSET", 4]]
#<Post:0x00007f7a72583088> {
            :id => 5,
        :userId => 1,
         :title => "nesciunt quas odio",
          :body => "ccc",
    :created_at => Sat, 01 Oct 2022 14:49:33 UTC +00:00,
    :updated_at => Sun, 02 Oct 2022 08:44:06 UTC +00:00
}
irb(main):049:0> Post.sixth # 但不知道為什麼只有到fifth... XD
Traceback (most recent call last):
        1: from (irb):49
NoMethodError (undefined method 'sixth' for #<Class:0x00007f7a6c1d02e8>)

irb(main):052:0> Post.third_to_last
  Post Load (0.6ms)  SELECT "posts".* FROM "posts" ORDER BY "posts"."id" DESC LIMIT $1  [["LIMIT", 3]]
  
#<Post:0x00007f7a6c1e6d18> {
            :id => 98,
        :userId => 10,
         :title => "laboriosam dolor voluptates",
          :body => "doloremque ex facilis sit sint culpa\nsoluta assumenda eligendi non ut eius\nsequi ducimus vel quasi\nveritatis est dolores",
    :created_at => Sat, 01 Oct 2022 14:49:33 UTC +00:00,
    :updated_at => Sat, 01 Oct 2022 14:49:33 UTC +00:00
}
irb(main):053:0> Post.fourth_to_last # 倒數的則是只有最後三筆（好無聊的發現= =）
Traceback (most recent call last):
        1: from (irb):53
NoMethodError (undefined method `fourth_to_last' for #<Class:0x00007f7a6c1d02e8>)

我們也來試試QueryMethod 其中的方法：

irb(main):054:0> Post.where(id: 1)
  Post Load (0.4ms)  SELECT "posts".* FROM "posts" WHERE "posts"."id" = $1  [["id", 1]]
[
    [0] #<Post:0x00007f7a6d73ad18> {
                :id => 1,
            :userId => 1,
             :title => "sunt aut facere repellat provident occaecati excepturi optio reprehenderit",
              :body => "ccc",
        :created_at => Sat, 01 Oct 2022 14:49:33 UTC +00:00,
        :updated_at => Sun, 02 Oct 2022 08:44:06 UTC +00:00
    }
]

irb(main):055:0> Post.where(id: 1).class 
Post::ActiveRecord_Relation < ActiveRecord::Relation # 雖然只有一筆資料，但實質上被array 包起來，是個collection

值得一提的是，兩個Methods 都有include（include? vs. includes）的方法，但使用起來卻是完全不同的概念（難怪此前自己常常打錯XD）

FinderMethod 的include?(record)：

Returns true if the relation contains the given record or false otherwise.

No query is performed if the relation is loaded; the given record is compared to the records in memory. If the relation is unloaded, an efficient existence query is performed, as in exists?.

irb(main):073:0> post = Post.first
irb(main):074:0> Post.all.include?(post)
  Post Load (1.3ms)  SELECT "posts".* FROM "posts"
true

# 雖然Post.all 是一個collection，但透過include?(record) 只檢查一筆資料，並回傳true/false，所以算finder method？

# 我們來這麼驗證：
posts = Post.where(userId: 1) # 有10 筆資料

irb(main):091:0> Post.all.include?(posts)
  Post Load (1.1ms)  SELECT "posts".* FROM "posts"
false # ！

而QueryMethod 的includes 則類似joins：

Specify relationships to be included in the result set.

irb(main):093:0> posts = Post.includes(:comments).class
Post::ActiveRecord_Relation < ActiveRecord::Relation

也能透過這方法includes nested relationships：

irb(main):108:0> User.includes(:posts, {posts: :comments})

不過令人費解的是Rails API 文字說hash，但sample 卻是用array，可自己實測也可行？

irb(main):109:0> User.includes(:posts, [posts: :comments])

總之includes 在巢狀關聯下很有用；雖然joins 和includes 的用法有些微不同，如：

irb(main):122:0> User.joins(:posts)
  User Load (0.8ms)  SELECT "users".* FROM "users" INNER JOIN "posts" ON "posts"."userId" = "users"."id"

irb(main):125:0> User.includes(:posts)
  User Load (0.7ms)  SELECT "users".* FROM "users"
  Post Load (0.9ms)  SELECT "posts".* FROM "posts" WHERE "posts"."userId" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)  [["userId", 1], ["userId", 2], ["userId", 3], ["userId", 4], ["userId", 5], ["userId", 6], ["userId", 7], ["userId", 8], ["userId", 9], ["userId", 10]]

但就擇日再研究說明吧！XD

接著我們來觀察一下使用Active Record query methods 時SQL 的query statements:

irb(main):112:0> User.find(1).posts.first
  User Load (0.5ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  Post Load (0.5ms)  SELECT "posts".* FROM "posts" WHERE "posts"."userId" = $1 ORDER BY "posts"."id" ASC LIMIT $2  [["userId", 1], ["LIMIT", 1]]

irb(main):120:0> User.includes(:posts).where(id: 1)
  User Load (0.4ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1  [["id", 1]]
  Post Load (0.3ms)  SELECT "posts".* FROM "posts" WHERE "posts"."userId" = $1  [["userId", 1]]
  
irb(main):133:0> User.joins(:posts).where(id: 1)
  User Load (0.8ms)  SELECT "users".* FROM "users" INNER JOIN "posts" ON "posts"."userId" = "users"."id" WHERE "users"."id" = $1  [["id", 1]]

上述query 的結果是相同的，但因query methods 的語句不同，而有不同卻近似的SQL statements；但不知道大家還記得我們之前在Day 15用過to_sql的方法嗎？

該方法可以將ActiveRecord::Relation 的語句轉成相對應的SQL statements，因此只有後兩句的QueryMethod 回傳的ActiveRecord::Relation 可以使用：

irb(main):169:0> User.find(1).posts.first.class
> class Post < ApplicationRecord 
> 
irb(main):170:0> User.find(1).posts.first.to_sql
Traceback (most recent call last):
        1: from (irb):170
NoMethodError (undefined method 'to_sql' for #<Post:0x00007f7a6d427310>)

irb(main):171:0> User.includes(:posts).where(id: 1).class
> User::ActiveRecord_Relation < ActiveRecord::Relation

irb(main):172:0> User.includes(:posts).where(id: 1).to_sql
"SELECT \"users\".* FROM \"users\" WHERE \"users\".\"id\" = 1"

irb(main):173:0> User.joins(:posts).where(id: 1).class
> User::ActiveRecord_Relation < ActiveRecord::Relation

irb(main):174:0> User.joins(:posts).where(id: 1).to_sql
"SELECT \"users\".* FROM \"users\" INNER JOIN \"posts\" ON \"posts\".\"userId\" = \"users\".\"id\" WHERE \"users\".\"id\" = 1"

如此我們就能得知其對應的SQL statements 了

但今天的任務不能只有這樣，還是要自己試寫成SQL：

SELECT * FROM posts WHERE posts."userId" = 1 LIMIT 1

接著利用先前研究出來的方法（ActiveRecord::Base.connection.exec_query(sql)、Model.find_by_sql(sql)）將SQL statements 丟進去以得到同樣結果：

irb(main):175:0> sql = "SELECT * FROM posts WHERE posts.\"userId\" = 1 LIMIT 1"
"SELECT * FROM posts WHERE posts.\"userId\" = 1 LIMIT 1"
# 注意這裡需要用跳脫字元來加工一下

irb(main):176:0> ActiveRecord::Base.connection.exec_query(sql)
  SQL (0.9ms)  SELECT * FROM posts WHERE posts."userId" = 1 LIMIT 1
  
irb(main):177:0> Post.find_by_sql(sql)
  Post Load (0.8ms)  SELECT * FROM posts WHERE posts."userId" = 1 LIMIT 1

完成~~~

可研究到這邊，被翻爛的書其實還是不太明白Active Record Query Interface提供的便捷性，但今天還是先研究到這就好... 謝謝大家 XD