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

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

## CRUD語法相關筆記

### regex相關
ex1

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

# 取出每個顧客的訂單數
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

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

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

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

Article.where("articles.title = 'Rails 3' OR articles.title = 'Rails 4'")

#### where not in with raw query

  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
### 設定資料預設排序

class Product < ActiveRecord::Base
  default_scope {order('id ASC')}
end

## Model

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

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

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

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

class Cat < ActiveRecord::Base
  has_many :subcats
end

models/subcat.rb

class Subcat < ActiveRecord::Base
  belongs_to :cat
  has_many :products
end

models/product.rb

class Product < ActiveRecord::Base
  belongs_to :subcat
end

application_controller.rb

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

class Cat < ActiveRecord::Base
  has_many :subcats
  has_many :products, through: :subcats #<--加上這行
end

models/subcat.rb

class Subcat < ActiveRecord::Base
  belongs_to :cat
  has_many :products
end

models/product.rb

class Product < ActiveRecord::Base
  belongs_to :subcat
end

application_controller.rb

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

#...
@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

class Table1 < ActiveRecord::Base

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

end