The table name can be expressed
public from ( array | string | Zend_Db_Expr | Zend_Db_Table_Abstract $name, array | string | Zend_Db_Expr $cols = self::SQL_WILDCARD, string $schema = null ) : Zend_Db_Table_Select | ||
$name | array | string | Zend_Db_Expr | Zend_Db_Table_Abstract | The table name or an associative array relating table name to correlation name. |
$cols | array | string | Zend_Db_Expr | The columns to select from this table. |
$schema | string | The schema name to specify, if any. |
리턴 | Zend_Db_Table_Select | This Zend_Db_Table_Select object. |
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))); }