PHP Zend_Db_Table_Select::from方法代码示例

 public function onStatistics($event)
 {
     $table = Engine_Api::_()->getDbTable('playlists', 'music');
     $select = new Zend_Db_Table_Select($table);
     $select->from($table->info('name'), array('COUNT(*) AS count'));
     $event->addResponse($select->query()->fetchColumn(0), 'playlist');
     $table = Engine_Api::_()->getDbTable('playlistSongs', 'music');
     $select = new Zend_Db_Table_Select($table);
     $select->from($table->info('name'), array('COUNT(*) AS count'));
     $event->addResponse($select->query()->fetchColumn(0), 'song');
 }

 /**
  * Returns an instance of a Zend_Db_Table_Select object.
  *
  * @param bool $withFromPart Whether or not to include the from part of the select based on the table
  * @return Zend_Db_Table_Select
  */
 public function select($withFromPart = self::SELECT_WITHOUT_FROM_PART)
 {
     require_once 'Zend/Db/Table/Select.php';
     $select = new Zend_Db_Table_Select($this);
     if ($withFromPart == self::SELECT_WITH_FROM_PART) {
         $select->from($this->info(self::NAME), Zend_Db_Table_Select::SQL_WILDCARD, $this->info(self::SCHEMA));
     }
     return $select;
 }

 /**
 * Adds a FROM table and optional columns to the query.
 *
 * The table name can be expressed
 *
 * @param  array|string|Zend_Db_Expr|Zend_Db_Table_Abstract $name The table name or an
                                                                  associative array relating
                                                                  table name to correlation
                                                                  name.
 * @param  array|string|Zend_Db_Expr $cols The columns to select from this table.
 * @param  string $schema The schema name to specify, if any.
 * @return Axis_Db_Table_Select This Axis_Db_Table_Select object.
 */
 public function from($name, $cols = Zend_Db_Table_Select::SQL_WILDCARD, $schema = null)
 {
     parent::from($name, $cols, $schema);
     //        $from = $this->_parts[self::FROM];
     //        if (1 < count($from)) {
     //            $keys = array_keys($from);
     //            $this->_parts[self::FROM] =
     //                array(array_pop($keys) => array_pop($from)) + $from;
     //        }
     return $this;
 }

 /**
  * Get DataTable data
  *
  * @param Zend_Db_Table_Select $select
  * @param array|null $columns
  * @return array
  */
 public function direct(Zend_Db_Table_Select $select, $columns = null)
 {
     $table = $select->getTable();
     if (!$columns) {
         $columns = $table->info(Zend_Db_Table_Abstract::COLS);
     }
     $colCount = count($columns);
     $request = $this->getRequest();
     /*
      * Ordering
      */
     if (null !== $request->getParam('iSortCol_0', null)) {
         for ($i = 0, $l = $request->getParam('iSortingCols'); $i < $l; $i++) {
             if ($request->getParam('bSortable_' . (int) $request->getParam('iSortCol_' . $i))) {
                 $select->order($columns[(int) $request->getParam('iSortCol_' . $i)] . " " . $request->getParam('sSortDir_' . $i));
             }
         }
     }
     /*
      * Filtering
      * NOTE this does not match the built-in DataTables filtering which does it
      * word by word on any field. It's possible to do here, but concerned about efficiency
      * on very large tables, and MySQL's regex functionality is very limited
      */
     if ($search = $request->getParam('sSearch')) {
         for ($i = 0; $i < $colCount; $i++) {
             $select->orHaving("{$columns[$i]} LIKE ?", '%' . $search . '%');
         }
     }
     /* Individual column filtering */
     for ($i = 0; $i < $colCount; $i++) {
         if ($request->getParam('bSearchable_' . $i) == "true") {
             if ($search = $request->getParam('sSearch_' . $i)) {
                 $select->having("{$columns[$i]} LIKE ?", '%' . $search . '%');
             }
         }
     }
     //save current query for fetching data
     $query = clone $select;
     $tableName = $table->info(Zend_Db_Table::NAME);
     $expr = new Zend_Db_Expr('COUNT(*) as total');
     /* Data set length after filtering */
     if ($select->getPart(Zend_Db_Select::FROM)) {
         $select->columns($expr);
     } else {
         $select->from($tableName, $expr);
     }
     /* Data no result */
     if ($iFilteredTotal = $table->fetchRow($select)) {
         $iFilteredTotal = $iFilteredTotal->total;
     } else {
         $iFilteredTotal = 0;
     }
     $query->limit($request->getParam('iDisplayLength'), $request->getParam('iDisplayStart'));
     /*
      * SQL queries
      * Get data to display
      */
     if ($query->getPart(Zend_Db_Select::FROM)) {
         $query->columns($columns);
     } else {
         $query->from($tableName, $columns);
     }
     // Get total rows count
     $select = $table->select()->from($tableName, $expr);
     $iTotalRecords = $table->fetchRow($select)->total;
     return array("sEcho" => (int) $request->getParam('sEcho'), "aaData" => $table->fetchAll($query)->toArray(), "iTotalRecords" => $iTotalRecords, "iTotalDisplayRecords" => $iFilteredTotal);
 }

 /**
  * Display a list of all users in the system.
  *
  */
 public function allAction()
 {
     $this->view->acl = array('add' => $this->_helper->hasAccess('add'), 'edit' => $this->_helper->hasAccess('edit'), 'delete' => $this->_helper->hasAccess('delete'));
     $filterUsername = $this->_getParam('username');
     $filterFirstName = $this->_getParam('firstName');
     $filterLastName = $this->_getParam('lastName');
     $filterRole = $this->_getParam('role', 'any');
     $filterSort = $this->_getParam('sort', 'username');
     $filterDirection = $this->_getParam('direction', 'asc');
     $form = new Ot_Form_UserSearch();
     $form->populate($this->getAllParams());
     $account = new Ot_Model_DbTable_Account();
     $accountTbl = $account->info('name');
     $select = new Zend_Db_Table_Select($account);
     $select->from($accountTbl);
     if ($filterUsername != '') {
         $select->where($accountTbl . '.username LIKE ?', '%' . $filterUsername . '%');
     }
     if ($filterFirstName != '') {
         $select->where($accountTbl . '.firstName LIKE ?', '%' . $filterFirstName . '%');
     }
     if ($filterLastName != '') {
         $select->where($accountTbl . '.lastName LIKE ?', '%' . $filterLastName . '%');
     }
     if ($filterRole != '' && $filterRole != 'any') {
         $otRole = new Ot_Model_DbTable_AccountRoles();
         $roleTbl = $otRole->info('name');
         $select->join($roleTbl, $accountTbl . '.accountId = ' . $roleTbl . '.accountId', array());
         $select->where($roleTbl . '.roleId = ?', $filterRole);
         $select->distinct();
     }
     if ($filterSort == 'name') {
         $select->order('firstName ' . $filterDirection);
         $select->order('lastName ' . $filterDirection);
     } else {
         $select->order($filterSort . ' ' . $filterDirection);
     }
     $filterOptions = array('username' => $filterUsername, 'lastname' => $filterLastName, 'firstname' => $filterFirstName, 'direction' => $filterDirection, 'role' => $filterRole, 'sort' => $filterSort);
     foreach ($filterOptions as $key => $value) {
         if (!$value) {
             unset($filterOptions[$key]);
         }
     }
     $adapter = new Zend_Paginator_Adapter_DbSelect($select);
     $paginator = new Zend_Paginator($adapter);
     $paginator->setCurrentPageNumber($this->_getParam('page', 1));
     $aa = new Ot_Model_DbTable_AuthAdapter();
     $adapters = $aa->fetchAll();
     $adapterMap = array();
     foreach ($adapters as $a) {
         $adapterMap[$a->adapterKey] = $a;
     }
     $this->_helper->pageTitle('ot-account-all:title');
     $this->view->assign(array('paginator' => $paginator, 'form' => $form, 'interface' => true, 'sort' => $filterSort, 'direction' => $filterDirection, 'adapters' => $adapterMap, 'filterOptions' => array('urlParams' => $filterOptions)));
 }