Php/zend framework/DBモデルメモ

モデルの定義

application/models/dao/TestDao.phpに以下を定義

class TestDao extends Zend_Db_Table_Abstract
{
  // テーブル名
  protected $_name = 'test';
  // 主キー
  protected $_primary = 'id';
}

データ挿入

create table test(id int, title varchar(10));なテーブルに挿入

$db = Zend_Db::factory('PDO_MYSQL', $db_info);
// すべての Zend_Db_Table オブジェクトに対するデフォルトアダプタを設定します
Zend_Db_Table::setDefaultAdapter($db);
$dao = new TestDao();
$item = array(
    'id' => 1,
    'title'  => 'abc',
);
// 実行&プライマリーID取得
$id = $dao->insert($item);

データ削除

$where = $dao->getAdapter()->quoteInto('id = ?', 1, 'INTEGER');
$dao->delete($where);

データ更新

$item = array('title' => 'def');
$where = $dao->getAdapter()->quoteInto('id = ?', 1, 'INTEGER');
$dao->update($item, $where);

schemaを変更したデータ更新

$adapter = $this->getAdapter();
$where = $adapter->quoteInto("uid = ?", $id);
return $adapter->update(array($this->_schema, $this->_name), $data, $where);

日時を自動で入れる

public function insert($data = array())
{
   $data['created'] = date('Y-m-d H:i:s', time());
   parent::insert($data);
}

検索

$rows = $dao->find(1);
$rowArray = $rows->toArray();

検索カスタマイズ

TestDaoクラスに以下メソッドを追加し

public function findByTitle($title)
{
  $where = $this->getAdapter()->quoteInto('title = ?', $title);
  return $this->fetchAll($where);
}

アクションに以下を追加

$rows = $dao->findByTitle('aiueo');

findで一行だけ取得する方法

$row = $users->find(1)->current();

トランザクション

$dao->getAdapter()->beginTransaction();
$dao->getAdapter()->commit();
$dao->getAdapter()->rollback();

検索推奨メソッド名

タイトルで検索する場合

$dao->findAllByTitle($title);

IDとパスワードで検索

$dao->findByIdAndPassword($id, $password);

複数のDBを使用する場合

$db = Zend_Db::factory('PDO_MYSQL', $config->db->params);
Zend_Registry::set('my_db', $db);
$dao = new Model_Dbtable_Users(array('db' => 'my_db'));

テーブル名を動的に変更する場合

public function __construct($month)
{
  $this->_name = 'tests' . $month;
  parent::__construct();
}

likeを使った条件

$where = $adapter->quoteInto('word like ?', '%' . $word . '%');

CreateTable文取得

public function showCreateTable()
{
  $adapter = $this->getAdapter();
  $sql = sprintf('SHOW CREATE TABLE %s;', $adapter->quoteIdentifier($this->_name));
  $stmt = $adapter->query($sql);
  $row = $stmt->fetch(Zend_Db::FETCH_ASSOC);
  return $row['Create Table'];
}

DropTable文取得

public function getDropTable()
{
  $adapter = $this->getAdapter();
  $sql = sprintf('DROP TABLE IF EXISTS %s;', $adapter->quoteIdentifier($this->_name));
  return $sql;
}

Zend_Db_Tableで集計SQLを実行した例

public function countGroupModeAndServiceCodeByCreated($date)
{
  $adapter = $this->getAdapter();
  $select = $adapter->select();
  $select->from($this->_name,
                 'left(created, 10) as day, mode, service_code, count(*) as cnt',
                 $this->_schema);
  $select->where($adapter->quoteInto('left(created, 10) = ?', $date));
  $select->group('left(created, 10), mode, service_code');
  return $adapter->fetchAll($select);
}

Zend_Db_Tableでcountを使う例

$adapter = $this->getAdapter();
$select = $adapter->select();
$select->from($this->_name, 'count(*)', $this->_schema);
$select->where($adapter->quoteInto("id = ?", $id, 'INTEGER'));
return $adapter->fetchOne($select);

Zend_Db_Tableでテーブル件数取得（Zend_Db_Tableで使うことは推奨ではない

$adapter = $this->getAdapter();
$select = $adapter->select();
$select->from($this->_name, 'count(*)');
return $adapter->fetchOne($select);

Zend_Db_TableでIN検索

if ($ids) {
    $where = $this->getAdapter()->quoteInto("id IN (?)", $ids);
    return $adapter->fetchAll($where);
}

Zend_Db_TableでOR検索

public function fetchAllByIds($ids)
{
  $adapter = $this->getAdapter();
  $select = $adapter->select();
  $select->from($this->_name, "*", $this->_schema);
  foreach ($ids as $id) {
    $select->orWhere($adapter->quoteInto("id = ?", $id, 'INTEGER'));
  }
  return $adapter->fetchAll($select);
}

Zend_Db_TableでNullを入れる

$data = array(
    'flag'      => null // 普通にNullでいける
);
$this->update($data);

$dao->insert($data);

Zend_Db_Tableごとに固有のDBを指定する

class TestDao extends Zend_Db_Table_Abstract
{
    protected $_name = 'test';
    public function _setup()
    {
        $config = new Zend_Config_Ini(APPLICATION_PATH . '/configs/db.ini', APPLICATION_ENV);
        $adapter = Zend_Db::factory($config->default);
        $this->_setAdapter($adapter);
    }

class外部からtable名を変更する

$dao = new TestsDao();
$dao->setOptions(array('name' => 'tests_1', 'schema' => 'schema_1'));

quoteIntoのOR

$where[] = $adapter->quoteInto("finish_cnt >= ? OR retry_cnt >= ?" , 10, 20);

SQL式評価

例：update間隔をSQLを使って設定

$data = array();
$data['updated_span'] = new Zend_Db_Expr('UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(updated)');
$data['updated'] = date('Y-m-d H:i:s', time());
$this->update($data);

例：increment処理などはこのように

$adapter = $this->getAdapter();
$data['access'] = new Zend_Db_Expr('access + 1');
$where = $adapter->quoteInto("id = ?", $id);
return $this->update($data, $where);

sqlログを吐く

注：プレースホルダーとバインド値は個別に出力

• Bootstarap.php

$config = new Zend_Config_Ini('db.ini', 'development');
$db = Zend_Db::factory($config->db);
// プロファイルを有効に
$db->getProfiler()->setEnabled(true);
Zend_Db_Table_Abstract::setDefaultAdapter($db);

• AbstractDao.php

require_once 'Zend/Db/Table.php';
require_once 'Zend/Log.php';
require_once 'Zend/Log/Writer/Stream.php';
class AbstractDao extends Zend_Db_Table_Abstract
{
  protected function _update($data, $where)
  {
    $log = new Zend_Log();
    $log->addWriter(new Zend_Log_Writer_Stream('/tmp/sql.log'));
    try {
      $this->update($data, $where);
      $query = $this->_db->getProfiler()->getLastQueryProfile();
      $sqllog = sprintf("%s [%s] %ss", $query->getQuery(), join(",", $query->getQueryParams()), $query->getElapsedSecs());
      $log->log($sqllog, Zend_Log::DEBUG);
    } catch(Exception $e) {
      $query = $this->_db->getProfiler()->getLastQueryProfile();
      $sqllog = sprintf("%s [%s]", $query->getQuery(), join(",", $query->getQueryParams()));
      $log->log($sqllog, Zend_Log::ERR);
      throw $e;
    }
  }
}

Zend_Db_SelectでLeftJoin

$joinTableScheme = 'draws';
$joinTable = 'sub_users';
$select = $adapter->select();
$select->from($this->_name, '*', $this->_schema);
$select->joinLeft($joinTableScheme.'.'.$joinTable, 'users.id = '. $joinTable.'.user_id', '*');

参考：https://stackoverflow.com/questions/12273493/how-to-join-tables-in-zend-framework

Zend_Db_Selectで順序変更

$select->order('rating DESC');

参考

http://framework.zend.com/manual/ja/zend.db.table.html