哈囉，大家好~~

昨天研究到利用Query Interface 來加快query 的便利性而不用寫raw SQL，而今天則是想練習大家耳熟能詳的where，但想專注在混雜SQL statement 的相關用法!

我們先來看一下Rails 對where 的說明：

where:

The where method allows you to specify conditions to limit the records returned, representing the WHERE-part of the SQL statement. Conditions can either be specified as a string, array, or hash.

根據Rails Guide，conditions 主要分成五種：

1. pure String
2. Array
3. Hash
4. Not
5. And

讓我們自己下conditions 並利用以上幾種方法練習吧~~！

但在開始之前，為了便於等會可用LIKE 等query statements，我們先讓資料有點相似的變化，如：

posts = Post.where(userId: 1)

posts.map { |post| post.update(body: "IThome#{post.id}") }
# 如此body 就會是IThome1、IThome2、IThome3......

接著就能開始練習了：

1. pure String：

irb(main):032:0> Post.where("body = 'IThome1'")
  Post Load (0.7ms)  SELECT "posts".* FROM "posts" WHERE (body = 'IThome1')
[
    [0] #<Post:0x00007f7b207197e0> {
                :id => 1,
            :userId => 1,
             :title => "sunt aut facere repellat provident occaecati excepturi optio reprehenderit",
              :body => "IThome1",
        :created_at => Sat, 01 Oct 2022 14:49:33 UTC +00:00,
        :updated_at => Mon, 03 Oct 2022 15:05:40 UTC +00:00
    }
]

# 欄位形態是string 或text 的沒問題，但integer 呢？

irb(main):062:0> Post.where("userId = '1'")
  Post Load (1.4ms)  SELECT "posts".* FROM "posts" WHERE (userId = '1')
Traceback (most recent call last):
ActiveRecord::StatementInvalid (PG::UndefinedColumn: ERROR:  column "userid" does not exist)
LINE 1: SELECT "posts".* FROM "posts" WHERE (userId = '1')

# 好吧，我失敗了！期待有緣人解惑 XD
irb(main):122:0> Post.where("'userId\' = 1")
  Post Load (1.5ms)  SELECT "posts".* FROM "posts" WHERE ('userId' = 1)
Traceback (most recent call last):
ActiveRecord::StatementInvalid (PG::InvalidTextRepresentation: ERROR:  invalid input syntax for integer: "userId")
LINE 1: SELECT "posts".* FROM "posts" WHERE ('userId' = 1)

2. Array：

irb(main):136:0> Post.where("body = ?", "IThome1")
  Post Load (1.0ms)  SELECT "posts".* FROM "posts" WHERE (body = 'IThome1')
  
# 但上面那句的實際query 效果跟pure string 差不多，因此我們來假造一個params 並仿Rails Guide query：
irb(main):150:0> params = { body: 'IThome1' }
{
    :body => "IThome1"
}
irb(main):151:0> Post.where("body = ?", params[:body])
  Post Load (1.1ms)  SELECT "posts".* FROM "posts" WHERE (body = 'IThome1')
[
    [0] #<Post:0x00007f7b23a8fed0> {
                :id => 1,
            :userId => 1,
             :title => "sunt aut facere repellat provident occaecati excepturi optio reprehenderit",
              :body => "IThome1",
        :created_at => Sat, 01 Oct 2022 14:49:33 UTC +00:00,
        :updated_at => Mon, 03 Oct 2022 15:05:40 UTC +00:00
    }
]

接著我們嘗試給兩個條件：
irb(main):157:0> Post.where("body = ? AND title = ?", params[:body], 'sunt aut facere repellat provident occaecati excepturi optio reprehenderit')

# 或

irb(main):158:0> Post.where("body = ? AND created_at < ?", params[:body], Time.now)
  Post Load (0.9ms)  SELECT "posts".* FROM "posts" WHERE (body = 'IThome1' AND created_at < '2022-10-03 18:12:57.943402')

# 還算成功

那我們能不能讓時間也利用params 當query 的statement 呢？

# 首先這是剛剛假造的params
irb(main):168:0> params
{
    :body => "IThome1"
}

# 我們先偷吃步抓出Post.first 的created_at
irb(main):169:0> time = Post.find(1).created_at
  Post Load (0.5ms)  SELECT "posts".* FROM "posts" WHERE "posts"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
Sat, 01 Oct 2022 14:49:33 UTC +00:00

# 接著加工params
irb(main):170:0> params = params.merge(created_at: time)
{
          :body => "IThome1",
    :created_at => Sat, 01 Oct 2022 14:49:33 UTC +00:00
}

# 再試著模擬有params 來做multiple conditioned query XD
irb(main):171:0> Post.where("body = ? AND created_at = ?", params[:body], params[:created_at])
  Post Load (0.7ms)  SELECT "posts".* FROM "posts" WHERE (body = 'IThome1' AND created_at = '2022-10-01 14:49:33.285184')
  
# 再次偷吃步測試成功~

3. Hash:

待補