PHP Select::from方法代码示例

 protected function _init($itemClass, $keyField, $tableName = '')
 {
     parent::_init($itemClass, $keyField);
     $this->_table = $tableName;
     $this->_select = new Select();
     $this->_select->from(array('main_table' => $tableName));
 }

 /**
  * @param string $entityName
  */
 public function __construct($entityName, Config $config, Select $select = null)
 {
     $this->entityName = $entityName;
     $this->config = $config;
     if ($select === null) {
         $this->select = new Select();
         $this->select->from($this->config->getTable($this->entityName));
     } else {
         $this->select = $select;
     }
 }

 /**
  * 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;
 }

 public function getBlogList()
 {
     $select = new Select();
     $select->from($this->table);
     $select->order('date DESC');
     return new \Zend\Paginator\Paginator(new \Zend\Paginator\Adapter\DbSelect($select, $this->adapter, $this->resultSetPrototype));
 }

 /**
  * Data provider for testGetSqlString
  *
  * @return array
  */
 public function dataProvider()
 {
     $select0 = new Select();
     $select0->from(array('x' => 'foo'));
     $expectedSql0 = 'SELECT "x".* FROM "foo" "x"';
     return array(array($select0, $expectedSql0));
 }

 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;
 }

 /**
  * Get properties
  *
  * @param boolean $forceReload to initiliaze properties
  *
  * @return array
  */
 public function getProperties($forceReload = false)
 {
     if ($this->getData('properties') == null or $forceReload) {
         $select = new Select();
         $select->from('tab')->columns(array())->join('property', 'tab.id = property.tab_id', '*', Select::JOIN_INNER);
         if ($this->getDocumentId() !== null) {
             $select->join('document', 'document.document_type_id = tab.document_type_id', array(), Select::JOIN_INNER);
             $select->join('property_value', 'document.id = property_value.document_id AND property.id = property_value.property_id', array('value'), Select::JOIN_LEFT);
             $select->where(array('document.id' => $this->getDocumentId()));
         }
         if ($this->getTabId() != null) {
             $select->where(array('tab.id' => $this->getTabId()));
         }
         if ($this->getDocumentTypeId() != null) {
             $select->where(array('tab.document_type_id' => $this->getDocumentTypeId()));
         }
         $select->order('property.sort_order ASC');
         $rows = $this->fetchAll($select);
         $properties = array();
         foreach ($rows as $row) {
             $propertyModel = Model::fromArray((array) $row);
             if ($this->getDocumentId() !== null) {
                 $propertyModel->setDocumentId($this->getDocumentId());
             }
             $properties[] = $propertyModel;
         }
         $this->setData('properties', $properties);
     }
     return $this->getData('properties');
 }

 /**
  *
  * @param int $days_threshold days threshold
  * @param int $ca_threshold turnover threshold
  * @param int $limit
  * @return type
  */
 public function getCustomerGeo($days_threshold = 300, $ca_threshold = 1000, $min_accuracy = 6, $limit = 1000)
 {
     $akilia2db = $this->configuration['synchronizer']['db_akilia2'];
     $select = new Select();
     $bcg = new \Zend\Db\Sql\TableIdentifier('base_customer_geo', $akilia2db);
     $bc = new \Zend\Db\Sql\TableIdentifier('base_customer', $akilia2db);
     $bs = new \Zend\Db\Sql\TableIdentifier('base_state', $akilia2db);
     $bco = new \Zend\Db\Sql\TableIdentifier('base_country', $akilia2db);
     $so = new \Zend\Db\Sql\TableIdentifier('sal_order', $akilia2db);
     $sol = new \Zend\Db\Sql\TableIdentifier('sal_order_line', $akilia2db);
     $select->from(["bc" => $bc], [])->join(['bcg' => $bcg], "bc.id = bcg.customer_id", [], Select::JOIN_LEFT)->join(['bs' => $bs], "bs.id = bc.state_id", [], Select::JOIN_LEFT)->join(['bco' => $bco], "bco.id = bc.country_id", [], Select::JOIN_LEFT)->join(['so' => $so], "bc.id = so.customer_id", [], Select::JOIN_INNER)->join(['sol' => $sol], "so.id = sol.order_id", [], Select::JOIN_INNER)->where('bc.flag_archived <> 1');
     $columns = ['customer_id' => new Expression('bc.id'), 'name' => new Expression('bc.name'), 'street' => new Expression('bc.street'), 'street_2' => new Expression('bc.street_2'), 'street_number' => new Expression('bc.street_number'), 'state_reference' => new Expression('bs.reference'), 'state_name' => new Expression('bs.name'), 'zipcode' => new Expression('bc.zipcode'), 'city' => new Expression('bc.city'), 'country' => new Expression('bco.name'), 'accuracy' => new Expression('bcg.accuracy'), 'latitude' => new Expression('bcg.latitude'), 'longitude' => new Expression('bcg.longitude')];
     $select->columns(array_merge($columns, ['total_net' => new Expression('sum(sol.price_total_net)')]), true);
     $select->group($columns);
     $select->having("sum(sol.price_total_net) > {$ca_threshold}");
     $select->where(function (Where $where) use($min_accuracy) {
         //$where->greaterThan('so.date_order', '2012-12-31');
         $where->notLike('bc.name', '%FINISHED%');
         $where->nest->lessThan('accuracy', $min_accuracy)->or->isNull('accuracy')->unnest;
     });
     $select->where(new Expression("(TO_DAYS(NOW()) - TO_DAYS(so.date_order)) < {$days_threshold}"));
     if ($limit > 0) {
         $select->limit($limit);
     }
     $store = $this->getStore($select);
     $data = $store->getData()->toArray();
     return $data;
 }

 public function dataProvider()
 {
     $select0 = new Select();
     $select0->from('foo')->columns(array('bar', 'baz'))->order('bar')->limit(5)->offset(10);
     $expectedPrepareSql0 = 'SELECT [bar], [baz] FROM ( SELECT [foo].[bar] AS [bar], [foo].[baz] AS [baz], ROW_NUMBER() OVER (ORDER BY [bar] ASC) AS [__ZEND_ROW_NUMBER] FROM [foo] ) AS [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION] WHERE [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION].[__ZEND_ROW_NUMBER] BETWEEN ?+1 AND ?+?';
     $expectedParams0 = array('offset' => 10, 'limit' => 5, 'offsetForSum' => 10);
     $expectedSql0 = 'SELECT [bar], [baz] FROM ( SELECT [foo].[bar] AS [bar], [foo].[baz] AS [baz], ROW_NUMBER() OVER (ORDER BY [bar] ASC) AS [__ZEND_ROW_NUMBER] FROM [foo] ) AS [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION] WHERE [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION].[__ZEND_ROW_NUMBER] BETWEEN 10+1 AND 5+10';
     $expectedFormatParamCount0 = 3;
     $select1 = new Select();
     $select1->from('foo')->columns(array('bar', 'bam' => 'baz'))->limit(5)->offset(10);
     $expectedPrepareSql1 = 'SELECT [bar], [bam] FROM ( SELECT [foo].[bar] AS [bar], [foo].[baz] AS [bam], ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [__ZEND_ROW_NUMBER] FROM [foo] ) AS [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION] WHERE [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION].[__ZEND_ROW_NUMBER] BETWEEN ?+1 AND ?+?';
     $expectedParams1 = array('offset' => 10, 'limit' => 5, 'offsetForSum' => 10);
     $expectedSql1 = 'SELECT [bar], [bam] FROM ( SELECT [foo].[bar] AS [bar], [foo].[baz] AS [bam], ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [__ZEND_ROW_NUMBER] FROM [foo] ) AS [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION] WHERE [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION].[__ZEND_ROW_NUMBER] BETWEEN 10+1 AND 5+10';
     $expectedFormatParamCount1 = 3;
     $select2 = new Select();
     $select2->from('foo')->order('bar')->limit(5)->offset(10);
     $expectedPrepareSql2 = 'SELECT * FROM ( SELECT [foo].*, ROW_NUMBER() OVER (ORDER BY [bar] ASC) AS [__ZEND_ROW_NUMBER] FROM [foo] ) AS [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION] WHERE [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION].[__ZEND_ROW_NUMBER] BETWEEN ?+1 AND ?+?';
     $expectedParams2 = array('offset' => 10, 'limit' => 5, 'offsetForSum' => 10);
     $expectedSql2 = 'SELECT * FROM ( SELECT [foo].*, ROW_NUMBER() OVER (ORDER BY [bar] ASC) AS [__ZEND_ROW_NUMBER] FROM [foo] ) AS [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION] WHERE [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION].[__ZEND_ROW_NUMBER] BETWEEN 10+1 AND 5+10';
     $expectedFormatParamCount2 = 3;
     $select3 = new Select();
     $select3->from('foo');
     $expectedPrepareSql3 = 'SELECT [foo].* FROM [foo]';
     $expectedParams3 = array();
     $expectedSql3 = 'SELECT [foo].* FROM [foo]';
     $expectedFormatParamCount3 = 0;
     return array(array($select0, $expectedPrepareSql0, $expectedParams0, $expectedSql0, $expectedFormatParamCount0), array($select1, $expectedPrepareSql1, $expectedParams1, $expectedSql1, $expectedFormatParamCount1), array($select2, $expectedPrepareSql2, $expectedParams2, $expectedSql2, $expectedFormatParamCount2), array($select3, $expectedPrepareSql3, $expectedParams3, $expectedSql3, $expectedFormatParamCount3));
 }

 public function getList($where = array(), $order = null, $offset = null, $limit = null)
 {
     if (empty($where['ProductFilterOption.productCategoryFilterOptionID'])) {
         $select = new Select();
         $select->from(array('b' => 'Product'));
         $select->join(array('c' => 'ProductCategory'), 'b.productCategoryID = c.productCategoryID', array('categoryName'));
         $select->where($where);
         $select->offset($offset);
         $select->limit($limit);
         $select->order($order);
     } else {
         $select = $this->getSelect();
         $select->columns(array())->join(array('b' => 'Product'), 'ProductFilterOption.productID = b.productID')->join(array('c' => 'ProductCategory'), 'b.productCategoryID = c.productCategoryID', array('categoryName'))->where($where)->offset($offset)->limit($limit)->group(array('ProductFilterOption.productID'))->having('count(ProductFilterOption.productID) > ' . (count($where['ProductFilterOption.productCategoryFilterOptionID']) - 1));
         $select->order($order);
     }
     $paginator = $this->paginate($select);
     $paginator->setCurrentPageNumber(ceil($offset / $limit) + 1);
     //$paginator->setItemCountPerPage(1);
     $products = $paginator->getCurrentItems()->getArrayCopy();
     $pages = $paginator->getPages();
     $productsCount = $paginator->getTotalItemCount();
     foreach ($products as $k => $v) {
         $products[$k]['leftTime'] = Utility::getLeftTime(time(), $v['endTime']);
     }
     return array('products' => $products, 'productsCount' => $productsCount, 'pages' => $pages);
 }

 /**
  * {@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 find($params)
 {
     $sql = new Sql($this->getAdapter());
     $select = new Select();
     $select->from($params['from']);
     if (!empty($params['columns'])) {
         $select->columns($params['columns']);
     }
     foreach ($params['where'] as $where) {
         $select->where($where);
     }
     foreach ($params['joins'] as $join) {
         if (empty($join['columns'])) {
             $join['columns'] = Select::SQL_STAR;
         }
         if (empty($join['type'])) {
             $join['type'] = Select::JOIN_INNER;
         }
         $select->join($join['name'], $join['on'], $join['columns'], $join['type']);
     }
     $query = $sql->getSqlStringForSqlObject($select);
     $results = $this->adapter->query($query, Adapter::QUERY_MODE_EXECUTE);
     $data = $results->toArray();
     if (empty($data)) {
         return false;
     } else {
         if (count($data) == 1) {
             return $data[0];
         } else {
             return $data;
         }
     }
 }

 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 getBuilder()
 {
     if (empty($this->parsed['SELECT'])) {
         throw new \InvalidArgumentException('Queries other than SELECT are not supported yet');
     }
     $sql = new Select();
     $sql->columns($this->getBaseExprs($this->parsed['SELECT']));
     foreach ($this->parsed['FROM'] as $table) {
         if (!$table['ref_type']) {
             $sql->from(array($table['alias']['name'] => $table['table']));
             continue;
         }
         if ('JOIN' == $table['join_type']) {
             $table['join_type'] = 'INNER';
         }
         $sql->join(array($table['alias']['name'] => $table['table']), join(' ', $this->getBaseExprs($table['ref_clause'])), array(), $table['join_type']);
     }
     if (isset($this->parsed['GROUP'])) {
         $sql->group($this->getBaseExprs($this->parsed['GROUP']));
     }
     if (isset($this->parsed['WHERE'])) {
         $sql->where(join(' ', $this->getBaseExprs($this->parsed['WHERE'])));
     }
     unset($this->parsed['WHERE'], $this->parsed['GROUP'], $this->parsed['FROM'], $this->parsed['WHERE']);
     var_dump($this->parsed);
     #die('Stopped at ' . __FILE__ . ' on line ' . __LINE__);
     return $sql;
 }

 public function buscarUm($id)
 {
     $id = (int) $id;
     $select = new Select();
     $select->from('clientes')->columns(['id', 'nome', 'email', 'criado', 'modificado'])->where(['id' => $id]);
     $dados = $this->selectWith($select);
     return $dados->current();
 }