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. |
| return | 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)));
}
