PHP Sql\Select类代码示例

 public function search($str)
 {
     $select = new Select('video');
     $spec = new Where();
     $spec->like('title', '% ' . $str . ' %');
     $select->where($spec);
     $select->where->OR->like('title', $str . ' %');
     $select->where->OR->like('title', '% ' . $str);
     //$select->where(array('title'=>$str));
     //$select->where(array('catelog'=>$title,'description'=>$description));
     $select->order('id ASC');
     /*
                  // create a new result set based on the Album entity
                  $resultSetPrototype = new ResultSet();
                  $resultSetPrototype->setArrayObjectPrototype(new Search());
                  // create a new pagination adapter object
                  $paginatorAdapter = new DbSelect(
                      // our configured select object
                      $select,
                      // the adapter to run it against
                      $this->tableGateway->getAdapter(),
                      // the result set to hydrate
                      $resultSetPrototype
                  );
                  $paginator = new Paginator($paginatorAdapter);
                 
                  return $paginator;
     */
     return $this->tableGateway->selectWith($select);
 }

 public function setUp()
 {
     $this->column = $this->getMockBuilder('ZfcDatagrid\\Column\\Select')->disableOriginalConstructor()->getMock();
     $this->column->method('getSelectPart1')->willReturn('myCol');
     $this->column->method('getType')->willReturn(new \ZfcDatagrid\Column\Type\PhpString());
     $this->column->setUniqueId('myCol');
     $this->column->setSelect('myCol');
     $this->column2 = $this->getMockBuilder('ZfcDatagrid\\Column\\Select')->disableOriginalConstructor()->getMock();
     $this->column2->method('getSelectPart1')->willReturn('myCol2');
     $this->column2->method('getType')->willReturn(new \ZfcDatagrid\Column\Type\PhpString());
     $this->column2->setUniqueId('myCol2');
     $this->column2->setSelect('myCol2');
     $this->mockDriver = $this->getMock('Zend\\Db\\Adapter\\Driver\\DriverInterface');
     $this->mockConnection = $this->getMock('Zend\\Db\\Adapter\\Driver\\ConnectionInterface');
     $this->mockDriver->expects($this->any())->method('checkEnvironment')->will($this->returnValue(true));
     $this->mockDriver->expects($this->any())->method('getConnection')->will($this->returnValue($this->mockConnection));
     $this->mockPlatform = $this->getMock('Zend\\Db\\Adapter\\Platform\\PlatformInterface');
     $this->mockStatement = $this->getMock('Zend\\Db\\Adapter\\Driver\\StatementInterface');
     $this->mockDriver->expects($this->any())->method('createStatement')->will($this->returnValue($this->mockStatement));
     $this->adapter = new Adapter($this->mockDriver, $this->mockPlatform);
     $sql = new Sql($this->adapter, 'foo');
     $select = new Select('myTable');
     $select->columns(['myCol', 'myCol2']);
     $this->filterSelect = new FilterSelect($sql, $select);
 }

 /**
  * Sets the filter
  * @param \Zend\Db\Sql\Select $select
  * @param array $criteria
  * @return \Zend\Db\Sql\Select
  */
 private function setFilter($select, $criteria)
 {
     if (array_key_exists('profile_id', $criteria) && $criteria['profile_id'] != '') {
         $select->where('`profile_id` = ' . intval($criteria['profile_id']));
     }
     return $select;
 }

 public function fetchActive()
 {
     $select = new Select($this->getTable());
     $select->where->equalTo('active', 1);
     $select->order('id desc');
     return $this->selectWith($select)->toArray();
 }

 public function getUsersAndAbove(bool $paginated, $name = '', $roles = [])
 {
     $select = new Select('account');
     $where = new Where();
     if ($roles) {
         $sub = $where->nest();
         for ($i = 0; $i < count($roles); $i++) {
             $sub->equalTo('role', $roles[$i]);
             if ($i < count($roles) - 1) {
                 $sub->or;
             }
         }
         $sub->unnest();
     } else {
         $where->greaterThan('role', '0');
         $where->lessThan('role', '32');
     }
     if ($name) {
         $where->like('name', '%' . $name . '%');
     }
     $select->where($where)->order('name ASC');
     if ($paginated) {
         $resultSetPrototype = new ResultSet();
         $resultSetPrototype->setArrayObjectPrototype(new Account());
         $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
         return new Paginator($paginatorAdapter);
     }
     return $this->tableGateway->select($select);
 }

 public function fetchByIdCategoryWithLimit($idCategory, $limit)
 {
     $select = new Select();
     $select->from('post')->where('id_category = ' . (int) $idCategory)->limit((int) $limit);
     $resultSet = $this->tableGateway->select($select);
     return $resultSet;
 }

 /**
  *
  * @param \Zend\Db\Sql\Select $query
  */
 protected function initFilters($query)
 {
     $value = $this->getParamAdapter()->getValueOfFilter('idItem');
     if ($value != null) {
         $query->where("idItem = '" . $value . "' ");
     }
     $value = $this->getParamAdapter()->getValueOfFilter('nom');
     if ($value != null) {
         $query->where("nom like '%" . $value . "%' ");
     }
     $value = $this->getParamAdapter()->getValueOfFilter('ajouterPar');
     if ($value != null) {
         $query->where("ajouterPar like '%" . $value . "%' ");
     }
     $value = $this->getParamAdapter()->getValueOfFilter('majPar');
     if ($value != null) {
         $query->where("majPar like '%" . $value . "%' ");
     }
     $value = $this->getParamAdapter()->getValueOfFilter('idItemJeu');
     if ($value != null) {
         $query->where("idIBnet like '%" . $value . "%' ");
     }
     $value = $this->getParamAdapter()->getValueOfFilter('couleur');
     if ($value != null) {
         $query->where("couleur like '%" . $value . "%' ");
     }
 }

 public function getByProduct($product)
 {
     $select = new Select($this->tableGateway->getTable());
     $select->where(array("product" => $product));
     $rows = $this->tableGateway->selectWith($select);
     return $rows ? $rows : false;
 }

 /**
  * {@inheritDoc}
  */
 public function getRoles()
 {
     /* @var $tableGateway \Zend\Db\TableGateway\TableGateway */
     $tableGateway = $this->serviceLocator->get('BjyAuthorize\\Service\\RoleDbTableGateway');
     $sql = new Select();
     $sql->from($this->tableName);
     /* @var $roles Role[] */
     $roles = array();
     $indexedRows = array();
     $rowset = $tableGateway->selectWith($sql);
     // Pass 1: collect all rows and index them by PK
     foreach ($rowset as $row) {
         $indexedRows[$row[$this->identifierFieldName]] = $row;
     }
     // Pass 2: build a role for each indexed row
     foreach ($indexedRows as $row) {
         $parentRoleId = isset($row[$this->parentRoleFieldName]) ? $indexedRows[$row[$this->parentRoleFieldName]][$this->roleIdFieldName] : null;
         $roleId = $row[$this->roleIdFieldName];
         $roles[$roleId] = new Role($roleId, $parentRoleId);
     }
     // Pass 3: Re-inject parent objects to preserve hierarchy
     foreach ($roles as $role) {
         $parentRoleObj = $role->getParent();
         if ($parentRoleObj && ($parentRoleId = $parentRoleObj->getRoleId())) {
             $role->setParent($roles[$parentRoleId]);
         }
     }
     return array_values($roles);
 }

 public function fetchAll($paginated = false, $sort = 'name', $order = 'ASC')
 {
     switch ($sort) {
         case "id":
             $sort = "C_Id";
             break;
         case "name":
             $sort = "C_Name";
             break;
         case "lastinvoice":
             $sort = "C_LastInvoice";
             break;
         default:
             $sort = "C_Name";
     }
     $select = new Select();
     $select->from($this->table);
     $subquery = "(SELECT I_BaseDate FROM Invoice WHERE Invoice.I_ClientId = Client.C_Id ORDER BY I_Id DESC LIMIT 1)";
     $select->columns(array('C_Id' => 'C_Id', 'C_Name' => 'C_Name', 'C_PaymentTerms' => 'C_PaymentTerms', 'C_Reference' => 'C_Reference', 'C_CRN' => 'C_CRN', 'C_Class' => 'C_Class', 'C_Updated' => 'C_Updated', 'C_LastInvoice' => new Expression($subquery)))->order($sort . ' ' . $order);
     if ($paginated) {
         return new Paginator(new DbSelect($select, $this->adapter, $this->resultSetPrototype));
     }
     $resultSet = $this->select($select);
     return $resultSet;
 }

 public function fetchAll($paginate = true, $usertype = 8)
 {
     if ($paginate) {
         switch ($usertype) {
             case 3:
                 $select = new Select('users');
                 $select->join('lookup_status', 'lookup_status.status_id = users.status_id', array('status'), 'left');
                 $select->join('user_feature_setting', 'user_feature_setting.user_id = users.id', array(), 'left');
                 $select->where('users.user_type_id = 3 and user_feature_setting.newsletter in (1,4)');
                 break;
             case 4:
                 $select = new Select('users');
                 $select->join('lookup_status', 'lookup_status.status_id = users.status_id', array('status'), 'left');
                 $select->join('user_feature_setting', 'user_feature_setting.user_id = users.id', array(), 'left');
                 $select->where('users.user_type_id = 4 and user_feature_setting.newsletter in (1,4)');
                 break;
             case 8:
                 $select = new Select('newsletter_subscription');
                 $select->join('lookup_status', 'lookup_status.status_id = newsletter_subscription.status_id', array('status'), 'left');
                 break;
             default:
                 $select = new Select('newsletter_subscription');
                 $select->join('lookup_status', 'lookup_status.status_id = newsletter_subscription.status_id', array('status'), 'left');
                 break;
         }
         $resultSetPrototype = new ResultSet();
         $resultSetPrototype->setArrayObjectPrototype(new NewsletterSubscribers());
         $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
         $paginator = new Paginator($paginatorAdapter);
         return $paginator;
     }
     return $this->tableGateway->select();
 }

 function fetchAll($beginning, $limit)
 {
     $select = new Select($this->tableGateway->getTable());
     $select->limit($limit)->offset($beginning);
     $rowSet = $this->tableGateway->selectWith($select);
     return $rowSet;
 }

 public function fetchByIdCategoryWithLimit($idCategory, $limit = 10, $nbDays = 30)
 {
     $select = new Select($this->tableGateway->getTable());
     $select->where("id_category = {$idCategory}")->where("created_at >= '" . date('Y-m-d H:i:s', time() - 86400 * $nbDays) . "'")->limit($limit);
     $resultset = $this->tableGateway->selectWith($select);
     return $resultset->toArray();
 }

 /**
  * Méthode get : récupère un ou plusieurs pays
  * 
  * @param string $code
  * @return array
  * @throws \Exception
  */
 public function getPays($code = "", $returnObject = false)
 {
     $select = new Select();
     $where = null;
     $result = [];
     try {
         $select->from('pays');
         if ($code) {
             $where = $this->constructWhereFromCode($code);
             $select->where($where);
         }
         $rowset = $this->tableGateway->selectWith($select);
         if ($rowset->count() > 1) {
             for ($i = 0; ($row = $rowset->current()) && $i < 30; $rowset->next(), $i++) {
                 $result[] = $row->toArray();
             }
         } else {
             if ($rowset->count() > 0) {
                 if ($returnObject) {
                     $result[] = $rowset->current();
                 } else {
                     $result[] = $rowset->current()->toArray();
                 }
             }
         }
         if (empty($result)) {
             throw new \Exception("Nous n'avons pas trouve le pays possedant ce code, alpha2 ou alpha3 egal  {$code}");
         }
     } catch (\Exception $e) {
         $result['error'] = $e->getMessage();
     }
     return $result;
 }

 /**
  *
  * @param \Zend\Db\Sql\Select $query
  */
 protected function initFilters($query)
 {
     $value = $this->getParamAdapter()->getValueOfFilter('idGuildes');
     if ($value != null) {
         $query->where("idGuildes = '" . $value . "' ");
     }
     $value = $this->getParamAdapter()->getValueOfFilter('nom');
     if ($value != null) {
         $query->where("nom like '%" . $value . "%' ");
     }
     $value = $this->getParamAdapter()->getValueOfFilter('serveur');
     if ($value != null) {
         $query->where("serveur like '%" . $value . "%' ");
     }
     $value = $this->getParamAdapter()->getValueOfFilter('niveau');
     if ($value != null) {
         $query->where("niveau like '%" . $value . "%' ");
     }
     $value = $this->getParamAdapter()->getValueOfFilter('miniature');
     if ($value != null) {
         $query->where("miniature like '%" . $value . "%' ");
     }
     $value = $this->getParamAdapter()->getValueOfFilter('idFaction');
     if ($value != null) {
         $query->where("idFaction = '" . $value . "' ");
     }
 }