Zend_DBのSELECTメソッドのまとめ

[参考記事] Zend Frameworkのデータベース接続
 Zend_DBの基本
 クエリの実行
 クォート処理
 レコード挿入処理(INSERT)
レコード更新処理(UPDATE)
レコード削除処理(DELETE)
トランザクション処理

 レコード参照処理(SELECT)
fromメソッド
 joinメソッドその他のJOIN
whereメソッド
 limitメソッド、limitPageメソッド
 orderメソッド
 groupメソッド
 havingメソッド
 distinctメソッド
 columnsメソッド
 unionメソッド
 forUpdateメソッド

 resetメソッド Select オブジェクトの一部のリセット
 getPartメソッド Select オブジェクトの一部の取得
 reset() および getPart() で使用する定数

レコード参照処理(SELECT)

Zend_Dbで、SELECT文を実行するには、Zend_Db_Selectを使用します。
そしてZend_Db_Selectのインスタンスを生成するには、Zend_Db_Adapterのselectメソッドを使います。

これにより生成されるインスタンスは、SELECT構文の名前と同じメソッドを持っていて、柔軟にSELECT文を作成できます。

例えば

SELECT * FROM test_table WHERE id > 10;

は

$select = $db->select();
$select->from('test_table', '*');
$select->where('id > ?', 10);
echo $select->__toString();

のように書くとSQL文が生成されます。
$db->select() にはSQL文を生成するためのオブジェクトが入りますが、 __toStringメソッドで文字列に変換することができます。

レコードを抽出するには、fetchAllメソッドかfetchRowを使います。
fetchAllは該当する全件を取得します。
fetchRowは1件だけ取得します。

使い方は次のようになります。

$select = $db->select();
$select->from('test_table', '*');
$select->where('id > ?', 10);
$rows = $db->fetchAll($select);

$select = $db->select();
$select->from('test_table', '*');
$select->where('id > ?', 10);
$sql = $select->__toString();
$rows = $db->fetchAll($sql);

$select = $db->select();
$select->from('test_table', '*');
$select->where('id > ?', 10);
$sql = $select->__toString();
$result = $db->query($sql);
$rows = $result->fetchAll();

この戻り値は全て同じで、オブジェクトが返ります。
$rows->toArray();とすることで配列に変換することができ、結果は以下のような感じになります。

array(
  [0]=>array(['id']=>10, ['name']=>'hoge', ['text']=>'aaaaa'),
  [1]=>array(['id']=>11, ['name']=>'huga', ['text']=>'bbbbb'),
  [2]=>array(['id']=>12, ['name']=>'aree', ['text']=>'ccccc')
)

fetchRowの場合は次のようになります。

$select = $db->select();
$select->from('test_table', '*');
$select->where('id > ?', 10);
$rows = $db->fetchRow($select);

$select = $db->select();
$select->from('test_table', '*');
$select->where('id > ?', 10);
$sql = $select->__toString();
$rows = $db->fetchRow($sql);

$select = $db->select();
$select->from('test_table', '*');
$select->where('id > ?', 10);
$sql = $select->__toString();
$result = $db->query($sql);
$rows = $result->fetchRow();

この戻り値も全て同じで、オブジェクトが返ります。
$rows->toArray();とすることで配列に変換することができ、結果は以下のような感じになります。

array(
  ['id']=>10, ['name']=>'hoge', ['text']=>'aaaaa'
)

fetchAllと違い、1レコードのみが返ります。

fromメソッド

fromメソッドでは、SELECT文で取得する項目と参照するテーブルを指定します。

$select = $db->select();
$select->from('test_table', '*');
$select->__toString();

とすると

SELECT `test_table`.* FROM `test_table`

のようなSQL文が作られます。

取得するカラムフィールドを指定するには次のようにします。

$select = $db->select();
$select->from('test_table', 'id');
$select->__toString();

SELECT `test_table`.`id` FROM `test_table`

複数指定する場合は配列で渡します。

$select = $db->select();
$select->from('test_table', array('id','name'));
$select->__toString();

SELECT `test_table`.`id`, `test_table`.`name` FROM `test_table`

文字列として渡してしまうと、それを１つのフィールドとして判断されてしまいます。

$select = $this->_db->select();
$select->from('test_table', 'id , name');
$sql = $select->__toString();

SELECT `test_table`.`id , name` FROM `test_table`

また複数のテーブルを参照したい場合は、fromメソッドを複数実行します。

$select = $db->select();
$select->from('test1_table', 'aaa');
$select->from('test2_table', 'bbb');

SELECT `test1_table`.`aaa`, `test2_table`.`bbb` FROM `test1_table` INNER JOIN `test2_table`

テーブルや項目に別名を振るには、単純には次のようにします。

$select = $db->select();
$select->from('test1_table as t1', 't1.aaa');
$select->from('test2_table as t2', 't2.bbb');

SELECT `t1`.`aaa`, `t2`.`bbb` FROM `test1_table` AS `t1` INNER JOIN `test2_table` AS `t2`

しかしZend_DBでの正しい書き方は以下のように連想配列で渡します。

$select = $db->select();
$select->from(array('t1'=>'test1_table'), 't1.aaa');
$select->from(array('t2'=>'test2_table'), 't2.bbb');

フィールドの別名を振る場合も連想配列で渡します。

$select = $db->select();
$select->from(array('t1'=>'test_table'), array('t1'=>'aaa'));

SELECT `t1`.`aaa` AS `t1` FROM `test_table` AS `t1`;

joinメソッド

テーブルをJOINする場合には、joinメソッドを使用します。 fromメソッドとの違いは、テーブル名とカラム名の間に接続条件を指定します。

$select = $db->select();
$select->from('test1_table', '*');
$select->join('test2_table', 'test1_table.id = test2_table.id', '*');

SELECT `test1_table`.*, `test2_table`.* FROM `test1_table` INNER JOIN `test2_table` ON test1_table.id = test2_table.id

Zend_Db_Table_Selectでjoinメソッドを使用する場合には、setIntegrityCheck(false)を指定しないとエラーになります。
Select query cannot join with another table

マニュアルには以下のようにあります。

Zend_Db_Table_Select の主な使用目的は、制約を強要して正しい形式の SELECT クエリを作成することです。
しかし時には、Zend_Db_Table_Row の柔軟性が必要であって行を更新したり削除したりすることはないということもあります。
そんな場合には、setIntegrityCheck に false を渡して行/行セットを取得できます。
この場合に返される行/行セットは 'ロックされた' 行 (save()、delete() やフィールドの設定用メソッドを実行すると例外が発生する) となります。

記述例

$select = $this->select()
    ->setIntegrityCheck(false)
    ->from('test_table1')
    ->join('test_table2', "test_table1.test_id1 = test_table1.test_id2"))
Zend_Db_Table

joinメソッドの種類

メソッド	SQL	結合種類	RDBMSの対応
join(table, condition[, columns]) joinInner(table, condition[, columns])	INNER JOIN	等価結合	◎
joinLeft(table, condition[, columns])	LEFT JOIN	外部結合(左結合・左外部結合)	◎
joinRight(table, condition[, columns])	RIGHT JOIN	外部結合(右結合・右外部結合)	△
joinFull(table, condition[, columns])	FULL JOIN	完全外部結合	△
joinCross(table[, columns])	CROSS JOIN	クロス結合	△
joinNatural(table[, columns])	NATURAL JOIN	自然結合	自然内部結合のみ

joinメソッド、joinInnerメソッド

join(【テーブル名】, 【JOIN条件】[, 【取得するカラム】])
joinInner(【テーブル名】, 【JOIN条件】[, 【取得するカラム】])

INNER JOIN
等価結合
結果セットには、その結合条件を満たす行のみが含まれます。条件のどちらかのデータが存在しない場合、結果セットには含まれません。

すべての RDBMS が、この結合形式に対応しています。

joinLeftメソッド

joinLeft(【テーブル名】, 【JOIN条件】[, 【取得するカラム】])

LEFT JOIN (left outer join)
外部結合(左結合・左外部結合)
一方の条件に対するデータが存在しなかった場合でも片方のデータを結果セットに含める指定ができます。
左側のテーブルのすべての行と条件にマッチする右側のテーブルの行が含まれます。右側のテーブルからのカラムのうち、左側のテーブルに対応する行がないものについては NULL で埋められます。

すべての RDBMS が、この結合形式に対応しています。

joinRightメソッド

joinRight(【テーブル名】, 【JOIN条件】[, 【取得するカラム】])

RIGHT JOIN (right outer join)
外部結合(右結合・右外部結合)
joinLeftメソッドの逆です。
一方の条件に対するデータが存在しなかった場合でも片方のデータを結果セットに含める指定ができます。
右側のテーブルのすべての行と条件にマッチする左側のテーブルの行が含まれます。左側のテーブルからのカラムのうち、右側のテーブルに対応する行がないものについては NULL で埋められます。

RDBMS によっては、この結合形式に対応していないものもありますが、テーブルの記述順を変更することで左外部結合として表すことが可能です。

joinFullメソッド

joinFull(【テーブル名】, 【JOIN条件】[, 【取得するカラム】])

FULL JOIN
完全外部結合
joinLeftメソッド(左外部結合)とjoinRightメソッド(右外部結合)を組み合わせたようなものです。
両側のテーブルのすべての行が含まれます。
結合条件を満たす組み合わせがあった場合はそれらが同一行にまとめられ、それ以外の場合は、対応するデータがないカラムについては NULL で埋められます。

RDBMS によっては、この結合形式に対応していないものもあります。

joinCrossメソッド

joinCross(【テーブル名】[, 【取得するカラム】])

CROSS JOIN
クロス結合
デカルト積のことです。
最初のテーブルの各行に対して、二番目のテーブルのすべての行がマッチします。つまり、結果セットの行数は、ふたつのテーブルの行数の積と等しくなります。結果セットをフィルタリングするには、WHERE 句で条件を指定します。この方法によるクロス結合は、昔の SQL-89 の結合構文と似ています。

RDBMS によっては、この結合形式に対応していないものもあります。

joinNaturalメソッド

joinNatural(【テーブル名】[, 【取得するカラム】])

NATURAL JOIN
自然結合
両方のテーブルに同じ名前で登場するカラムを比較します。比較はすべてのカラムに対して行われます。
この API でサポートしているのは、自然内部結合のみです。 SQL で自然外部結合がサポートされていたとしても、使用できません。

whereメソッド

SELECT文でWHERE条件を指定するには、whereメソッドを使用します。

$select = $db->select();
$select->from('test_table', '*');
$select->where('id=?', 1);

whereメソッドに条件を設定すると次のようなのSQL文が作らます。

SELECT `test_table`.* FROM `test_table` WHERE (id=1)

条件が複数ある場合は、whereメソッドを続けて指定するとAND条件になり、orWhereメソッドを指定するとOR条件になります。

$select = $db->select();
$select->from('test_table', '*');
$select->where('id=?', 1);
$select->where('id2=?', 10);

SELECT `test_table`.* FROM `test_table` WHERE (id=1) AND (id2=10)

$select = $db->select();
$select->from('test_table', '*');
$select->where('id=?', 1);
$select->orWhere('id2=?', 10);

SELECT `test_table`.* FROM `test_table` WHERE (id=1) OR (id2=10)

orWhereメソッドを最初に書いた場合には、『 OR 』が付かないのでwhereメソッドと同じになります。

条件に『 IN 』などを指定し、複数の値を渡すには配列で渡します。

$select = $db->select();
$select->from('test_table', '*');
$select->where('id IN(?)', array(1,3));

SELECT `test_table`.* FROM `test_table` WHERE (id IN(1, 3))

limitメソッド、limitPageメソッド

取得する値の数を制限するには、limitメソッドまたはlimitPageメソッドを使います。
limitメソッドは、件数とオフセットで絞り込むときに使います。
limitPageメソッドはページャーなどページで絞り込むときに使います。

limitメソッドは

limit(【取得する件数】, 【オフセット(開始位置)】)

となりオフセット(開始位置)は０から始まります。

$select = $db->select();
$select->from('test_table', '*');
$select->limit(10, 25);

SELECT `test_table`.* FROM `test_table` LIMIT 10 OFFSET 25

limitPageメソッドは

limitPage(【ページ番号】, 【取得する件数】)

となりページ番号は１から始まります。

$select = $db->select();
$select->from('test_table', '*');
$select->limitPage(4, 10);

SELECT `test_table`.* FROM `test_table` LIMIT 10 OFFSET 30

生成されるSQL文はDBMSによって異なります。

limitメソッドとlimitPageメソッドは、ともに２つの引数を渡しますが、取得する件数の指定をlimitメソッドでは第１引数に、limitPageメソッドでは第２引数に渡す違いがあるので注意が必要です。

orderメソッド

取得する値の並び順を指定するには、orderメソッドを使います。

$select = $db->select();
$select->from('test_table', '*');
$select->order('name');
SELECT `test_table`.* FROM `test_table` ORDER BY `name` ASC

複数の並び順を指定するには、複数回orderメソッドを指定するか、配列で渡します。

$select = $db->select();
$select->from('test_table', '*');
$select->order('name');
$select->order('name2');
SELECT `test_table`.* FROM `test_table` ORDER BY `name` ASC, `name2` ASC

$select = $db->select();
$select->from('test_table', '*');
$select->order(array('name','name2'));
SELECT `test_table`.* FROM `test_table` ORDER BY `name` ASC, `name2` ASC

orderメソッドは並び順を指定しない場合は、デフォルトで昇順(ASC）が設定されます。
降順で表示したい場合は、明示的に『 DESC 』を付ける必要があります。

$select = $db->select();
$select->from('test_table', '*');
$select->order('name DESC');
SELECT `test_table`.* FROM `test_table` ORDER BY `name` DESC

groupメソッド

グループ化を指定するには、groupメソッドを使います。

$select = $db->select();
$select->from('test_table', '*');
$select->group('text');

SELECT `test_table`.* FROM `test_table` GROUP BY `text` 

$select = $db->select();
$select->from('test_table', '*');
$select->group('text');
$select->group('text2');

SELECT `test_table`.* FROM `test_table` GROUP BY `text`, `text2`

2回実施しているgroupメソッドは、配列にして渡す事で1回にする事が可能です。

$select = $db->select();
$select->from('test_table', '*');
$select->group(array('text','text2'));
SELECT `test_table`.* FROM `test_table` GROUP BY `text`, `text2`

havingメソッド

グループ化したデータを絞り込むには、havingメソッドを使います。

$select = $db->select();
$select->from('test_table', '*');
$select->having('cnt>?', 2);

SELECT `test_table`.* FROM `test_table` HAVING (cnt>2)

また、havingメソッドはwhereメソッドと同様にクォート処理が行われています。

distinctメソッド

重複レコードを取り除くには、distinctメソッドを使います。

$select = $db->select();
$select->distinct();
$select->from('test_table', '*');

SELECT DISTINCT `test_table`.* FROM `test_table`

columnsメソッド

既存の FROM あるいは JOIN テーブルへのカラムの追加するには、columnsメソッドを使います。

クエリを実行する前ならいつでも好きなときに特定のカラムを追加できます。カラムは、文字列あるいは Zend_Db_Expr、あるいはその配列で指定します。このメソッドの 2 番目の引数は省略可能です。省略した場合は、FROM テーブルにカラムが追加されます。指定する場合は、既存の相関名を使用しなければなりません。

$select = $db->select();
$select->from(array('t' => 'test_table'), 'test_id');
$select->columns('test_name');

SELECT t.`test_id`, t.`test_name` FROM `test_table` AS t


$select = $db->select();
$select->from(array('t' => 'test_table'), 'test_id');
$select->columns('test_name', 't');
// または $select->columns('t.test_name');

SELECT t.`test_id`, t.`test_name` FROM `test_table` AS t

unionメソッド

結合クエリを構築するには、columnsメソッドを使います。

Zend_Db_Selectの配列、または SQL クエリ文字列を渡すことによって、 Zend_Db_Selectで結合クエリを構築できます。
どの種類の結合を実行したいか指定するために、第２引数として、Zend_Db_Select::SQL_UNION、またはZend_Db_Select::SQL_UNION_ALL定数を渡せます。

$select1 = $db->select();
$select2 = "SELECT ...";
$select = $db->select()
    ->union(array($select1, $select2))
    ->order("id");

ただし$select1の部分も__toStringメソッドを使用するなどして、純粋なSQL文として渡す必要がある。

forUpdateメソッド

行レベルでロックをするには、forUpdateメソッドを使います。

$select = $db->select();
$select->forUpdate();
$select->from('test_table', '*');

SELECT FOR UPDATE `test_table`.* FROM `test_table`

resetメソッド

SQL クエリの指定した部分のみを消去するには、resetメソッドを使います。
引数を省略した場合は、すべての部分を消去します。

消去したい SQL の部分を、getPart() メソッドの引数と同じ文字列で指定します。クエリの指定した部分が、デフォルトの状態に戻ります。

パラメータを省略すると、reset() はクエリのすべての部分をデフォルトの状態に戻します。

// ORDER句を消去する
$select->reset( Zend_Db_Select::ORDER );

// クエリ全体を消去します
$select->reset();

getPartメソッド

SQLクエリの一部の取得するには、getPartメソッドを使います。

たとえば、このメソッドを使用すると、WHERE 句の式を表す配列や SELECT するカラム (あるいは式) の配列、または LIMIT 句のカウントやオフセットを取得することができます。

返り値は、SQL の一部を抜き取った文字列ではありません。オブジェクトでの内部表現で、通常は値と式を含む配列となります。クエリの各部分によって、その構造は異なります。

getPart() メソッドの引数はひとつで、 Select クエリのどの部分を返すのかをここで指定します。たとえば、文字列 'from' を指定すると、 Select オブジェクトが FROM 句として保持しているテーブルの情報を返します。ここには結合している他のテーブルも含まれます。

Zend_Db_Select クラスでは、SQL クエリの各部分を指定するための定数を定義しています。これらの定数、あるいはリテラル文字列のいずれかで指定することができます。

$select->getPart( 'order' );

$select->getPart( Zend_Db_Select::ORDER );

reset() および getPart() で使用する定数

定数	文字列値
Zend_Db_Select::DISTINCT	'distinct'
Zend_Db_Select::FOR_UPDATE	'forupdate'
Zend_Db_Select::COLUMNS	'columns'
Zend_Db_Select::FROM	'from'
Zend_Db_Select::WHERE	'where'
Zend_Db_Select::GROUP	'group'
Zend_Db_Select::HAVING	'having'
Zend_Db_Select::ORDER	'order'
Zend_Db_Select::LIMIT_COUNT	'limitcount'
Zend_Db_Select::LIMIT_OFFSET	'limitoffset'