PHP Select::group方法代码示例

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

 /**
  *
  * @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 countMessages($uid)
 {
     $fetchFn = function () use($uid) {
         $select = new Select($this->table);
         $select->columns(['id', 'read', 'count' => new Expression('COUNT(`id`)'), 'max_id' => new Expression('MAX(`message_id`)')])->where->equalTo('recipient', $uid);
         $select->group(['id', 'read']);
         $result = $this->selectWith($select)->toArray();
         return $result;
     };
     $cacheKey = MemcacheProvider::getKeyDirectusCountMessages($uid);
     $result = $this->memcache->getOrCache($cacheKey, $fetchFn, 1800);
     $count = ['read' => 0, 'unread' => 0, 'total' => 0, 'max_id' => 0];
     foreach ($result as $item) {
         if ((int) $item['max_id'] > $count['max_id']) {
             $count['max_id'] = (int) $item['max_id'];
         }
         switch ($item['read']) {
             case '1':
                 $count['read'] = (int) $item['count'];
                 break;
             case '0':
                 $count['unread'] = (int) $item['count'];
                 break;
         }
     }
     $count['total'] = $count['read'] + $count['unread'];
     return $count;
 }

 /**
  * Get paginator for listing
  *
  * @return \Zend\Paginator\Paginator
  */
 public function getPaginator()
 {
     $xTag = new Select('banner_x_set_by_tag');
     $xLocale = new Select('banner_x_set_by_locale');
     $xGlobal = new Select('banner_x_set_by_global');
     $xTag->join('banner_set_x_tag', 'banner_set_x_tag.id = banner_x_set_by_tag.setXTagId', array('setId'), Select::JOIN_INNER)->join('tag', 'tag.id = banner_set_x_tag.tagId', array(), Select::JOIN_INNER)->group('banner_set_x_tag.setId')->columns(array('tags' => new Expression('STRING_AGG( DISTINCT ?.?, ? )', array('tag', 'name', "\n"), array(Expression::TYPE_IDENTIFIER, Expression::TYPE_IDENTIFIER, Expression::TYPE_VALUE))));
     $xLocale->group('banner_x_set_by_locale.setId')->columns(array('setId', 'locales' => new Expression('STRING_AGG( DISTINCT ?, ? )', array('locale', "\n"), array(Expression::TYPE_IDENTIFIER, Expression::TYPE_VALUE))));
     $xGlobal->group('banner_x_set_by_global.setId')->columns(array('setId', 'globals' => new Expression('COUNT(*) > 0')));
     return $this->getMapper()->getPaginator(null, null, null, array('x_tag' => array('table' => array('x_tag' => $xTag), 'where' => 'x_tag.setId = banner_set.id', 'columns' => array('tags'), 'type' => Select::JOIN_LEFT), 'x_locale' => array('table' => array('x_locale' => $xLocale), 'where' => 'x_locale.setId = banner_set.id', 'columns' => array('locales'), 'type' => Select::JOIN_LEFT), 'x_global' => array('table' => array('x_global' => $xGlobal), 'where' => 'x_global.setId = banner_set.id', 'columns' => array('globals'), 'type' => Select::JOIN_LEFT)));
 }

 public function fetchByUniqueRoles(array $roleNames)
 {
     $select = new Select(self::$_tableName);
     $select->group('role');
     $select->where->in('role', $roleNames);
     $rows = $this->selectWith($select);
     $roles = [];
     foreach ($rows as $row) {
         $row = $row->toArray();
         // The adapter's `params` column is JSON serialized.
         $row['params'] = $this->jsonDecodeIfPossible($row['params']);
         $roles[$row['role']] = $row;
     }
     return $roles;
 }

 public function fetchAll()
 {
     $select = new Select();
     $select->from('items');
     //$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');
     //echo $select->getSqlString();
     $resultSet = $this->tableGateway->selectWith($select);
     //$resultSet = $this->tableGateway->select();
     return $resultSet;
 }

 public function selectCart($cart_id = null)
 {
     //NOTICE:   get all items with cart_id == '$cart_id'
     //NOTICE:   if $cart_id don't passed, return num of all items in cart (admin needed)
     if ($cart_id) {
         $select = new Select();
         $select->from('cart');
         $select->join('images', "images.img_item_id = cart.item_id", array('img_link'), 'left');
         $select->where->equalTo('cart.cart_id', $cart_id);
         $select->group('cart.item_id');
         $resultSet = $this->cartTableGateway->selectWith($select);
     } else {
         $resultSet = $this->cartTableGateway->select()->count();
     }
     return $resultSet;
 }

 public function getUserByUsername($username)
 {
     $selectPost = new Select();
     $selectPost->from('post');
     $selectPost->group('user_id');
     $selectPost->columns(['user_id', 'postcount' => new Expression('COUNT(user_id)')]);
     $predicate = new Predicate(null, Predicate::OP_AND);
     $predicate->equalTo('username', $username);
     $select = new Select();
     $select->from($this->tableName);
     $select->join(['po' => $selectPost], 'user.user_id = po.user_id', [], $select::JOIN_LEFT . ' ' . $select::JOIN_OUTER);
     $select->where($predicate);
     $select->columns(['user_id' => new Expression('user.user_id'), 'username', 'display_name', 'date_joined', 'role', 'title', 'avatar', 'post_signature', 'post_count' => new Expression('po.postcount')], false);
     $result = $this->select($select);
     return $result->toArray();
 }

 public function getWsr()
 {
     $dateStart = date('Y-m-01');
     $dateEnd = date('Y-m-t');
     $select = new Select(self::TABLE_NAME);
     $columns = array('projectName' => 'r_project_name', 'quality' => 'r_project_quality', 'schedule' => 'r_project_schedule', 'process' => 'r_project_process', 'effortVariance' => 'r_project_effort_variance', 'customerManagment' => 'r_project_customer_managment', 'currentMonthBillingTarget' => 'r_project_current_month_billing_target', 'comment' => 'r_project_description');
     $select->columns($columns);
     $subQry = new Select(TimeEntryTable::TABLE_NAME);
     $subQry->columns(array("projectId" => 'r_project_id', 'consumeHrs' => new Expression('SUM(r_issue_timelog_logged_hours)')));
     $subQry->group('r_project_id');
     $subQry2 = new Select(TimeEntryTable::TABLE_NAME);
     $subQry2->columns(array("projectID" => 'r_project_id', 'currentMonthConsumeHrs' => new Expression('SUM(r_issue_timelog_logged_hours)')));
     $subQry2->where->between('r_issue_timelog_date', $dateStart, $dateEnd);
     $subQry2->group('r_project_id');
     $subQry3 = new Select(IssueTable::TABLE_NAME);
     $subQry3->columns(array("sub3ID" => 'r_project_id', 'effort' => new Expression('SUM(r_issue_estimated_hours)')));
     $subQry3->where->between('r_issue_start_date', $dateStart, $dateEnd);
     $subQry3->where->between('r_issue_due_date', $dateStart, $dateEnd);
     $subQry3->where(array('r_issue_tracker_code' => 26));
     $subQry3->group('r_project_id');
     $subQry4 = new Select(IssueTable::TABLE_NAME);
     $subQry4->columns(array("sub4ID" => 'r_project_id', 'estimate' => new Expression('SUM(r_issue_estimated_hours)')));
     $subQry4->join(array('t' => TimeEntryTable::TABLE_NAME), 't.r_issue_id=r_issue.r_issue_id', array('issueID' => 'r_issue_id', 'spent' => new Expression('SUM(r_issue_timelog_logged_hours)')), Select::JOIN_LEFT);
     $subQry4->where(array('r_issue_tracker_code' => 26));
     $subQry4->where->between('r_issue_due_date', $dateStart, $dateEnd);
     //$subQry4->where->greaterThanOrEqualTo('r_issue_start_date',$dateStart);
     $subQry4->group('r_issue.r_project_id');
     $subQry5 = new Select(IssueTable::TABLE_NAME);
     $subQry5->columns(array("sub5ID" => 'r_project_id', 'totalEstimate' => new Expression('SUM(r_issue_estimated_hours)'), 'start_day_of_project' => new Expression('DAY(r_issue_start_date)'), 'end_day_of_month' => new Expression('DAY("2015-09-30")'), 'diffrence_dates' => new Expression('DATEDIFF(r_issue_due_date,r_issue_start_date)')));
     $subQry5->where->between('r_issue_start_date', $dateStart, $dateEnd);
     //$subQry5->where->greaterThan('r_issue_due_date',$dateEnd);
     $subQry5->where(array('r_issue_tracker_code' => 26));
     $subQry5->group('r_project_id');
     $select->join(array('issue' => IssueTable::TABLE_NAME), 'issue.r_project_id=r_project.r_project_id', array('owner' => new Expression('MAX(r_user_name)'), 'estimatedHours' => new Expression('SUM(r_issue_estimated_hours)'), 'percentComplete' => 'r_issue_completion_ratio', 'completionDate' => 'r_issue_target_completion_date', 'projectType' => 'r_project_type'), Select::JOIN_LEFT);
     $select->join(array('sub' => $subQry), 'sub.projectId = r_project.r_project_id', array('totalConsumeHrs' => 'consumeHrs'), Select::JOIN_LEFT);
     $select->join(array('sub2' => $subQry2), 'sub2.projectID = r_project.r_project_id', array('totalCurrentMnthConsumeHrs' => 'currentMonthConsumeHrs'), Select::JOIN_LEFT);
     $select->join(array('sub3' => $subQry3), 'sub3.sub3ID = r_project.r_project_id', array('effort' => 'effort'), Select::JOIN_LEFT);
     $select->join(array('sub4' => $subQry4), 'sub4.sub4ID = r_project.r_project_id', array('estimate' => 'estimate', 'spent' => 'spent'), Select::JOIN_LEFT);
     $select->join(array('sub5' => $subQry5), 'sub5.sub5ID = r_project.r_project_id', array('totalEstimate' => 'totalEstimate', 'startDay' => 'start_day_of_project', 'endDay' => 'end_day_of_month', 'diffDate' => 'diffrence_dates'), Select::JOIN_LEFT);
     $select->where(array('r_issue_tracker_code' => 26));
     $select->group('r_project_name', 'issue.r_user_name');
     $sql = new Sql($this->tableGateway->getAdapter());
     $db = $this->tableGateway->getAdapter()->getDriver()->getConnection()->getResource();
     return $this->getSqlContent($db, $sql, $select);
 }

 public function fetchById($item_id, $param = 'single')
 {
     //die($item_id);
     $select = new Select();
     $select->from('items');
     $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->where->equalTo('items.item_id', $item_id);
     switch ($param) {
         case 'single':
             $select->group('items.item_id');
             break;
         case 'group':
             break;
     }
     $resultSet = $this->tableGateway->selectWith($select);
     return $resultSet;
 }

 public function getPostsByDate($threadId, $pageNo = 1)
 {
     $selectPost = new Select();
     $selectPost->from('post');
     $selectPost->group('user_id');
     $selectPost->columns(['user_id', 'postcount' => new Expression('COUNT(user_id)')]);
     $predicate = new Predicate(null, Predicate::OP_AND);
     $predicate->equalTo('thread_id', $threadId);
     $select = new Select();
     $select->from($this->tableName);
     $select->order('date_added ASC');
     $select->join(['po' => $selectPost], 'post.user_id = po.user_id', [], $select::JOIN_LEFT . ' ' . $select::JOIN_OUTER);
     $select->join('user', 'user.user_id = post.user_id', [], $select::JOIN_LEFT . ' ' . $select::JOIN_OUTER);
     $select->limit(20);
     $select->offset(($pageNo - 1) * 20);
     $select->columns(['id', 'date_added', 'content', 'last_updated', 'username' => new Expression('user.username'), 'user_title' => new Expression('user.title'), 'user_avatar' => new Expression('user.avatar'), 'user_signature' => new Expression('user.post_signature'), 'user_joined' => new Expression('user.date_joined'), 'user_postcount' => new Expression('po.postcount')], false);
     $select->where($predicate);
     $result = $this->select($select);
     return $result->toArray();
 }

 public function getTimeline()
 {
     $select = new Select();
     $select->from(array('fl' => 'fg_flicks'));
     $select->columns(array('*'));
     $select->join(array('fr' => 'fg_friends'), 'fl.owner = fr.user_one OR fl.owner = fr.user_two', array());
     $select->join(array('u' => 'fg_users'), 'fl.owner = u.id', array("nickname"));
     $where = new Where();
     $or = $where->nest();
     $or->equalTo('fr.user_one', $this->user_id);
     $or->OR->equalTo('fr.user_two', $this->user_id);
     $or->unnest();
     $where->AND->equalTo('state', 1);
     $where->AND->notEqualTo('fl.owner', $this->user_id);
     $select->where($where);
     $select->order(array('id DESC'));
     $select->group("id");
     $statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($select);
     $resultSet = $statement->execute();
     // var_dump($resultSet->current());
     // exit;
     return $resultSet;
 }

 public function getProjectList()
 {
     $dateStart = '2015-08-01';
     $dateEnd = '2015-08-31';
     $select = new Select(self::TABLE_NAME);
     $columns = array('projectName' => 'r_project_name', 'quality' => 'r_project_quality', 'schedule' => 'r_project_schedule', 'process' => 'r_project_process', 'effortVariance' => 'r_project_effort_variance', 'comment' => 'r_project_description');
     $select->columns($columns);
     $subQry = new Select(TimeEntryTable::TABLE_NAME);
     $subQry->columns(array("projectId" => 'r_project_id', 'consumeHrs' => new Expression('SUM(r_issue_timelog_logged_hours)')));
     $subQry->group('r_project_id');
     $subQry2 = new Select(TimeEntryTable::TABLE_NAME);
     $subQry2->columns(array("projectID" => 'r_project_id', 'currentMonthConsumeHrs' => new Expression('SUM(r_issue_timelog_logged_hours)')));
     $subQry2->where->between('r_issue_timelog_date', $dateStart, $dateEnd);
     $subQry2->group('r_project_id');
     $select->join(array('issue' => ProductionIssueTable::TABLE_NAME), 'issue.r_project_id=r_project.r_project_id', array('owner' => new Expression('MAX(r_user_name)'), 'estimatedHours' => new Expression('SUM(r_issue_estimated_hours)'), 'percentRemain' => 'r_issue_completion_ratio', 'completionDate' => 'r_issue_due_date', 'hoursNeedToFinish' => new Expression('SUM(r_hours_needed_to_finish)')), Select::JOIN_INNER);
     $select->join(array('sub' => $subQry), 'sub.projectId = r_project.r_project_id', array('totalConsumeHrs' => 'consumeHrs'), Select::JOIN_INNER);
     $select->join(array('sub2' => $subQry2), 'sub2.projectID = r_project.r_project_id', array('totalCurrentMnthConsumeHrs' => 'currentMonthConsumeHrs'), Select::JOIN_INNER);
     $select->where(array('r_issue_tracker_code' => 26));
     $select->group('r_project_name', 'issue.r_user_name');
     $sql = new Sql($this->tableGateway->getAdapter());
     $db = $this->tableGateway->getAdapter()->getDriver()->getConnection()->getResource();
     return $this->getSqlContent($db, $sql, $select);
 }

 public function getSources($active = true, $format = 'array')
 {
     $select = new Select();
     $select->from('form');
     $select->columns(array('id' => 'form.id', 'source' => 'form.source'), false);
     if ($active) {
         $select->join('lead', 'form.id = lead.formid', array('formid'), $select::JOIN_INNER);
     }
     $select->order(array("form.source asc"));
     $select->group('form.id');
     // echo "<pre>" . print_r($select->getSqlString(), true) . "</pre>";
     $resultSet = $this->tableGateways['form']->selectWith($select);
     return $format == 'array' ? $resultSet->toArray() : $resultSet;
 }

 public function fetchMessagesInbox($uid, $messageId = null)
 {
     $select = new Select($this->table);
     $select->columns(['message_id' => 'response_to', 'thread_length' => new Expression('COUNT(`directus_messages`.`id`)')])->join('directus_messages_recipients', 'directus_messages_recipients.message_id = directus_messages.id', ['id', 'message_id', 'recipient', 'read', 'group']);
     $select->where->equalTo('recipient', $uid);
     if (!empty($messageId)) {
         if (gettype($messageId) == 'array') {
             $select->where->in('response_to', $messageId)->or->in('directus_messages.id', $messageId);
         } else {
             $select->where->nest->equalTo('response_to', $messageId)->or->equalTo('directus_messages.id', $messageId)->unnest;
         }
     }
     $select->group(['directus_messages_recipients.id', 'directus_messages_recipients.message_id', 'directus_messages_recipients.recipient', 'directus_messages_recipients.read', 'directus_messages_recipients.group', 'response_to', 'directus_messages.id'])->order('directus_messages.id DESC');
     $result = $this->selectWith($select)->toArray();
     $messageIds = [];
     foreach ($result as $message) {
         $messageIds[] = $message['message_id'];
     }
     if (sizeof($messageIds) == 0) {
         return [];
     }
     $result = $this->fetchMessageThreads($messageIds, $uid);
     if (sizeof($result) == 0) {
         return [];
     }
     $resultLookup = [];
     $ids = [];
     // Grab ids;
     foreach ($result as $item) {
         $ids[] = $item['id'];
     }
     $directusMessagesTableGateway = new DirectusMessagesRecipientsTableGateway($this->acl, $this->adapter);
     $recipients = $directusMessagesTableGateway->fetchMessageRecipients($ids);
     foreach ($result as $item) {
         $item['responses'] = ['rows' => []];
         $item['recipients'] = implode(',', $recipients[$item['id']]);
         $resultLookup[$item['id']] = $item;
     }
     foreach ($result as $item) {
         if ($item['response_to'] != NULL) {
             // Move it to resultLookup
             unset($resultLookup[$item['id']]);
             $item = $this->parseRecord($item);
             $resultLookup[$item['response_to']]['responses']['rows'][] = $item;
         }
     }
     $result = array_values($resultLookup);
     foreach ($result as &$row) {
         $row = $this->parseRecord($row);
     }
     // Add date_updated
     // Update read
     foreach ($result as &$message) {
         $responses = $message['responses']['rows'];
         /*foreach ($responses as $response) {
               if($response['read'] == "0") {
                   $message['read'] = "0";
                   break;
               }
           }*/
         $lastResponse = end($responses);
         if ($lastResponse) {
             $message['date_updated'] = $lastResponse['datetime'];
         } else {
             $message['date_updated'] = $message['datetime'];
         }
     }
     return $result;
 }