ZendFramework2应用实例,使用Zend\Db\TableGateway\TableGateway获取数据

<?php
namespace Application\Controller;

use Zend\Mvc\Controller\AbstractActionController;
use Zend\View\Model\ViewModel;
use Zend\Db\TableGateway\TableGateway;

class IndexController extends AbstractActionController
{

public function indexAction()
    {
$sm = $this->getServiceLocator();
$dbAdapter = $sm->get('Zend\Db\Adapter\Adapter');

$goods_table=new TableGateway("ecm_goods",$dbAdapter);

//这句相当于执行了sql语句:SELECT "ecm_goods".* FROM "ecm_goods" ORDER BY "goods_id" ASC LIMIT '20' OFFSET '10'
$goods_list=$goods_table->select(function($select){
return $select->limit(20)->offset(10)->order("goods_id");
            });


return new ViewModel(array("goods_list"=>$goods_list));
    }

}

 join使用方法

$goods_list=$goods_table->select(function($select){
$select->limit(5)->offset(10)->order("ecm_goods.goods_id")->join("ecm_goods_spec","ecm_goods.goods_id=ecm_goods_spec.goods_id");
return $select;
            });

执行后sql语句如下

SELECT "ecm_goods".*, "ecm_goods_spec".* FROM "ecm_goods" INNER JOIN "ecm_goods_spec" ON "ecm_goods"."goods_id"="ecm_goods_spec"."goods_id" ORDER BY "ecm_goods"."goods_id" ASC LIMIT '5' OFFSET '10'

别名使用方法

$goods_list=$goods_table->select(function($select){
$select->limit(5)->offset(10)->order("ecm_goods.goods_id")->join(array("gs"=>"ecm_goods_spec"),"ecm_goods.goods_id=gs.goods_id");
return $select;
            });

执行后sql语句如下，暂时只实现在join方法里实现别名,如果是自己生成的select对象可以通过$select->from(array("g"=>"ecm_goods"))来使用别名

SELECT "ecm_goods".*, "gs".* FROM "ecm_goods" INNER JOIN "ecm_goods_spec" AS "gs" ON "ecm_goods"."goods_id"="gs"."goods_id" ORDER BY "ecm_goods"."goods_id" ASC LIMIT '5' OFFSET '10'

left join 和 right join  等使用方法

$select->join("ecm_goods_spec","ecm_goods.goods_id=ecm_goods_spec.goods_id",$select::SQL_STAR,$select::JOIN_LEFT);
JOIN_INNER  JOIN_OUTER  JOIN_LEFT  JOIN_RIGHT