PHP Select::columns方法代码示例

 /**
  * 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 setParameters(Parameters $params)
 {
     $groupsPostsTable = Api::_()->getDbTable('Group\\DbTable\\GroupsPosts');
     $groupsPostsTableName = $groupsPostsTable->initTableName()->getTable();
     $groupsCategoriesTable = Api::_()->getDbTable('Group\\DbTable\\CategoriesGroups');
     $groupsCategoriesTableName = $groupsCategoriesTable->initTableName()->getTable();
     if ($params->group_id || $params->groupCategory) {
         $params->inGroup = true;
     }
     if ($params->inGroup) {
         $groupId = $params->group_id;
         $categoryId = $params->groupCategory;
         $this->where(function ($where) use($groupsPostsTableName, $groupsCategoriesTableName, $groupId, $categoryId) {
             $select = new Select($groupsPostsTableName);
             $select->columns(array('post_id'));
             if ($groupId) {
                 $select->where(array('group_id' => $groupId));
             }
             if ($categoryId) {
                 $cateSelect = new Select($groupsCategoriesTableName);
                 $cateSelect->columns(array('group_id'));
                 $cateSelect->where(array('category_id' => $categoryId));
                 $select->where(function ($where) use($cateSelect) {
                     $where->in('group_id', $cateSelect);
                     return $where;
                 });
             }
             $where->in('id', $select);
             return $where;
         });
     }
     return parent::setParameters($params);
 }

 public function getBuilder()
 {
     if (empty($this->parsed['SELECT'])) {
         throw new \InvalidArgumentException('Queries other than SELECT are not supported yet');
     }
     $sql = new Select();
     $sql->columns($this->getBaseExprs($this->parsed['SELECT']));
     foreach ($this->parsed['FROM'] as $table) {
         if (!$table['ref_type']) {
             $sql->from(array($table['alias']['name'] => $table['table']));
             continue;
         }
         if ('JOIN' == $table['join_type']) {
             $table['join_type'] = 'INNER';
         }
         $sql->join(array($table['alias']['name'] => $table['table']), join(' ', $this->getBaseExprs($table['ref_clause'])), array(), $table['join_type']);
     }
     if (isset($this->parsed['GROUP'])) {
         $sql->group($this->getBaseExprs($this->parsed['GROUP']));
     }
     if (isset($this->parsed['WHERE'])) {
         $sql->where(join(' ', $this->getBaseExprs($this->parsed['WHERE'])));
     }
     unset($this->parsed['WHERE'], $this->parsed['GROUP'], $this->parsed['FROM'], $this->parsed['WHERE']);
     var_dump($this->parsed);
     #die('Stopped at ' . __FILE__ . ' on line ' . __LINE__);
     return $sql;
 }

 /**
  * Returns all the comments pending approval for a thread.
  *
  * @param string $thread
  * @return ResultSet
  */
 public function fetchAllPendingForThread($thread)
 {
     $select = new Select($this->tableGateway->getTable());
     $select->columns(array('id', 'author', 'content', 'contact', 'published_on_raw' => 'published_on', 'published_on' => new Expression("DATE_FORMAT(published_on, '%M %d, %Y %H:%i')")))->where(array('thread' => $thread, 'visible' => 0))->order('published_on_raw ASC');
     $resultSet = $this->tableGateway->selectWith($select);
     return $resultSet;
 }

 public function setUp()
 {
     $this->column = $this->getMockBuilder('ZfcDatagrid\\Column\\Select')->disableOriginalConstructor()->getMock();
     $this->column->method('getSelectPart1')->willReturn('myCol');
     $this->column->method('getType')->willReturn(new \ZfcDatagrid\Column\Type\PhpString());
     $this->column->setUniqueId('myCol');
     $this->column->setSelect('myCol');
     $this->column2 = $this->getMockBuilder('ZfcDatagrid\\Column\\Select')->disableOriginalConstructor()->getMock();
     $this->column2->method('getSelectPart1')->willReturn('myCol2');
     $this->column2->method('getType')->willReturn(new \ZfcDatagrid\Column\Type\PhpString());
     $this->column2->setUniqueId('myCol2');
     $this->column2->setSelect('myCol2');
     $this->mockDriver = $this->getMock('Zend\\Db\\Adapter\\Driver\\DriverInterface');
     $this->mockConnection = $this->getMock('Zend\\Db\\Adapter\\Driver\\ConnectionInterface');
     $this->mockDriver->expects($this->any())->method('checkEnvironment')->will($this->returnValue(true));
     $this->mockDriver->expects($this->any())->method('getConnection')->will($this->returnValue($this->mockConnection));
     $this->mockPlatform = $this->getMock('Zend\\Db\\Adapter\\Platform\\PlatformInterface');
     $this->mockStatement = $this->getMock('Zend\\Db\\Adapter\\Driver\\StatementInterface');
     $this->mockDriver->expects($this->any())->method('createStatement')->will($this->returnValue($this->mockStatement));
     $this->adapter = new Adapter($this->mockDriver, $this->mockPlatform);
     $sql = new Sql($this->adapter, 'foo');
     $select = new Select('myTable');
     $select->columns(['myCol', 'myCol2']);
     $this->filterSelect = new FilterSelect($sql, $select);
 }

 public function getDetails_OderDetails($id_user, $id_oeder)
 {
     $select = new Select('odersdetails');
     $select->columns(array('id_detail' => 'id', 'description' => 'description', 'status_details' => 'status', 'quantity' => 'quantity', 'cpmRate' => 'cpmRate', 'amount' => 'amount', 'type_details' => 'type', 'AdobeFlashEnabled' => 'AdobeFlashEnabled', 'MaxDailyBudget' => 'MaxDailyBudget', 'MobileTargeting' => 'MobileTargeting', 'trafficsources' => 'trafficsources', 'PreviousWebsite' => 'PreviousWebsite', 'payment_select' => 'payment_select'))->join('oders', 'oders.id = odersdetails.oder_id', array('id' => 'id', 'creatnamecampaign' => 'creatnamecampaign', 'date_creat' => 'date_creat', 'date_mod' => 'date_mod', 'id_user' => 'id_user', 'status' => 'status', 'status_oder' => 'status_oder', 'type' => 'type', 'DolarTotal' => 'DolarTotal', 'TotaVistor' => 'TotaVistor', 'Cpmrate' => 'Cpmrate'), 'left')->join('ads', 'ads.oder_id = oders.id', array('id_code' => 'id', 'delay' => 'delay', 'time_display' => 'time_display', 'namecampaigns' => 'name', 'js_id' => 'js_id', 'status_code' => 'status'), 'left')->join('process', 'process.oder_id = oders.id', array('date_reg' => 'date_reg', 'date_active' => 'date_active', 'checked' => 'checked', 'id_proc' => 'id', 'ads_id' => 'ads_id', 'advertiser_id' => 'advertiser_id', 'publisher_id' => 'publisher_id', 'type_id' => 'type_id', 'status_process' => 'status', 'website' => 'website'), 'left')->where(array('odersdetails.oder_id' => $id_oeder));
     //->order ( 'oders.id DESC' )->limit ( $limit )->offset ( $offset );
     $rowset = $this->tableGateway->getSql()->prepareStatementForSqlObject($select);
     $results = $rowset->execute();
     /*
     <div id="shoppingnew">
     	<input type="hidden" class="form-control" id="spVietnamid" name="palpost[Vietnam][id]" value="218">
     	<input type="hidden" class="form-control" id="spVietnamoder_id" name="palpost[Vietnam][oder]" value="105">
     	<input type="hidden" class="form-control" id="spVietnamtype" name="palpost[Vietnam][type]" value="0">
     	<input type="hidden" class="form-control" id="spVietnamnamepackge" name="palpost[Vietnam][namepackge]" value="Vietnam">
     	<input type="hidden" class="form-control" id="spVietnamprice" name="palpost[Vietnam][price]" value="0.4">
     	<input type="hidden" class="form-control" id="spVietnamvistor" name="palpost[Vietnam][vistor]" value="5">
     	<input type="hidden" class="form-control" id="spVietnamamount" name="palpost[Vietnam][amount]" value="2">
     </div>
     */
     $i = 0;
     $array = array();
     foreach ($results as $key => $value) {
         $array[$i]['namepackge'] = $value['description'];
         $array[$i]['id'] = $value['id_detail'];
         // id details
         $array[$i]['oder'] = $value['id'];
         // oder id
         $array[$i]['type'] = $value['type_details'];
         $array[$i]['price'] = $value['cpmRate'];
         $array[$i]['vistor'] = $value['quantity'];
         $array[$i]['amount'] = $value['cpmRate'] * $value['quantity'];
         $i++;
     }
     return $array;
 }

 /**
  *
  * @param int $days_threshold days threshold
  * @param int $ca_threshold turnover threshold
  * @param int $limit
  * @return type
  */
 public function getCustomerGeo($days_threshold = 300, $ca_threshold = 1000, $min_accuracy = 6, $limit = 1000)
 {
     $akilia2db = $this->configuration['synchronizer']['db_akilia2'];
     $select = new Select();
     $bcg = new \Zend\Db\Sql\TableIdentifier('base_customer_geo', $akilia2db);
     $bc = new \Zend\Db\Sql\TableIdentifier('base_customer', $akilia2db);
     $bs = new \Zend\Db\Sql\TableIdentifier('base_state', $akilia2db);
     $bco = new \Zend\Db\Sql\TableIdentifier('base_country', $akilia2db);
     $so = new \Zend\Db\Sql\TableIdentifier('sal_order', $akilia2db);
     $sol = new \Zend\Db\Sql\TableIdentifier('sal_order_line', $akilia2db);
     $select->from(["bc" => $bc], [])->join(['bcg' => $bcg], "bc.id = bcg.customer_id", [], Select::JOIN_LEFT)->join(['bs' => $bs], "bs.id = bc.state_id", [], Select::JOIN_LEFT)->join(['bco' => $bco], "bco.id = bc.country_id", [], Select::JOIN_LEFT)->join(['so' => $so], "bc.id = so.customer_id", [], Select::JOIN_INNER)->join(['sol' => $sol], "so.id = sol.order_id", [], Select::JOIN_INNER)->where('bc.flag_archived <> 1');
     $columns = ['customer_id' => new Expression('bc.id'), 'name' => new Expression('bc.name'), 'street' => new Expression('bc.street'), 'street_2' => new Expression('bc.street_2'), 'street_number' => new Expression('bc.street_number'), 'state_reference' => new Expression('bs.reference'), 'state_name' => new Expression('bs.name'), 'zipcode' => new Expression('bc.zipcode'), 'city' => new Expression('bc.city'), 'country' => new Expression('bco.name'), 'accuracy' => new Expression('bcg.accuracy'), 'latitude' => new Expression('bcg.latitude'), 'longitude' => new Expression('bcg.longitude')];
     $select->columns(array_merge($columns, ['total_net' => new Expression('sum(sol.price_total_net)')]), true);
     $select->group($columns);
     $select->having("sum(sol.price_total_net) > {$ca_threshold}");
     $select->where(function (Where $where) use($min_accuracy) {
         //$where->greaterThan('so.date_order', '2012-12-31');
         $where->notLike('bc.name', '%FINISHED%');
         $where->nest->lessThan('accuracy', $min_accuracy)->or->isNull('accuracy')->unnest;
     });
     $select->where(new Expression("(TO_DAYS(NOW()) - TO_DAYS(so.date_order)) < {$days_threshold}"));
     if ($limit > 0) {
         $select->limit($limit);
     }
     $store = $this->getStore($select);
     $data = $store->getData()->toArray();
     return $data;
 }

 public function find($params)
 {
     $sql = new Sql($this->getAdapter());
     $select = new Select();
     $select->from($params['from']);
     if (!empty($params['columns'])) {
         $select->columns($params['columns']);
     }
     foreach ($params['where'] as $where) {
         $select->where($where);
     }
     foreach ($params['joins'] as $join) {
         if (empty($join['columns'])) {
             $join['columns'] = Select::SQL_STAR;
         }
         if (empty($join['type'])) {
             $join['type'] = Select::JOIN_INNER;
         }
         $select->join($join['name'], $join['on'], $join['columns'], $join['type']);
     }
     $query = $sql->getSqlStringForSqlObject($select);
     $results = $this->adapter->query($query, Adapter::QUERY_MODE_EXECUTE);
     $data = $results->toArray();
     if (empty($data)) {
         return false;
     } else {
         if (count($data) == 1) {
             return $data[0];
         } else {
             return $data;
         }
     }
 }

 public function fetchAll($paginated = false, $sort = 'name', $order = 'ASC')
 {
     switch ($sort) {
         case "id":
             $sort = "C_Id";
             break;
         case "name":
             $sort = "C_Name";
             break;
         case "lastinvoice":
             $sort = "C_LastInvoice";
             break;
         default:
             $sort = "C_Name";
     }
     $select = new Select();
     $select->from($this->table);
     $subquery = "(SELECT I_BaseDate FROM Invoice WHERE Invoice.I_ClientId = Client.C_Id ORDER BY I_Id DESC LIMIT 1)";
     $select->columns(array('C_Id' => 'C_Id', 'C_Name' => 'C_Name', 'C_PaymentTerms' => 'C_PaymentTerms', 'C_Reference' => 'C_Reference', 'C_CRN' => 'C_CRN', 'C_Class' => 'C_Class', 'C_Updated' => 'C_Updated', 'C_LastInvoice' => new Expression($subquery)))->order($sort . ' ' . $order);
     if ($paginated) {
         return new Paginator(new DbSelect($select, $this->adapter, $this->resultSetPrototype));
     }
     $resultSet = $this->select($select);
     return $resultSet;
 }

 /**
  * @testdox unit test: Test columns() returns Select object (is chainable)
  * @covers Zend\Db\Sql\Select::columns
  */
 public function testColumns()
 {
     $select = new Select();
     $return = $select->columns(array('foo', 'bar'));
     $this->assertSame($select, $return);
     return $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 fetchDrafts()
 {
     $select = new Select();
     $select->from($this->table);
     $select->columns(array('I_Id' => 'I_Id', 'I_Number' => 'I_Number', 'I_BaseDate' => 'I_BaseDate', 'I_Vat' => 'I_Vat', 'I_DueDays' => 'I_DueDays', 'I_SentDate' => 'I_SentDate', 'I_PayedDate' => 'I_PayedDate', 'I_ClientId' => 'I_ClientId', 'I_ClientReference' => 'I_ClientReference', 'I_OwnReference' => 'I_OwnReference', 'I_AddressId' => 'I_AddressId', 'I_Text' => 'I_Text', 'I_Updated' => 'I_Updated'))->where(array(new \Zend\Db\Sql\Predicate\IsNull('I_Number')))->join('Client', 'Invoice.I_ClientId = Client.C_Id');
     $resultSet = $this->selectWith($select);
     return $resultSet;
 }

 public function getNewsList()
 {
     $select = new Select();
     $select->columns(['content' => 'en', 'title' => 'en_title', 'url' => 'en_title', 'date']);
     $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 getLoginUser()
 {
     $select = new Select(self::TABLE_NAME);
     $columns = array('emailId' => 'p_user_email_id', 'password' => 'p_user_password');
     $select->columns($columns);
     $sql = new Sql($this->tableGateway->getAdapter());
     $db = $this->tableGateway->getAdapter()->getDriver()->getConnection()->getResource();
     return $this->getSqlContent($db, $sql, $select);
 }

 public function get($id, $status = null)
 {
     $select = new Select();
     $select->from($this->tableGateway->getTable());
     $select->columns(array('*'));
     $select->where($status === null ? array('users.id' => $id) : array('users.id' => $id, 'users.status' => $status));
     $resultSet = $this->tableGateway->selectWith($select);
     $row = $resultSet->current();
     return !$row ? false : $row;
 }