active record使用memcached

一直用sql當memcached的key跑的順順的
不過最近在用ci在開發，發現用的active record不知抓什麼來當key
研究了一下，發現有last_query()可取出sql string
不過又看了一下發現是先execute後，才會有的query

而cache當然是要跑之前做，不然就沒有意義了
又serach了一下，發現有_compile_select()
差別當然在有執行跟沒執行，以下是片斷程式碼
有引用tomschlick寫好給ci用的memcached-library

 $key = $this->db->_compile_select();
$this->load->library('Memcached_library','','memcached');
$results = $this->memcached->get($key);

// If the key does not exist it could mean the key was never set or expired
if ($results) 
     echo 'hit';
else{
     echo 'miss';
     $results = $this->db->get()->row_array();
     $this->memcached->add($key, $results);
}

不過每個有用db的程式這樣寫太麻煩了
原本想改寫ci的db driver，看起來有點麻煩，先求有吧
所以先簡單的寫在helper裡，有空再來研究改寫driver

_compile_select());
        $ci = & get_instance();
        $ci->load->library('Memcached_library','','memcached');
        $results = $ci->memcached->get($key);

        // If the key does not exist it could mean the key was never set or expired
        if ($results) {

                $ci->fb->info('[memcache] hit', "info");
        }else{

                $ci->fb->warn('[memcache] miss', "memcached");
                $results = $db->get()->row_array();
                $ci->memcached->add($key, $results);
        }

        $db->_reset_select();  //clear sql query string
        return $results;
}

References Getting CodeIgniter Active Record's current SQL code

CodeIgniter - db active record常用指令

• basic
  
  • 取得config中，名為group_one的db

    $DB1 = $this->load->database('group_one', TRUE); //給true才會產生實體給$DB1
    $this->db->select('title')->from('mytable')->where('id', $id)->limit(10, 20);
    

    p.s. 給true才會回傳個db object
  • 取sql string
    • last_query()
      執行的過程中，最後的一個sql query string
    • _compile_select()
      目前sql條件的sql query string，尚未實際執行
• select

  $this->db->select('title, content, date'); //不下的話就是*
  $query = $this->db->get('mytable');
  foreach ($query->result() as $row)
  {
      echo $row->title;
  }
  

• from、join

  $this->db->from('mytable');

  $this->db->select('*');
  $this->db->from('blogs');
  $this->db->join('comments', 'comments.id = blogs.id');
  
  $query = $this->db->get();
  
  // Produces:
  // SELECT * FROM blogs
  // JOIN comments ON comments.id = blogs.id

• where 

  //法1.
  $query = $this->db->get_where('mytable', array('id' => $id), $limit, $offset);
  $this->db->or_where('id >', $id);  // Produces: WHERE name != 'Joe' OR id > 50
  
  //法2
  $this->db->select('title')->from('mytable')->where('id', $id);
  

• insert
新增欄位為now()的方法

$data = array (
   'customer_id'=> $customer_id,
   'total' => $totalprice
  );
  $this->db->set('order_date', 'NOW()', FALSE);
  $this->db->insert('omc_orders', $data);

Reference

• Connecting to your Database
• Active Record Class