public function indexAction() { if (!Engine_Api::_()->core()->hasSubject()) { return $this->setNoRender(); } $subject = Engine_Api::_()->core()->getSubject(); if ($subject->getType() == 'user' && $subject->level_id == Book_Plugin_Constants::AUTHOR_LEVEL) { $bookTable = new Book_Model_DbTable_Books(); $bookTableName = $bookTable->info('name'); $bookSelect = $bookTable->getSelect(); $bookAuthorTable = new Book_Model_DbTable_BookAuthor(); $bookAuthorTableName = $bookAuthorTable->info('name'); $bookSelect->join($bookAuthorTableName, "{$bookTableName}.book_id = {$bookAuthorTableName}.book_id"); $bookSelect->where("{$bookAuthorTableName}.author_id = ?", $subject->getIdentity()); $bookSelect->order('RAND()'); $this->view->paginator = $paginator = Zend_Paginator::factory($bookSelect); $paginator->setItemCountPerPage($this->_getParam('itemCountPerPage', 5)); $paginator->setCurrentPageNumber($this->_getParam('page', 1)); $itemCount = $paginator->getTotalItemCount(); if ($itemCount == 0) { return $this->setNoRender(); } else { $this->_childCount = $paginator->getTotalItemCount(); $paginator->setItemCountPerPage($this->_getParam('itemCountPerPage', 10)); $paginator->setCurrentPageNumber($this->_getParam('page', 1)); } } else { return $this->setNoRender(); } }
public function getSelect($selectedColumns = null) { $select = parent::getSelect($selectedColumns); $tableName = $this->info(Zend_Db_Table_Abstract::NAME); $bookAuthorTable = new Book_Model_DbTable_BookAuthor(); $bookAuthorTableName = $bookAuthorTable->info(Zend_Db_Table_Abstract::NAME); // $select->joinLeft( // $bookAuthorTableName, // "$tableName.book_id = $bookAuthorTableName.book_id", // array('GROUP_CONCAT(author_id) As author_ids', 'GROUP_CONCAT(author_name) As author_names') // ); // $select->where("$bookAuthorTableName.type = ?", Book_Plugin_Constants::AUTHOR); $bookAuthorSelect = $bookAuthorTable->select()->from($bookAuthorTableName, array(new Zend_Db_Expr('GROUP_CONCAT(author_id) As author_ids', 'GROUP_CONCAT(author_name) As author_names')))->where("{$bookAuthorTableName}.type = ?", Book_Plugin_Constants::AUTHOR); $select->columns(new Zend_Db_Expr('(' . $bookAuthorSelect->assemble() . ')')); return $select; }
public function indexAction() { $userTable = Engine_Api::_()->getItemTable('user'); $userTableName = $userTable->info('name'); $bookAuthorTable = new Book_Model_DbTable_BookAuthor(); $bookAuthorTableName = $bookAuthorTable->info('name'); $userSelect = $userTable->select()->from($userTableName); $userSelect->setIntegrityCheck(false); $userSelect->join($bookAuthorTableName, "{$bookAuthorTableName}.author_id = {$userTableName}.user_id", array("COUNT(*) AS num_books")); $userSelect->group(array("{$userTableName}.user_id")); $userSelect->where("{$userTableName}.level_id = ?", Book_Plugin_Constants::AUTHOR_LEVEL); $userSelect->where("{$userTableName}.enabled = ?", 1); $userSelect->where("{$userTableName}.verified = ?", 1); $userSelect->where("{$userTableName}.approved = ?", 1); $numberOfAuthors = $this->_getParam('itemCountPerPage', 5); $userSelect->order('RAND()'); $userSelect->limit($numberOfAuthors); $this->view->authors = $users = $userTable->fetchAll($userSelect); }