嗨！大家好~~

被翻爛的書今天要繼續研究Active Record 混用SQL 的語法，昨天停在
利用ActiveRecord::Base.connection.exec_query(sql) 的方式得到ActiveRecord::Result回傳的Hash 結果，而今天要繼續研究第二個方法：Model.find_by_sql(sql)：

> sql = "SELECT email FROM users WHERE id = 1"

> User.find_by_sql(sql)
  User Load (0.7ms)  SELECT email FROM users WHERE id = 1
[
    [0] #<User:0x00007f94196c2fa0> {
           "id" => nil,
        "email" => "Sincere@april.biz"
    }
]

首先查了一下這個方法，也是來自Class: ActiveRecord::Base
RubyDoc reference

下方有提到find_by_sql是個類別方法，後面的params 則可插入SQL statements：

sql - An SQL statement which should return a count query from the database, see the example below.

再看看該方法
.find_by_sql(sql, binds = []) ⇒ Object

Executes a custom SQL query against your database and returns all the results. The results will be returned as an array with columns requested encapsulated as attributes of the model you call this method from. If you call Product.find_by_sql then the results will be returned in a Product object with the attributes you specified in the SQL query.

翻譯蒟蒻：將資料表的欄位data 封裝成array，回傳給呼叫他的Model 作為attributes data。

接著注意到這句：The sql parameter is a full SQL query as a string.
翻譯蒟蒻：後面的params 整句都要是字串形態

但想想如果每個sql params 都要自己拼裝，好像也有點累且容易出錯，不知道有什麼方法偷懶呢？

...

查了一下，Class: ActiveRecord::Relation 內有個to_sql的方法，能將Relation query statement 都轉為SQL query statement，但查到這邊，似乎可以先回想一下query methods 的差異：

Rails Guide Active Record Query Interface

Retrieving Objects from the Database:

To retrieve objects from the database, Active Record provides several finder methods. Each finder method allows you to pass arguments into it to perform certain queries on your database without writing raw SQL.

翻譯蒟蒻：Active Record 提供了finder methods，能將資料從database 抽取出來成為物件如此就不用特別寫raw SQL statement

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.

而Finder methods 分為：

1. where, group 等方法: 回傳一個collection

irb(main):003:0> User.where(id: 1, username: "Bret").class
User::ActiveRecord_Relation < ActiveRecord::Relation

2. find, first 等方法：回傳一筆實體紀錄

irb(main):004:0> User.find_by_name("Leanne Graham").class
  User Load (0.7ms)  SELECT "users".* FROM "users" WHERE "users"."name" = $1 LIMIT $2  [["name", "Leanne Graham"], ["LIMIT", 1]]
class User < ApplicationRecord {
...
}

換句話說，雖然我們能在query 時看到Active Record 轉換成SQL 的statement：
User Load (0.6ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 [["id", 1]]

irb(main):001:0> User.where(id: 1)
  User Load (0.6ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1  [["id", 1]]
[
    [0] #<User:0x00007fc4f5dd13e8> {
                :id => 1,
        :created_at => Mon, 26 Sep 2022 13:45:53 UTC +00:00,
        :updated_at => Mon, 26 Sep 2022 13:45:53 UTC +00:00,
              :name => "Leanne Graham",
          :username => "Bret",
             :email => "Sincere@april.biz",
             :phone => "1-770-736-8031 x56442",
           :website => "hildegard.org"
    }
]

但這要複製修改好麻煩，所以如果我們知道下的statement 或clause 會回傳relation 時，也能偷吃步利用to_sql方法來轉化為SQL statement，如：

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

irb(main):006:0> User.where(name: "Clementina DuBuque").to_sql
"SELECT \"users\".* FROM \"users\" WHERE \"users\".\"name\" = 'Clementina DuBuque'"

# 或更複雜的句子
irb(main):007:0> User.joins(:posts).where(name: "Clementina DuBuque").to_sql
"SELECT \"users\".* FROM \"users\" INNER JOIN \"posts\" ON \"posts\".\"userId\" = \"users\".\"id\" WHERE \"users\".\"name\" = 'Clementina DuBuque'"

而當我們有SQL statements，就能用來反向查詢到同樣的資料啦：

irb(main):010:0> sql = User.joins(:posts).where(name: "Clementina DuBuque").to_sql
"SELECT \"users\".* FROM \"users\" INNER JOIN \"posts\" ON \"posts\".\"userId\" = \"users\".\"id\" WHERE \"users\".\"name\" = 'Clementina DuBuque'"


irb(main):011:0> User.find_by_sql(sql)
  User Load (1.2ms)  SELECT "users".* FROM "users" INNER JOIN "posts" ON "posts"."userId" = "users"."id" WHERE "users"."name" = 'Clementina DuBuque'
[
    [0] #<User:0x00007fc4f6cf0fe0> {
# 略...
]

呵... 然後期望自己多看幾次，後面練起SQL 就會比較順吧？哈~~

那麼今天被翻爛的書的研究就到這囉，謝謝大家！

補充，查看方法時看到.table_name 可以查Model 對應的資料表，好像終於多瞭解了一些公司專案在做什麼，但... 哈哈哈~~