ZF2を使っていろいろなSELECT文を生成する

たまにZF2を使ったりするけど、いつも使い方忘れて調べてる気がするのでメモとして残しておく、ちなみにMysqlを想定

#アダプタの生成

$adapter = new \Zend\Db\Adapter\Adapter(array(
            'driver'        => 'Pdo',
            'dsn'           => 'mysql:dbname=DBの名前;host=ホスト名',
            'username'      => 'ユーザ名',
            'password'      => 'パスワード',
            'driver_option' => array(
                \PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''
            ),
        ));

#TableGatewayの生成

 //特に指定しないと結果はarrayobjectになる
 $tableGateway = new \Zend\Db\TableGateway\TableGateway('テーブル名', $adapter);
 
 //配列にしたい場合はこんな感じ
 $resultSet    = new \Zend\Db\ResultSet\ResultSet(ResultSet::TYPE_ARRAY);
 $tableGateway = new \Zend\Db\TableGateway\TableGateway('テーブル名', $adapter, null, $resultSet);

#SELECT

select実行

$tableGateway = new \Zend\Db\TableGateway\TableGateway('user', $adapter);
$select = $tableGateway->getSql()->select();
$res = $tableGateway->selectWith($select);

条件指定

$select->where->equalTo('col', '値');
//SELECT "user".* FROM "user" WHERE "col" = '値'

$select->where->notEqualTo('col', '値')
//SELECT "user".* FROM "user" WHERE "col" != '値'

$select->where->greaterThan('col', '値');
//SELECT "user".* FROM "user" WHERE "col" > '値'

$select->where->greaterThanOrEqualTo('col', '値');
//SELECT "user".* FROM "user" WHERE "col" >= '値'

$select->where->lessThan('col', '値');
//SELECT "user".* FROM "user" WHERE "col" < '値'

$select->where->lessThanOrEqualTo('col', '値');
//SELECT "user".* FROM "user" WHERE "col" <= '値'

$select->where->in('col', array('値1', '値2'));
//SELECT "user".* FROM "user" WHERE "col" IN ('値1', '値2')

$select->where->isNull('col');
//SELECT "user".* FROM "user" WHERE "col" IS NULL

$select->where->isNotNull('col')
//SELECT "user".* FROM "user" WHERE "col" IS NOT NULL

$select->where->between('col', 'min', 'max');
//SELECT "user".* FROM "user" WHERE "col" BETWEEN 'min' AND 'max'

$select->where->equalTo('col', '値')
              ->or
              ->equalTo('col2', '値2');
//SELECT "user".* FROM "user" WHERE "col" = '値' OR "col2" = '値2'

$select->where->equalTo('col', '値1')
              ->NEST
              ->equalTo('col2', '値2')
              ->or
              ->equalTo('col3', '値3')
              ->UNNEST;
//SELECT "user".* FROM "user" WHERE "col" = '値1' AND ("col2" = '値2' OR "col3" = '値3')

$select->where->like('col', '%値1%');
//SELECT "user".* FROM "user" WHERE "col" LIKE '%値1%'

$select->where->notLike('col', '%値1%');
//SELECT "user".* FROM "user" WHERE "col" NOT LIKE '%値1%'

カラムの指定

 $select->columns(array('col1','col2'));
 //SELECT "user"."col1" AS "col1", "user"."col2" AS "col2" FROM "user"
 
 $select->quantifier(\Zend\Db\Sql\Select::QUANTIFIER_DISTINCT)
        ->columns(array('col1','col2'));
 //SELECT DISTINCT "user"."col1" AS "col1", "user"."col2" AS "col2" FROM "user"
 
 $select->columns(array('colcol' => 'col1'));
 //SELECT "user"."col1" AS "colcol" FROM "user"
 
 $select->columns(array('count' => new \Zend\Db\Sql\Predicate\Expression('COUNT(*)')));
 //SELECT COUNT(*) AS "count" FROM "user"

FROM句にサブクエリ

 $select = $tableGateway->getSql()->select();
 $select2 = new \Zend\Db\Sql\Select();
 $select2->from(array('select' => $select));
 //SELECT "select".* FROM (SELECT "user".* FROM "user") AS "select"

union

$select = $tableGateway->getSql()->select();
$select2 = new \Zend\Db\Sql\Select();
$select2->from('user');
$select2->combine($select);
//( SELECT "user".* FROM "user" ) UNION ( SELECT "user".* FROM "user" )

join

$select->join('table', 'user.id = table.id', array('col'));
//SELECT "user".*, "table"."col" AS "col" FROM "user" INNER JOIN "table" ON "user"."id" = "table"."id"

$select->join('table', 'user.id = table.id', array('col'), $select::JOIN_LEFT);
//SELECT "user".*, "table"."col" AS "col" FROM "user" LEFT JOIN "table" ON "user"."id" = "table"."id"