PHP Select::order方法代码示例

 public function fetchAll($paginate = true, $filter = array(), $orderBy = array())
 {
     if ($paginate) {
         $select = new Select('service_language');
         $select->join('lookup_status', 'lookup_status.status_id = service_language.status_id', array('status'), 'left');
         /* Data filter code start here*/
         if (count($filter) > 0) {
             $filter['language_name'] != "" ? $select->where("service_language.language_name LIKE '%" . $filter['language_name'] . "%'") : "";
             $filter['status_id'] != "" ? $select->where("service_language.status_id = " . $filter['status_id']) : "";
         }
         /* Data filter code end here*/
         /* Data sorting code starts here */
         if (count($orderBy) > 0 && $orderBy['sort_field'] != '' && $orderBy['sort_order'] != '') {
             switch ($orderBy['sort_field']) {
                 case 'language':
                     $select->order('service_language.language_name ' . $orderBy['sort_order']);
                     break;
                 case 'status':
                     $select->order('lookup_status.status ' . $orderBy['sort_order']);
                     break;
             }
         }
         /* Data sorting code ends here */
         $resultSetPrototype = new ResultSet();
         $resultSetPrototype->setArrayObjectPrototype(new ServiceLanguages());
         $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
         $paginator = new Paginator($paginatorAdapter);
         return $paginator;
     }
     return $this->tableGateway->select();
 }

 public function fetchAll($method = 'asc')
 {
     $select = new Select();
     $select->from('items');
     if (is_numeric($method) and $method != 0) {
         $select->where->equalTo('items.item_category', $method);
     }
     //$select->columns(array('item_name','item_brand','item_price'));
     $select->join('brands', "brands.b_id = items.item_brand", array('b_name'), 'left');
     $select->join('categories', "categories.cat_id = items.item_category", array('cat_name'), 'left');
     $select->join('sub_categories', "sub_categories.subcat_id = items.item_sub_category", array('subcat_name'), 'left');
     $select->join('images', "images.img_item_id = items.item_id", array('img_link'), 'left');
     $select->group('items.item_id');
     switch ($method) {
         case 'asc':
             $select->order(array('item_price ACS'));
             break;
         case 'desc':
             $select->order(array('item_price DESC'));
             break;
     }
     $select->order(array('item_price ' . $method));
     //echo $select->getSqlString();
     $resultSet = $this->tableGateway->selectWith($select);
     //$resultSet = $this->tableGateway->select();
     return $resultSet;
 }

 public function fetchAll($id, $paginate = true, $filter = array(), $orderBy = array())
 {
     if ($paginate) {
         $select = new Select('media');
         //$select->columns(array('*', new Expression("user_feature_setting.email as email_status")));
         $select->join('users', 'users.id = media.user_id', array('first_name', 'last_name'), 'inner');
         $select->join('lookup_status', 'lookup_status.status_id = media.status_id', array('status'), 'left');
         $select->where(array('user_id' => $id, 'media_type' => 1));
         /* Data filter code start here*/
         if (count($filter) > 0) {
             isset($filter['name']) && $filter['name'] != "" ? $select->where("CONCAT(users.first_name,' ',users.last_name) LIKE '%" . $filter['name'] . "%'") : "";
             isset($filter['title']) && $filter['title'] != "" ? $select->where("media.media_title LIKE '%" . $filter['title'] . "%'") : "";
             isset($filter['media_type']) && $filter['media_type'] != "" ? $select->where("media.media_type = " . $filter['media_type']) : "";
             if (isset($filter['from_date']) && $filter['from_date'] != "" && isset($filter['to_date']) && $filter['to_date'] != "") {
                 $select->where("DATE_FORMAT(media.created_date , '%Y-%m-%d') BETWEEN '" . $filter['from_date'] . "' AND '" . $filter['to_date'] . "'");
             } else {
                 if (isset($filter['from_date']) && !isset($filter['to_date']) && $filter['from_date'] != "") {
                     $select->where("DATE_FORMAT(media.created_date , '%Y-%m-%d') = '" . $filter['from_date'] . "'");
                 } else {
                     if (!isset($filter['from_date']) && isset($filter['to_date']) && $filter['to_date'] != "") {
                         $select->where("DATE_FORMAT(media.created_date , '%Y-%m-%d') = '" . $filter['to_date'] . "'");
                     }
                 }
             }
             isset($filter['status_id']) && $filter['status_id'] != "" ? $select->where("media.status_id = " . $filter['status_id']) : "";
         }
         /* Data filter code end here*/
         /* Data sorting code starts here */
         if (count($orderBy) > 0 && $orderBy['sort_field'] != '' && $orderBy['sort_order'] != '') {
             switch ($orderBy['sort_field']) {
                 case 'name':
                     $select->order('users.first_name ' . $orderBy['sort_order']);
                     break;
                 case 'title':
                     $select->order('media.media_title ' . $orderBy['sort_order']);
                     break;
                 case 'media_type':
                     $select->order('media.media_type ' . $orderBy['sort_order']);
                     break;
                 case 'date':
                     $select->order('media.created_date ' . $orderBy['sort_order']);
                     break;
                 case 'status':
                     $select->order('lookup_status.status ' . $orderBy['sort_order']);
                     break;
             }
         }
         /* Data sorting code ends here */
         //echo str_replace('"', '', $select->getSqlString()); exit;
         $resultSetPrototype = new ResultSet();
         $resultSetPrototype->setArrayObjectPrototype(new UsersMedia());
         $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
         $paginator = new Paginator($paginatorAdapter);
         return $paginator;
     } else {
         $select = $this->tableGateway->getSql()->select();
         return $this->tableGateway->selectwith($select);
     }
 }

 public function fetchAll($paginate = true, $filter = array(), $orderBy = array())
 {
     if ($paginate) {
         $select = new Select('booking');
         $select->columns(array('*', new Expression("service_provider.first_name as sp_first_name, service_provider.last_name as sp_last_name,\n\t\t\tinvoice.status_id AS invoice_status,payment_history.currency as currency,CASE invoice.status_id WHEN 0 THEN 'Unpaid' WHEN 1 THEN 'Paid' WHEN 2 THEN 'Partially Paid' END AS PaymentStatus")));
         $select->join('booking_suggestion_history', 'booking_suggestion_history.booking_id = booking.id', array('booking_time', 'booking_status'), 'inner');
         $select->join('users', 'users.id = booking.user_id', array('first_name', 'last_name'), 'left');
         $select->join(array('service_provider' => 'users'), 'service_provider.id = booking.service_provider_id', array(), 'left');
         $select->join('service_provider_service', 'service_provider_service.id = booking.service_provider_service_id', array('duration', 'price'), 'left');
         $select->join('service_category', 'service_category.id = service_provider_service.service_id', array('category_name'), 'left');
         $select->join('invoice', 'invoice.id = booking.invoice_id', array('invoice_total', 'site_commision'), 'inner');
         $select->join('invoice_details', 'invoice_details.invoice_id = invoice.id', array('sale_item_details'), 'inner');
         $select->join('payment_history', 'payment_history.invoice_id = invoice.id', array(), 'inner');
         $select->join('lookup_status', 'lookup_status.status_id = booking_suggestion_history.booking_status', array('status'), 'left');
         $select->where('booking_suggestion_history.id = (SELECT id FROM booking_suggestion_history WHERE booking_id = booking.id ORDER BY id DESC LIMIT 1)');
         count($filter) > 0 ? $select->where($filter) : "";
         /* Data sorting code starts here */
         if (count($orderBy) > 0 && $orderBy['sort_field'] != '' && $orderBy['sort_order'] != '') {
             switch ($orderBy['sort_field']) {
                 case 'user':
                     $select->order('users.first_name ' . $orderBy['sort_order']);
                     break;
                 case 'service_provider':
                     $select->order('service_provider.first_name ' . $orderBy['sort_order']);
                     break;
                 case 'service':
                     $select->order('service_category.category_name ' . $orderBy['sort_order']);
                     break;
                 case 'booked_date':
                     $select->order('booking.booked_date ' . $orderBy['sort_order']);
                     break;
             }
         } else {
             $select->order('booking.id desc');
         }
         /* Data sorting code ends here */
         //echo str_replace('"', '', $select->getSqlString()); exit;
         $resultSetPrototype = new ResultSet();
         $resultSetPrototype->setArrayObjectPrototype(new Bookings());
         $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
         $paginator = new Paginator($paginatorAdapter);
         return $paginator;
     } else {
         $select = $this->tableGateway->getSql()->select();
         $select->columns(array('*', new Expression("service_provider.first_name as sp_first_name, service_provider.last_name as sp_last_name,\n\t\t\tinvoice.status_id AS invoice_status,CASE invoice.status_id WHEN 0 THEN 'Unpaid' WHEN 1 THEN 'Paid' WHEN 2 THEN 'Partially Paid' END AS PaymentStatus")));
         $select->join('booking_suggestion_history', 'booking_suggestion_history.booking_id = booking.id', array('booking_time', 'booking_status'), 'inner');
         $select->join('users', 'users.id = booking.user_id', array('first_name', 'last_name'), 'left');
         $select->join(array('service_provider' => 'users'), 'service_provider.id = booking.service_provider_id', array(), 'left');
         $select->join('service_provider_service', 'service_provider_service.id = booking.service_provider_service_id', array('duration', 'price'), 'left');
         $select->join('service_category', 'service_category.id = service_provider_service.service_id', array('category_name'), 'left');
         $select->join('invoice', 'invoice.id = booking.invoice_id', array('invoice_total', 'site_commision', 'created_date'), 'inner');
         $select->join('invoice_details', 'invoice_details.invoice_id = invoice.id', array('sale_item_details'), 'inner');
         $select->join('payment_history', 'payment_history.invoice_id = invoice.id', array(), 'inner');
         $select->join('lookup_status', 'lookup_status.status_id = booking_suggestion_history.booking_status', array('status'), 'left');
         $select->where('booking_suggestion_history.id = (SELECT id FROM booking_suggestion_history WHERE booking_id = booking.id ORDER BY id DESC LIMIT 1)');
         count($filter) > 0 ? $select->where($filter) : "";
         return $this->tableGateway->selectwith($select);
     }
 }

 public function fetchAll($paginate = true, $filter = array(), $orderBy = array())
 {
     if ($paginate) {
         $select = new Select('feedback');
         $select->columns(array('*', new Expression("service_provider_contact.first_name AS sp_first_name, service_provider_contact.last_name AS sp_last_name")));
         $select->join(array('service_provider_contact' => 'users'), 'service_provider_contact.id = feedback.users_id', array(), 'inner');
         $select->join('users', 'users.id = feedback.created_by', array('first_name', 'last_name'), 'inner');
         $select->join('service_provider_service', 'service_provider_service.id = feedback.service_id', array('duration'), 'inner');
         $select->join('service_category', 'service_category.id = service_provider_service.service_id', array('category_name'), 'inner');
         $select->join('lookup_status', 'lookup_status.status_id = feedback.status_id', array('status'), 'left');
         /* Data filter code start here*/
         if (count($filter) > 0) {
             isset($filter['name']) && $filter['name'] != "" ? $select->where("CONCAT(service_provider_contact.first_name,' ',service_provider_contact.last_name) LIKE '%" . $filter['name'] . "%'") : "";
             if (isset($filter['from_date']) && $filter['from_date'] != "" && isset($filter['to_date']) && $filter['to_date'] != "") {
                 $select->where("DATE_FORMAT(feedback.created_date , '%Y-%m-%d') BETWEEN '" . $filter['from_date'] . "' AND '" . $filter['to_date'] . "'");
             } else {
                 if (isset($filter['from_date']) && !isset($filter['to_date']) && $filter['from_date'] != "") {
                     $select->where("DATE_FORMAT(feedback.created_date , '%Y-%m-%d') = '" . $filter['from_date'] . "'");
                 } else {
                     if (!isset($filter['from_date']) && isset($filter['to_date']) && $filter['to_date'] != "") {
                         $select->where("DATE_FORMAT(feedback.created_date , '%Y-%m-%d') = '" . $filter['to_date'] . "'");
                     }
                 }
             }
             if (isset($filter['service_id']) && $filter['service_id'] != "") {
                 $select->where("feedback.service_id = " . $filter['service_id']);
             }
             isset($filter['status_id']) && $filter['status_id'] != "" ? $select->where("users.status_id = " . $filter['status_id']) : "";
         }
         /* Data filter code end here*/
         /* Data sorting code starts here */
         if (count($orderBy) > 0 && $orderBy['sort_field'] != '' && $orderBy['sort_order'] != '') {
             switch ($orderBy['sort_field']) {
                 case 'name':
                     $select->order('service_provider_contact.first_name ' . $orderBy['sort_order']);
                     break;
                 case 'service':
                     $select->order('service_category.category_name ' . $orderBy['sort_order']);
                     break;
                 case 'date':
                     $select->order('feedback.created_date ' . $orderBy['sort_order']);
                     break;
             }
         }
         /* Data sorting code ends here */
         //echo str_replace('"','',$select->getSqlString()); exit;
         $resultSetPrototype = new ResultSet();
         $resultSetPrototype->setArrayObjectPrototype(new Feedbacks());
         $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
         $paginator = new Paginator($paginatorAdapter);
         return $paginator;
     }
     return $this->tableGateway->select();
 }

 /**
  * Sets the filter
  * @param \Zend\Db\Sql\Select $select
  * @param array $criteria
  * @return \Zend\Db\Sql\Select
  */
 private function setFilter($select, $criteria)
 {
     if (array_key_exists('filter', $criteria) && is_array($criteria['filter']) && array_key_exists('capitaleSociale', $criteria['filter']) && is_array($criteria['filter']['capitaleSociale']) && sizeof($criteria['filter']['capitaleSociale']) > 1) {
         if ($criteria['filter']['capitaleSociale'][0] == 0) {
             $select->where('capitaleSociale BETWEEN 0 AND ' . pow(10, $criteria['filter']['capitaleSociale'][1]));
         } else {
             $select->where('capitaleSociale BETWEEN ' . pow(10, $criteria['filter']['capitaleSociale'][0]) . ' AND ' . pow(10, $criteria['filter']['capitaleSociale'][1]));
         }
     }
     if (array_key_exists('filter', $criteria) && is_array($criteria['filter']) && array_key_exists('fEventi', $criteria['filter']) && $criteria['filter']['fEventi'] != 'false') {
         $select->where('eventi > 0');
         $select->order('eventi DESC');
     }
     if (array_key_exists('filter', $criteria) && is_array($criteria['filter']) && array_key_exists('fProtesti', $criteria['filter']) && $criteria['filter']['fProtesti'] != 'false') {
         $select->where('protesti > 0');
         $select->order('protesti DESC');
     }
     if (array_key_exists('filter', $criteria) && is_array($criteria['filter']) && array_key_exists('fFallimenti', $criteria['filter']) && $criteria['filter']['fFallimenti'] != 'false') {
         $select->where('fallimenti > 0');
         $select->order('fallimenti DESC');
     }
     if (array_key_exists('filter', $criteria) && is_array($criteria['filter']) && array_key_exists('fPregiudizievoli', $criteria['filter']) && $criteria['filter']['fPregiudizievoli'] != 'false') {
         $select->where('pregiudizievoli > 0');
         $select->order('pregiudizievoli DESC');
     }
     if (array_key_exists('filter', $criteria) && is_array($criteria['filter']) && array_key_exists('fCassaIntegrazione', $criteria['filter']) && $criteria['filter']['fCassaIntegrazione'] != 'false') {
         $select->where('cassaIntegrazione > 0');
         $select->order('cassaIntegrazione DESC');
     }
     if (array_key_exists('filter', $criteria) && is_array($criteria['filter']) && array_key_exists('textSearch', $criteria['filter']) && $criteria['filter']['textSearch'] != '') {
         $select->where('
                 (
                 ragSoc LIKE "%' . addslashes($criteria['filter']['textSearch']) . '%" OR
                 indirizzo LIKE "%' . addslashes($criteria['filter']['textSearch']) . '%" OR    
                 email LIKE "%' . addslashes($criteria['filter']['textSearch']) . '%" OR
                 telefono LIKE "%' . addslashes($criteria['filter']['textSearch']) . '%" OR
                 desISTAT LIKE "%' . addslashes($criteria['filter']['textSearch']) . '%" OR
                 desAttivita LIKE "%' . addslashes($criteria['filter']['textSearch']) . '%"
                 )
                 ');
     }
     if ($this->codFisc != '') {
         $select->where('`partIVA` IN (SELECT `or_partIVA` FROM `person_to_company` WHERE `codFisc` = "' . addslashes($this->codFisc) . '")');
     }
     if (is_numeric($this->partIVA)) {
         $select->where('
         (
         `partIVA` IN (SELECT `dest_partIVA` FROM `company_to_company` WHERE `or_partIVA` = "' . addslashes($this->partIVA) . '") OR
         `partIVA` IN (SELECT `or_partIVA` FROM `company_to_company` WHERE `dest_partIVA` = "' . addslashes($this->partIVA) . '")
         )
         ');
     }
     return $select;
 }

 /**
  * Method to fetch all data in pagination object
  **/
 public function fetchAll($optionArray = array(), $paginated = false)
 {
     if ($paginated) {
         // create a new Select object for the table cmspage
         $select = new Select('cms_pages');
         if (!empty($optionArray['fieldArray'])) {
             $select->columns($optionArray['fieldArray']);
         }
         if (!empty($optionArray['sortByColumn']['sort_column']) && !empty($optionArray['sortByColumn']['sort_order'])) {
             $orderBy = $optionArray['sortByColumn']['sort_column'] . ' ' . $optionArray['sortByColumn']['sort_order'];
             $select->order($orderBy);
         } else {
             if (!empty($optionArray['default_sort_column']) && !empty($optionArray['default_sort_order'])) {
                 $orderBy = $optionArray['default_sort_column'] . ' ' . $optionArray['default_sort_order'];
                 $select->order($orderBy);
             }
         }
         if (!empty($optionArray['searchColumns']['searchKey']) && !empty($optionArray['searchColumns']['searchCol'])) {
             $searchKey = "%" . $optionArray['searchColumns']['searchKey'] . "%";
             $searchCol = $optionArray['searchColumns']['searchCol'] ? $optionArray['searchColumns']['searchCol'] : $optionArray['fieldArray'][1];
             $select->where->like($searchCol, $searchKey);
         }
         // create a new result set based on the cmspage entity
         $resultSetPrototype = new ResultSet();
         $resultSetPrototype->setArrayObjectPrototype(new Cmspage());
         // create a new pagination adapter object
         $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
         $paginator = new Paginator($paginatorAdapter);
         return $paginator;
     }
     $resultSet = $this->tableGateway->select(function (Select $select) use($optionArray) {
         if (!empty($optionArray['fieldArray'])) {
             $select->columns($optionArray['fieldArray']);
         }
         if (!empty($optionArray['sortByColumn']['sort_column']) && !empty($optionArray['sortByColumn']['sort_order'])) {
             $orderBy = $optionArray['sortByColumn']['sort_column'] . ' ' . $optionArray['sortByColumn']['sort_order'];
             $select->order($orderBy);
         } else {
             if (!empty($optionArray['default_sort_column']) && !empty($optionArray['default_sort_order'])) {
                 $orderBy = $optionArray['default_sort_column'] . ' ' . $optionArray['default_sort_order'];
                 $select->order($orderBy);
             }
         }
         if (!empty($optionArray['searchColumns']['searchKey']) && !empty($optionArray['searchColumns']['searchCol'])) {
             $searchKey = "%" . $optionArray['searchColumns']['searchKey'] . "%";
             $searchCol = $optionArray['searchColumns']['searchCol'] ? $optionArray['searchColumns']['searchCol'] : $optionArray['fieldArray'][1];
             $select->where->like($searchCol, $searchKey);
         }
     });
     return $resultSet;
 }

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

 public function indexAction()
 {
     /* 	$temp = $this->forward()->dispatch('Application/Controller/Album', array('action' => 'index'));
     		
     		echo '<pre>'; print_r($temp); echo '<pre>';die; */
     $auth = new AuthenticationService();
     if (!$auth->hasIdentity()) {
         return $this->redirect()->toRoute('home');
     }
     $select = new Select();
     $search = @$_REQUEST['search'];
     if (!empty($search)) {
         $select->where->like('name', '%' . $search . '%');
     }
     $order_by = $this->params()->fromRoute('order_by') ? $this->params()->fromRoute('order_by') : 'id';
     $order = $this->params()->fromRoute('order') ? $this->params()->fromRoute('order') : Select::ORDER_ASCENDING;
     $page = $this->params()->fromRoute('page') ? (int) $this->params()->fromRoute('page') : 1;
     $category = $this->getCategoryTable()->fetchAllCategory($select->order($order_by . ' ' . $order), $search);
     $itemPerPage = 2;
     $category->current();
     $paginator = new Paginator(new PaginatorIterator($category));
     $paginator->setCurrentPageNumber($page);
     $paginator->setItemCountPerPage($itemPerPage);
     $paginator->setPageRange(10);
     return new ViewModel(array('order_by' => $order_by, 'order' => $order, 'page' => $page, 'paginator' => $paginator));
 }

 public function fetchActive()
 {
     $select = new Select($this->getTable());
     $select->where->equalTo('active', 1);
     $select->order('id desc');
     return $this->selectWith($select)->toArray();
 }

 public function indexAction()
 {
     /* $service1=new \Lib\Service1\Custom();
     		echo $service1->demo(); die; */
     /* $plugin=$this->CustomPlugin();
     		print_r($plugin->doSomthing()); die; */
     /* $facebook = new \Facebook(array(
     				'appId'  => 'xxx',
     				'secret' => 'xxx',
     			));
     			print_r($facebook); die; */
     $auth = new AuthenticationService();
     if (!$auth->hasIdentity()) {
         return $this->redirect()->toRoute('home');
     }
     $select = new Select();
     $search = @$_REQUEST['search'];
     if (!empty($search)) {
         $select->where->like('name', '%' . $search . '%')->or->like('email', '%' . $search . '%')->or->like('mob', '%' . $search . '%')->or->like('title', '%' . $search . '%');
     }
     $order_by = $this->params()->fromRoute('order_by') ? $this->params()->fromRoute('order_by') : 'id';
     $order = $this->params()->fromRoute('order') ? $this->params()->fromRoute('order') : Select::ORDER_ASCENDING;
     $page = $this->params()->fromRoute('page') ? (int) $this->params()->fromRoute('page') : 1;
     $album = $this->getAdminTable()->fetchAll($select->order($order_by . ' ' . $order), $search);
     $itemPerPage = 2;
     $album->current();
     $paginator = new Paginator(new PaginatorIterator($album));
     $paginator->setCurrentPageNumber($page);
     $paginator->setItemCountPerPage($itemPerPage);
     $paginator->setPageRange(10);
     //print_r($paginator); die;
     return new ViewModel(array('order_by' => $order_by, 'order' => $order, 'page' => $page, 'paginator' => $paginator));
 }

 public function indexAction()
 {
     $searchform = new AlbumSearchForm();
     $searchform->get('submit')->setValue('Search');
     $select = new Select();
     $order_by = $this->params()->fromRoute('order_by') ? $this->params()->fromRoute('order_by') : 'id';
     $order = $this->params()->fromRoute('order') ? $this->params()->fromRoute('order') : Select::ORDER_ASCENDING;
     $page = $this->params()->fromRoute('page') ? (int) $this->params()->fromRoute('page') : 1;
     $select->order($order_by . ' ' . $order);
     $search_by = $this->params()->fromRoute('search_by') ? $this->params()->fromRoute('search_by') : '';
     $where = new \Zend\Db\Sql\Where();
     $formdata = array();
     if (!empty($search_by)) {
         $formdata = (array) json_decode($search_by);
         if (!empty($formdata['artist'])) {
             $where->addPredicate(new \Zend\Db\Sql\Predicate\Like('artist', '%' . $formdata['artist'] . '%'));
         }
         if (!empty($formdata['title'])) {
             $where->addPredicate(new \Zend\Db\Sql\Predicate\Like('title', '%' . $formdata['title'] . '%'));
         }
     }
     if (!empty($where)) {
         $select->where($where);
     }
     $album = $this->getAlbumTable()->fetchAll($select);
     $totalRecord = $album->count();
     $itemsPerPage = 2;
     $album->current();
     $paginator = new Paginator(new paginatorIterator($album));
     $paginator->setCurrentPageNumber($page)->setItemCountPerPage($itemsPerPage)->setPageRange(7);
     $searchform->setData($formdata);
     return new ViewModel(array('search_by' => $search_by, 'order_by' => $order_by, 'order' => $order, 'page' => $page, 'paginator' => $paginator, 'pageAction' => 'album', 'form' => $searchform, 'totalRecord' => $totalRecord));
 }

 public function search($str)
 {
     $select = new Select('video');
     $spec = new Where();
     $spec->like('title', '% ' . $str . ' %');
     $select->where($spec);
     $select->where->OR->like('title', $str . ' %');
     $select->where->OR->like('title', '% ' . $str);
     //$select->where(array('title'=>$str));
     //$select->where(array('catelog'=>$title,'description'=>$description));
     $select->order('id ASC');
     /*
                  // create a new result set based on the Album entity
                  $resultSetPrototype = new ResultSet();
                  $resultSetPrototype->setArrayObjectPrototype(new Search());
                  // create a new pagination adapter object
                  $paginatorAdapter = new DbSelect(
                      // our configured select object
                      $select,
                      // the adapter to run it against
                      $this->tableGateway->getAdapter(),
                      // the result set to hydrate
                      $resultSetPrototype
                  );
                  $paginator = new Paginator($paginatorAdapter);
                 
                  return $paginator;
     */
     return $this->tableGateway->selectWith($select);
 }

 public function fetchAll($request, $paginated = false)
 {
     if ($paginated) {
         // create a new Select object for the table album
         $select = new Select('album');
         $select->order($request['sort'] . " " . $request['order']);
         // Search
         if ($request['search']) {
             $x = $request['search'] . '%';
             $where = new \Zend\Db\Sql\Where();
             $where->like('title', $x);
             $where->or->like('artist', $x);
             $select->where($where);
         }
         // New result set based on the Album entity
         $resultSetPrototype = new ResultSet();
         $resultSetPrototype->setArrayObjectPrototype(new Album());
         // New pagination adapter object
         $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
         $paginator = new Paginator($paginatorAdapter);
         return $paginator;
     }
     $resultSet = $this->tableGateway->select();
     return $resultSet;
 }

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