---
# System prepended metadata

title: '[rails][model][note]rails4 model & 資料庫語法筆記'
tags: [Rails4, note, rails, model]

---

# [rails][model][note]rails4 model & 資料庫語法筆記

###### tags: `rails`,`Rails4`,`model`,`note`

## CRUD語法相關筆記


### regex相關
ex1
```ruby
regex = "\\d{4}-\\d{2}-\\d{2}"
customers = Customer.where("birthday ~* '#{regex}'")
```


### group+count簡例
https://stackoverflow.com/questions/27815296/group-count-having-and-order-by-in-rails
```ruby
# 取出每個顧客的訂單數
Order.group(:customer_id).count

# 取出每個顧客的訂單大於2的訂單數量
Order.group(:customer_id).having("count(customer_id) > 2").count

# 取出顧客的訂單數量大於2的總顧客數
Order.group(:customer_id).having("count(customer_id) > 2").count.keys.count
```

### 將query轉為raw sql語法
https://stackoverflow.com/questions/5483407/subqueries-in-activerecord
```ruby
User.where(:id => [5, 1, 2, 3]).to_sql
```


### raw query
https://stackoverflow.com/questions/15855606/rails-4-raw-query-using-activerecord
https://stackoverflow.com/questions/14824453/rails-raw-sql-example


### 找出非null跟empty的record
https://blog.arkency.com/rails-how-to-find-records-where-column-is-not-null-or-empty/


### 寫入時間日期
https://stackoverflow.com/questions/41224597/ruby-on-rails-how-to-save-current-datetime-to-database

```ruby
Customer.update(first.created_at: Datetime.Now)

```



### where相關


#### 用where+update_all更新資料

https://stackoverflow.com/questions/9186822/how-do-i-do-an-update-statement-where-a-condition
```ruby
UrModel.where(state: 'decline').update_all(state: 'deny')
```
>在where情況下若使用update會出現argument error
>

#### 關於where.not跟nil
https://thoughtbot.com/blog/activerecord-s-where-not-and-nil
>where.not無法取得欄位為null的值!

#### where with raw query
https://stackoverflow.com/questions/17192829/does-rails-4-have-support-for-or-queries
```ruby
Article.where("articles.title = 'Rails 3' OR articles.title = 'Rails 4'")
```

#### where not in with raw query
```ruby
  text_array = ["status1","status2"]

  output = Order.where("orders.column1 IS NULL OR (orders.column1 NOT IN ('#{text_array.join("','")}'))")
```

#### 用陣列查詢
@users = User.where(id:[1,2,3])


## Scope

https://railsbook.tw/chapters/16-model-basic.html#scope-and-class-method
### 設定資料預設排序
```ruby
class Product < ActiveRecord::Base
  default_scope {order('id ASC')}
end


```

## Model

### 檢查某筆資料是否存在於資料表中
https://stackoverflow.com/questions/18082778/rails-checking-if-a-record-exists-in-database

```ruby
if Truck.where(:id => current_truck.id).blank?
  # no truck record for this id
else
  # at least 1 record for this truck
end
```


### 取得關連式資料表的資料
https://progressbar.tw/courses/3/watching
ex1

cat為主分類,subcat為次分類,
cat與subcat為1 to N relationship

subcat belongs_to cat
cat has_many subcats

/app/models/subcat.rb
```ruby=
class Subcat < ActiveRecord::Base
  belongs_to :cat

  has_many :products

  def get_subcat_name
    "#{cat.name}/#{name}" 
  end
end
```

>重點1:直接用小寫cat可取得subcat上層cat的資料
>重點2:直接在model裡面打model的屬性即可取得該屬性


### 取出資料中所有id:使用ids

rails console
```c!
2.2.6 :039 > Cat.all.ids
   (0.4ms)  SELECT "cats"."id" FROM "cats"
 => [1, 2, 3] 
```



## join相關



### join三個資料表

三個資料表:cats(主分類),subcats(次分類),products(產品)

cats has many subcats
subcats belongs to cats

subcats has many products
products belongs to subcats

#### 較差的寫法: 直接join
https://stackoverflow.com/questions/10322164/rails-3-joins-select-only-certain-columns

models/cat.rb
```ruby=
class Cat < ActiveRecord::Base
  has_many :subcats
end
```

models/subcat.rb
```ruby=
class Subcat < ActiveRecord::Base
  belongs_to :cat
  has_many :products
end
```

models/product.rb
```ruby=
class Product < ActiveRecord::Base
  belongs_to :subcat
end
```


application_controller.rb
```ruby
class ApplicationController < ActionController::Base

#...
  def get_products(id,subcat_id= nil)
    begin
      cat_id = id

      @cat=Cat.find_by(id: cat_id)
      
      if subcat_id
        @subcat=@cat.subcats.find_by(id: subcat_id)
        raise if !@subcat

        return @subcat.products
          
      else

        return Product.joins(subcat: :cat)
        .select(%Q(
          products.id as id,
          subcats.id as subcat_id,
          cats.id as cat_id,
          cats.name as cat_name,
          subcats.name as subcat_name,
          products.name,
          products.description,
          products.img_url,
          products.price
          ))
          .where(subcat_id: @cat.subcats.ids)
       
      end
      
    rescue 
      redirect_to products_cat_path(Cat.first)
    end
  end
  
  
end

```
>寫在appplication_controller的function可以被所有controller呼叫

#### 較好的寫法: 使用has_many: though


models/cat.rb
```ruby=
class Cat < ActiveRecord::Base
  has_many :subcats
  has_many :products, through: :subcats #<--加上這行
end
```

models/subcat.rb
```ruby=
class Subcat < ActiveRecord::Base
  belongs_to :cat
  has_many :products
end
```

models/product.rb
```ruby=
class Product < ActiveRecord::Base
  belongs_to :subcat
end
```

application_controller.rb
```ruby
class ApplicationController < ActionController::Base
#...
  def get_products(id,subcat_id= nil)
    begin
      cat_id = id

      @cat=Cat.find_by(id: cat_id)
      
      if subcat_id
        @subcat=@cat.subcats.find_by(id: subcat_id)
        raise if !@subcat

        return @subcat.products
      
      else
      
        return @cat.products #<--改成這樣
       
      end
      
    rescue 
      redirect_to products_cat_path(Cat.first)
    end
  end
  
  
end
```




### 計算join後結果的總筆數

假設用上述method去結合table,如何計算資料的總筆數?

ans: 用size

controller
```ruby=
#...
@products = get_products(params[:id])
@total = @products.size
#...
```
>不能用count,會出問題


## 設定連動刪除
https://stackoverflow.com/questions/2203835/how-can-i-delete-child-objects-when-the-parent-is-deleted-in-rails
```ruby
class Table1 < ActiveRecord::Base

  has_many :table1_table2_relationships,:dependent => :destroy
  has_many :table2s,through: :table1_table2_relationships

end
```