//  ------ 所谓的谓词 ------
// 条件 case.3
$where = new \Zend\Db\Sql\Where();
$expression = new \Zend\Db\Sql\Predicate\Expression("field1=? and field2=?",array(‘a‘,‘b‘));
$where->addPredicates($expression,\Zend\Db\Sql\Predicate\PredicateSet::OP_OR);

// 条件 case.4
$predicate = new \Zend\Db\Sql\Predicate\Predicate();
$predicate = new \Zend\Db\Sql\Predicate\Expression("field1=? and field2=?",array(‘a‘,‘b‘));
$where->addPredicates($predicate,\Zend\Db\Sql\Predicate\PredicateSet::OP_OR);

/**
 * 所谓的添加、删除、修改、查询
 * @author zhouzhian
 *
 */
class CrudTest{
    
    /**
     * 下面这个代码能执行的条件是：
     *         有注入“服务管理器”，即：可正常调用$this->getServiceLocator()
     */
    public function testTableGateway(){
        $dbAdapter = $this->getServiceLocator()->get(‘Zend\Db\Adapter\Adapter‘);
        $resultSetPrototype = new \Zend\Db\ResultSet\ResultSet();
        $resultSetPrototype->setArrayObjectPrototype(new \Album\Model\Album());
        $tableGateway = new \Zend\Db\TableGateway\TableGateway(‘album‘, $dbAdapter, null, $resultSetPrototype);
    }
    
    /**
     * 条件
     *         查询条件、删除条件、修改条件、查询插入条件
     */
    public function testWhere(){
        // ---------条件---------
        $id = 0;
        
        // 条件 case.0
        $where = array(‘id‘ => $id);
        
        // 条件 case.1
        $where = new \Zend\Db\Sql\Where();
        $where->addPredicates(array(‘id‘ => $id), \Zend\Db\Sql\Predicate\PredicateSet::OP_AND);
        
        // 条件 case.2
        $where = new \Zend\Db\Sql\Where();
        $where->addPredicates(array(
            ‘field1=? and field2=?‘ => array(‘a‘,‘b‘),// field1=a and field2=b
            ‘field2‘ => null, // field2 IS NULL
            ‘field3‘ => array(1,2,3), // field3 in(1,2,3)
            ‘field4‘ => ‘4‘ // field4=4
        ), \Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
        
        // 条件 case.3
        $where = new \Zend\Db\Sql\Where();
        $expression = new \Zend\Db\Sql\Predicate\Expression("field1=? and field2=?",array(‘a‘,‘b‘));
        $where->addPredicates($expression,\Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
        
        // 条件 case.4
        $predicate = new \Zend\Db\Sql\Predicate\Predicate();
        $predicate = new \Zend\Db\Sql\Predicate\Expression("field1=? and field2=?",array(‘a‘,‘b‘));
        $where->addPredicates($predicate,\Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
        
        // 条件 case.5
        // WHERE "field0" IS NULL OR "field3" IN (:where1, :where2, :where3) OR (field3=:where4 and field4=:where5)
        $where1 = new \Zend\Db\Sql\Where();
        $expression1 = new \Zend\Db\Sql\Predicate\Expression("field3=? and field4=?",array(‘a‘,‘b‘));
        $where1->addPredicates($expression1,\Zend\Db\Sql\Predicate\PredicateSet::OP_AND);
        $condition = array(
            ‘field0‘ => null,
            ‘field3‘ => array(1,2,3),
            $where1,
        );
        $where = new \Zend\Db\Sql\Where();
        $where->addPredicates($condition,\Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
    }
    
    /**
     * 查询
     */
    public function testSelect(){
        $where = $this->testWhere();
        
         // ---------查询---------
        // 执行 case.0，这种方式不能自己指定要获取的列
        // SELECT "album".* FROM "album" WHERE field1=:where1 and field2=:where2
        $resultSet = $this->tableGateway->select($where);
        
        // 执行 case.1，指定要查询的列
        // SELECT "album"."field1" AS "field1", "album"."field2" AS "field2_alias" FROM "album" WHERE field1=:where1 and field2=:where2
        $select = $this->tableGateway->getSql()->select();
        $select->columns(array(‘field1‘,‘field2_alias‘=>‘field2‘));
        $select->where($where); // where ...
        $resultSet = $this->tableGateway->selectWith($select); // $rowset === Zend\Db\ResultSet\ResultSet
        
        // 执行 case.2,指定别名
        // SELECT "table1_alias"."field1" AS "field1", "table1_alias"."field2" AS "field2_alias" FROM "album" AS "table1_alias" WHERE field1=:where1 and field2=:where2
        $select = new \Zend\Db\Sql\Select();
        $select->from(array(‘table1_alias‘=>‘album‘));
        $select->columns(array(‘field1‘,‘field2_alias‘=>‘field2‘));
        $select->where($where); // where ...
        $resultSet = $this->tableGateway->selectWith($select);
        
        // 执行 case.3，分组、排序、分页限制
//         SELECT "album"."field1" AS "field1", "album"."field2" AS "field2_alias"
//          　     FROM "album" WHERE field1=:where1 and field2=:where2
//          　     GROUP BY "field1", "field2"
//          　     ORDER BY "field1" ASC, "field2" DESC
//          　     LIMIT :limit OFFSET :offset
        $select = $this->tableGateway->getSql()->select();
        $select->columns(array(‘field1‘,‘field2_alias‘=>‘field2‘));
        $select->where($where);
        $select->group(array(‘field1‘,‘field2‘)); // group by ....
        $select->order(array(‘field1‘=>‘ASC‘,‘field2‘=>‘DESC‘)); // $select->order(‘field1 ASC, field2 DESC‘); 逗号后面要带个空格
        $select->offset(1);
        $select->limit(10);
        $resultSet = $this->tableGateway->selectWith($select);
        
        // ---关联---
//         SELECT "table1_alias"."t1_field1" AS "t1_field1", "table1_alias"."t1_field2" AS "t1_field2_alias", "table2_alias"."t2_field1" AS "t2_field1", "table2_alias"."t2_field2" AS "t2_field2", "table3_alias".*
//             FROM "album" AS "table1_alias"
//             LEFT JOIN "table2" AS "table2_alias" ON "table2_alias"."t1_field1_ref" AND "table1_alias"."t1_field1"
//             LEFT JOIN "table3" AS "table3_alias" ON "table3_alias"."t1_field1_ref" AND "table1_alias"."t1_field1"
//             WHERE field1=:where1 and field2=:where2
//             GROUP BY "t1_field1", "t1_field2"
//             ORDER BY "t1_field1" ASC, "t1_field2" DESC
//             LIMIT :limit OFFSET :offset
        $select = new \Zend\Db\Sql\Select();
        $select->from(array(‘table1_alias‘=>‘album‘));
        $select->columns(array(‘t1_field1‘,‘t1_field2_alias‘=>‘t1_field2‘));
        $select->where($where);
        $select->group(array(‘t1_field1‘,‘t1_field2‘));
        $select->join(array(‘table2_alias‘=>‘table2‘), ‘table2_alias.t1_field1_ref AND table1_alias.t1_field1‘, array(‘t2_field1‘,‘t2_field2‘) ,‘LEFT‘);
        $select->join(array(‘table3_alias‘=>‘table3‘), ‘table3_alias.t1_field1_ref AND table1_alias.t1_field1‘, ‘*‘ ,‘LEFT‘);
        $select->order(array(‘t1_field1‘=>‘ASC‘,‘t1_field2‘=>‘DESC‘));
        $select->offset(1);
        $select->limit(10);
        $resultSet = $this->tableGateway->selectWith($select);
        
        // ---子查询---
//         执行 case.1，【子查询作为条件】
//         SELECT *  from pg_user where id in(SELECT id  from pg_user);
        $selectSubTable = new \Zend\Db\Sql\Select();
        $selectSubTable->columns(array(‘t2_field1‘));
        $selectSubTable->where($where);
        $selectSubTable->from(array(‘table2_alias‘=>‘table2‘));
        $select = new \Zend\Db\Sql\Select();
        $select->columns(array(‘t1_field1‘,‘t1_field2_alias‘=>‘t1_field2‘));
        $select->where(array(‘id‘=>array($selectSubTable)));
        $select->from(array(‘table1_alias‘=>‘album‘));
        $select->limit(10);
        $resultSet = $this->tableGateway->selectWith($select);
        
//         执行 case.2，【子查询作为表】子查询出列表，再从列表中查询
//         SELECT "sub_table2_alias"."t1_field1" AS "t1_field1", "sub_table2_alias"."t1_field2" AS "t1_field2_alias"
//                 FROM (SELECT "table2_alias"."t2_field1" AS "t2_field1", "table2_alias"."t2_field2" AS "t2_field2_alias" FROM "table2" AS "table2_alias") AS "sub_table2_alias"
//                         LIMIT :limit
        $selectSubTable = new \Zend\Db\Sql\Select();
        $selectSubTable->columns(array(‘t2_field1‘,‘t2_field2_alias‘=>‘t2_field2‘));
        $selectSubTable->from(array(‘table2_alias‘=>‘table2‘));
        { // build $tableGateway
            $dbAdapter = $this->getServiceLocator()->get(‘Zend\Db\Adapter\Adapter‘);
            $resultSetPrototype = new \Zend\Db\ResultSet\ResultSet();
            $resultSetPrototype->setArrayObjectPrototype(new \Album\Model\Album());
            $tableGateway = new \Zend\Db\TableGateway\TableGateway(array(‘sub_table2_alias‘=>$selectSubTable), $dbAdapter, null, $resultSetPrototype);
        }
        $select = new \Zend\Db\Sql\Select();
        $select->columns(array(‘t1_field1‘,‘t1_field2_alias‘=>‘t1_field2‘));
        $select->from(array(‘sub_table2_alias‘=>$selectSubTable));
        $select->limit(10);
        $resultSet = $tableGateway->selectWith($select);
        
//         执行 case.3，【子查询作为字段】子查询只能返回单条
//         SELECT "table1_alias"."t1_field1" AS "t1_field1", "table1_alias"."t1_field2" AS "t1_field2_alias", (SELECT "table2_alias"."t2_field2" AS "t2_field2_alias" FROM "table2" AS "table2_alias") AS "sub_field2_alias"
//                 FROM "album" AS "table1_alias" LIMIT :limit
         $selectSub = new \Zend\Db\Sql\Select();
         $selectSub->from(array(‘table2_alias‘=>‘table2‘));
         $selectSub->columns(array(‘t2_field2_alias‘=>‘t2_field2‘)); // 只能有一列
         $select = new \Zend\Db\Sql\Select();
         $select->from(array(‘table1_alias‘=>‘album‘));
         $select->columns(array(‘t1_field1‘,‘t1_field2_alias‘=>‘t1_field2‘,‘sub_field2_alias‘=>$selectSub));
         $select->limit(10);
         $resultSet = $this->tableGateway->selectWith($select);
        
        // ---联合查询---
//         ( SELECT "table1_alias"."t1_field1" AS "t1_field1", "table1_alias"."t1_field2" AS "field2_alias" FROM "album" AS "table1_alias" )
//             UNION ALL ( SELECT "table2_alias"."t2_field1" AS "t2_field1", "table2_alias"."t2_field2" AS "field2_alias" FROM "table2" AS "table2_alias" )
        $select = new \Zend\Db\Sql\Select();
        $select->from(array(‘table1_alias‘=>‘album‘));
        $select->columns(array(‘t1_field1‘,‘t1_field2_alias‘=>‘t1_field2‘));
        $select1 = new \Zend\Db\Sql\Select();
        $select1->from(array(‘table2_alias‘=>‘table2‘));
        $select1->columns(array(‘t2_field1‘,‘t2_field2_alias‘=>‘t2_field2‘));
        $select->where($where);
        $select->combine($select1,‘UNION ALL‘,‘‘);
        $resultSet = $this->tableGateway->selectWith($select);
        
        //　---统计---
//         SELECT "album"."count""("*")" AS "count_total" FROM "album"
        $select = $this->tableGateway->getSql()->select();
        $select->columns(array(‘count_total‘=>new \Zend\Db\Sql\Expression(‘count(*)‘)));
        $resultSet = $this->tableGateway->selectWith($select);
        
        //　---------结果集---------
        //　---单条---
        $itemRecord = $resultSet->current();
        echo $itemRecord->field1;
        
        //　---列表---
        $itemRecordList = $resultSet;
        foreach ($itemRecordList as $itemRecord){
            echo $itemRecord->field1;
        }
    }
    
    /**
     * 插入
     */
    public function testInsert(){
        $where = $this->testWhere();
        //　---------添加---------
        // case.0
        // INSERT INTO "album" ("field1", "field2") VALUES (:field1, :field2)
        $data = array(
            ‘field1‘ =>‘field1_value‘,
            ‘field2‘  =>‘field2_value‘,
        );
        $affectedRows = $this->tableGateway->insert($data);
        
        // case.1
        // INSERT INTO "album" ("field1", "field2") VALUES (:field1, :field2)
        $insert = $this->tableGateway->getSql()->insert();
        $insert->values($data);
        $affectedRows = $this->tableGateway->insertWith($insert);
        
        // case.2 查询插入
        // INSERT INTO "album" ("des_table_field1", "des_table_field2") SELECT "table1_alias"."field1" AS "field1", "table1_alias"."field2" AS "field2_alias" FROM "table1" AS "table1_alias" WHERE ...
        $select = new \Zend\Db\Sql\Select();
        $select->columns(array(‘field1‘,‘field2_alias‘=>‘field2‘));
        $select->where($where);
        $select->from(array(‘table1_alias‘=>‘table1‘));
        
        $insert = $this->tableGateway->getSql()->insert();
        $insert->select($select);
        $insert->into(‘album‘);
        $insert->columns(array(‘des_table_field1‘,‘des_table_field2‘));
        $affectedRows = $this->tableGateway->insertWith($insert);
    }
    
    /**
     * 删除
     */
    public function testDelete(){
        $where = $this->testWhere();
        //　---------删除---------
        // case.0
        // DELETE FROM "album" WHERE ...
        $affectedRows = $this->tableGateway->delete($where);
    
        // case.1
        // DELETE FROM "album" WHERE ...
        $delete = $this->tableGateway->getSql()->delete();
        $delete->where($where);
        $affectedRows = $this->tableGateway->deleteWith($delete);
    }
    
    /**
     * 修改
     */
    public function testUpdate(){
        $where = $this->testWhere();
        //　---------修改---------
        $data = array(
            ‘field1‘ =>‘field1_value‘,
            ‘field2‘  =>‘field2_value‘,
        );
        // case.0
        // UPDATE "album" SET "field1" = :field1, "field2" = :field2 WHERE ...
        $affectedRows = $this->tableGateway->update($data, $where);
        
        // case.1
        // UPDATE "album" SET "field1" = :field1, "field2" = :field2 WHERE ...
        $update = $this->tableGateway->getSql()->update();
        $update->set($data);
        $update->where($where);
        $affectedRows = $this->tableGateway->updateWith($update);
    }
    
}