PHP QQ::GroupBy方法代码示例

 public function testGroupBy()
 {
     $objItems = Project::QueryArray(QQ::All(), QQ::Clause(QQ::GroupBy(QQN::Project()->Id), QQ::Count(QQN::Project()->PersonAsTeamMember->PersonId, 'team_member_count')));
     $this->assertEqual(sizeof($objItems), 4, "4 projects found");
     $this->assertEqual($objItems[0]->Name, "ACME Website Redesign", "Project " . $objItems[0]->Name . " found");
     $this->assertEqual($objItems[0]->GetVirtualAttribute('team_member_count'), 5, "5 team members found for project " . $objItems[0]->Name);
     $this->assertEqual($objItems[1]->Name, "State College HR System", "Project " . $objItems[1]->Name . " found");
     $this->assertEqual($objItems[1]->GetVirtualAttribute('team_member_count'), 6, "6 team members found for project " . $objItems[1]->Name);
     $this->assertEqual($objItems[2]->Name, "Blueman Industrial Site Architecture", "Project " . $objItems[2]->Name . " found");
     $this->assertEqual($objItems[2]->GetVirtualAttribute('team_member_count'), 5, "5 team members found for project " . $objItems[2]->Name);
 }

 public function testAlias3()
 {
     $emptySelect = QQ::Select();
     $emptySelect->SetSkipPrimaryKey(true);
     $nVoyel = QQ::Alias(QQN::Person()->ProjectAsManager->Milestone, 'voyel');
     $nConson = QQ::Alias(QQN::Person()->ProjectAsManager->Milestone, 'conson');
     $objPersonArray = Person::QueryArray(QQ::IsNotNull($nConson->Id), QQ::Clause(QQ::Expand($nVoyel, QQ::In($nVoyel->Name, array('Milestone A', 'Milestone E', 'Milestone I')), $emptySelect), QQ::Expand($nConson, QQ::NotIn($nConson->Name, array('Milestone A', 'Milestone E', 'Milestone I')), $emptySelect), QQ::GroupBy(QQN::Person()->Id), QQ::Minimum($nVoyel->Name, 'min_voyel'), QQ::Minimum($nConson->Name, 'min_conson'), QQ::Expand(QQN::Person()->ProjectAsManager, null, $emptySelect), QQ::Minimum(QQN::Person()->ProjectAsManager->Id, 'dummy'), QQ::Select(QQN::Person()->FirstName, QQN::Person()->LastName)));
     $this->assertEquals(3, sizeof($objPersonArray));
     $obj = $this->verifyObjectPropertyHelper($objPersonArray, 'LastName', 'Doe');
     $this->assertNull($obj->GetVirtualAttribute('min_voyel'));
     $this->assertEquals('Milestone F', $obj->GetVirtualAttribute('min_conson'));
     $obj = $this->verifyObjectPropertyHelper($objPersonArray, 'LastName', 'Ho');
     $this->assertEquals('Milestone E', $obj->GetVirtualAttribute('min_voyel'));
     $this->assertEquals('Milestone D', $obj->GetVirtualAttribute('min_conson'));
     $obj = $this->verifyObjectPropertyHelper($objPersonArray, 'LastName', 'Wolfe');
     $this->assertEquals('Milestone A', $obj->GetVirtualAttribute('min_voyel'));
     $this->assertEquals('Milestone B', $obj->GetVirtualAttribute('min_conson'));
 }

 public function testHaving()
 {
     $objItems = Project::QueryArray(QQ::All(), QQ::Clause(QQ::GroupBy(QQN::Project()->Id), QQ::Count(QQN::Project()->PersonAsTeamMember->PersonId, 'team_member_count'), QQ::Having(QQ::SubSql('COUNT({1}) > 5', QQN::Project()->PersonAsTeamMember->PersonId)), QQ::OrderBy(QQN::Project()->Id)));
     $this->assertEqual(sizeof($objItems), 2, "2 projects found");
     $this->assertEqual($objItems[0]->Name, "State College HR System", "Project " . $objItems[0]->Name . " found");
     $this->assertEqual($objItems[0]->GetVirtualAttribute('team_member_count'), 6, "6 team members found for project " . $objItems[0]->Name);
 }

    _p('<li>' . $objPerson->FirstName . ' ' . $objPerson->LastName . '</li>', false);
}
?>
	</ul>
	<h2>Select all Projects and the Count of Team Members (if applicable)</h2>
	<p><em>GROUP BY in action</em></p>
	<ul>
<?php 
$objProjectArray = Project::QueryArray(QQ::All(), QQ::Clause(QQ::GroupBy(QQN::Project()->Id), QQ::Count(QQN::Project()->PersonAsTeamMember->PersonId, 'team_member_count')));
foreach ($objProjectArray as $objProject) {
    _p('<li>' . $objProject->Name . ' (' . $objProject->GetVirtualAttribute('team_member_count') . ' team members)' . '</li>', false);
}
?>
	</ul>

	<h2>Select all Projects with more than 5 team members. </h2>
	<p><em>Using a Having clause to further limit group functions</em></p>
	<ul>
<?php 
$objProjectArray = Project::QueryArray(QQ::All(), QQ::Clause(QQ::GroupBy(QQN::Project()->Id), QQ::Count(QQN::Project()->PersonAsTeamMember->PersonId, 'team_member_count'), QQ::Having(QQ::SubSql('COUNT({1}) > 5', QQN::Project()->PersonAsTeamMember->PersonId))));
foreach ($objProjectArray as $objProject) {
    _p($objProject->Name . ' (' . $objProject->GetVirtualAttribute('team_member_count') . ' team members)');
    _p('<br/>', false);
}
?>
	</ul>
</div>


<?php 
require '../includes/footer.inc.php';

 public function __get($strName)
 {
     switch ($strName) {
         case 'Votes':
             $mixResult = NarroSuggestionVote::QuerySingle(QQ::Equal(QQN::NarroSuggestionVote()->SuggestionId, $this->SuggestionId), array(QQ::Sum(QQN::NarroSuggestionVote()->VoteValue, 'votes'), QQ::GroupBy(QQN::NarroSuggestionVote()->SuggestionId)));
             if ($mixResult instanceof NarroSuggestionVote) {
                 return $mixResult->GetVirtualAttribute('votes');
             } else {
                 return 0;
             }
         default:
             try {
                 return parent::__get($strName);
                 break;
             } catch (QCallerException $objExc) {
                 $objExc->IncrementOffset();
                 throw $objExc;
             }
     }
 }

 public function testVirtualAttributeAliases()
 {
     $clauses = [QQ::GroupBy(QQN::Project()->ProjectStatusTypeId), QQ::Sum(QQN::Project()->Budget, 'Budget Amount'), QQ::Expand(QQ::Virtual('Balance', QQ::Func('SUM', QQ::Sub(QQN::Project()->Budget, QQN::Project()->Spent))))];
     $cond = QQ::Equal(QQN::Project()->ProjectStatusTypeId, ProjectStatusType::Open);
     $objProject = Project::QuerySingle($cond, $clauses);
     $amount1 = $objProject->GetVirtualAttribute('Budget Amount');
     $this->assertEquals(83000, $amount1);
     $amount2 = $objProject->GetVirtualAttribute('Balance');
     $this->assertEquals(5599.5, $amount2);
 }

 public function dtgLanguage_Bind()
 {
     if ($this->txtSearch->Text != '') {
         $objSearchCondition = QQ::Like(QQN::NarroLanguage()->LanguageName, sprintf('%%%s%%', $this->txtSearch->Text));
     } else {
         $objSearchCondition = QQ::All();
     }
     switch ($this->lstFilter->SelectedValue) {
         /**
          * Only active
          */
         case 1:
             $objFilterCondition = QQ::AndCondition($objSearchCondition, QQ::Equal(QQN::NarroLanguage()->Active, 1));
             break;
             /**
              * 0 - show all
              */
         /**
          * 0 - show all
          */
         default:
             $objFilterCondition = $objSearchCondition;
     }
     // Because we want to enable pagination AND sorting, we need to setup the $objClauses array to send to LoadAll()
     // Remember!  We need to first set the TotalItemCount, which will affect the calcuation of LimitClause below
     $this->dtgLanguage->TotalItemCount = NarroLanguage::QueryCount($objFilterCondition);
     // Setup the $objClauses Array
     $objClauses = array(QQ::Expand(QQ::Virtual('last_translation', QQ::SubSql('SELECT MAX(created) FROM narro_suggestion WHERE language_id={1}', QQN::NarroLanguage()->LanguageId))), QQ::Count(QQN::NarroLanguage()->NarroSuggestionAsLanguage->SuggestionId, 'translations_count'), QQ::GroupBy(QQN::NarroLanguage()->LanguageId));
     // If a column is selected to be sorted, and if that column has a OrderByClause set on it, then let's add
     // the OrderByClause to the $objClauses array
     if ($objClause = $this->dtgLanguage->OrderByClause) {
         array_push($objClauses, $objClause);
     }
     // Add the LimitClause information, as well
     if ($objClause = $this->dtgLanguage->LimitClause) {
         array_push($objClauses, $objClause);
     }
     // Set the DataSource to be the array of all NarroLanguage objects, given the clauses above
     $this->dtgLanguage->DataSource = NarroLanguage::QueryArray($objFilterCondition, $objClauses);
     QApplication::ExecuteJavaScript('highlight_datagrid();');
 }

$objPersonArray = Person::QueryArray(QQ::All(), QQ::Clause(QQ::OrderBy(QQN::Person()->LastName, QQN::Person()->FirstName)));
foreach ($objPersonArray as $objPerson) {
    _p($objPerson->FirstName . ' ' . $objPerson->LastName);
    _p('<br/>', false);
}
?>



	<h3>Select all People, Ordered by Last Name then First Name, Limited to the first 4 results</h3>
<?php 
$objPersonArray = Person::QueryArray(QQ::All(), QQ::Clause(QQ::OrderBy(QQN::Person()->LastName, QQN::Person()->FirstName), QQ::LimitInfo(4)));
foreach ($objPersonArray as $objPerson) {
    _p($objPerson->FirstName . ' ' . $objPerson->LastName);
    _p('<br/>', false);
}
?>



	<h3>Select all Projects and the Count of Team Members (if applicable)</h3>
<?php 
$objProjectArray = Project::QueryArray(QQ::All(), QQ::Clause(QQ::GroupBy(QQN::Project()->Id), QQ::Count(QQN::Project()->PersonAsTeamMember->PersonId, 'team_member_count')));
foreach ($objProjectArray as $objProject) {
    _p($objProject->Name . ' (' . $objProject->GetVirtualAttribute('team_member_count') . ' team members)');
    _p('<br/>', false);
}
?>

<?php 
require __INCLUDES__ . '/examples/footer.inc.php';

}
?>

	<h2>Example 3: Managers having one least a project with a conson milestone, and for each manager, the first voyel milestone and the first conson one</h2>
<?php 
$emptySelect = QQ::Select();
$emptySelect->SetSkipPrimaryKey(true);
$nVoyel = QQ::Alias(QQN::Person()->ProjectAsManager->Milestone, 'voyel');
$nConson = QQ::Alias(QQN::Person()->ProjectAsManager->Milestone, 'conson');
$objPersonArray = Person::QueryArray(QQ::IsNotNull($nConson->Id), QQ::Clause(QQ::Expand(QQN::Person()->ProjectAsManager, null, $emptySelect), QQ::Expand($nVoyel, QQ::In($nVoyel->Name, array('Milestone A', 'Milestone E', 'Milestone I')), $emptySelect), QQ::Expand($nConson, QQ::NotIn($nConson->Name, array('Milestone A', 'Milestone E', 'Milestone I')), $emptySelect), QQ::GroupBy(QQN::Person()->Id), QQ::Minimum($nVoyel->Name, 'min_voyel'), QQ::Minimum($nConson->Name, 'min_conson'), QQ::Expand(QQN::Person()->ProjectAsManager, null, $emptySelect), QQ::Minimum(QQN::Person()->ProjectAsManager->Id, 'dummy'), QQ::Select(QQN::Person()->FirstName, QQN::Person()->LastName)));
foreach ($objPersonArray as $objManager) {
    _p($objManager->FirstName . ' ' . $objManager->LastName . " (" . $objManager->GetVirtualAttribute('min_voyel') . ', ' . $objManager->GetVirtualAttribute('min_conson') . ")");
    _p('<br/>', false);
}
?>

	<h2>Example 4: Projects with, for each one, the "min" city from the addresses containing 'r' and the "min" city from the addresses NOT containing 'r' </h2>
<?php 
$nWithR = QQ::Alias(QQN::Project()->PersonAsTeamMember->Person->Address, 'with_r');
$nWithoutR = QQ::Alias(QQN::Project()->PersonAsTeamMember->Person->Address, 'without_r');
$objProjectArray = Project::QueryArray(QQ::All(), QQ::Clause(QQ::Expand($nWithR, QQ::Like($nWithR->Street, '%r%')), QQ::Expand($nWithoutR, QQ::NotLike($nWithoutR->Street, '%r%')), QQ::GroupBy(QQN::Project()->Id), QQ::Minimum($nWithR->City, 'min_city_r'), QQ::Minimum($nWithoutR->City, 'min_city_wor')));
foreach ($objProjectArray as $objProject) {
    _p($objProject->Name . " (" . $objProject->GetVirtualAttribute('min_city_r') . ', ' . $objProject->GetVirtualAttribute('min_city_wor') . ")");
    _p('<br/>', false);
}
QApplication::$Database[1]->OutputProfiling();
?>
</div>

<?php 
require '../includes/footer.inc.php';

 public static function CountByFileName($strFileName, $intFilter, $objExtraCondition = null)
 {
     if (!is_object($objExtraCondition)) {
         $objExtraCondition = QQ::All();
     }
     if (trim($strFileName) == '') {
         $objSearchCondition = QQ::All();
     } elseif (preg_match("/^'.*'\$/", $strFileName)) {
         $objSearchCondition = QQ::Equal(QQN::NarroContextInfo()->Context->File->FileName, substr($strFileName, 1, -1));
     } else {
         $objSearchCondition = QQ::Like(QQN::NarroContextInfo()->Context->File->FileName, '%' . $strFileName . '%');
     }
     switch ($intFilter) {
         case NarroTextListForm::SHOW_UNTRANSLATED_TEXTS:
             $objFilterCondition = QQ::Equal(QQN::NarroContextInfo()->HasSuggestions, 0);
             break;
         case NarroTextListForm::SHOW_APPROVED_TEXTS:
             $objFilterCondition = QQ::IsNotNull(QQN::NarroContextInfo()->ValidSuggestionId);
             break;
         case NarroTextListForm::SHOW_TEXTS_THAT_REQUIRE_APPROVAL:
             $objFilterCondition = QQ::AndCondition(QQ::IsNull(QQN::NarroContextInfo()->ValidSuggestionId), QQ::Equal(QQN::NarroContextInfo()->HasSuggestions, 1));
             break;
         default:
             // no filters
             $objFilterCondition = QQ::All();
     }
     $intContextCount = NarroContextInfo::QueryCount(QQ::AndCondition($objSearchCondition, $objFilterCondition, $objExtraCondition), array(QQ::GroupBy(QQN::NarroContextInfo()->ContextId)));
     return $intContextCount;
 }

 public function __construct($strDate, $objParentObject, NarroProject $objProject = null, $strControlId = null)
 {
     // Call the Parent
     try {
         parent::__construct($objParentObject, $strControlId);
     } catch (QCallerException $objExc) {
         $objExc->IncrementOffset();
         throw $objExc;
     }
     $this->strTemplate = dirname(__FILE__) . '/' . __CLASS__ . '.tpl.php';
     $this->blnAutoRenderChildren = true;
     // Tabs
     $this->tabTop = new QTabs($this);
     $pnlTranslators = new NarroUserDataGrid($this->tabTop);
     $pnlTranslators->CssClass = 'datagrid';
     $colUsername = $pnlTranslators->MetaAddColumn(QQN::NarroUser()->Username);
     $colUsername->HtmlEntities = false;
     $colUsername->Html = '<?=NarroLink::UserProfile($_ITEM->UserId, $_ITEM->Username)?>';
     $colCnt = new QDataGridColumn(t('Translations'));
     $colCnt->Html = '<?=$_ITEM->GetVirtualAttribute("suggestion_cnt")?>';
     $pnlTranslators->SortColumnIndex = 1;
     $pnlTranslators->ShowHeader = false;
     $pnlTranslators->SortDirection = 1;
     $pnlTranslators->AddColumn($colCnt);
     $pnlTranslators->AdditionalConditions = QQ::AndCondition(QQ::GreaterOrEqual(QQN::NarroUser()->NarroSuggestionAsUser->Created, $strDate), QQ::Equal(QQN::NarroUser()->NarroSuggestionAsUser->LanguageId, QApplication::GetLanguageId()), QQ::NotEqual(QQN::NarroUser()->UserId, NarroUser::ANONYMOUS_USER_ID), $objProject ? QQ::Equal(QQN::NarroUser()->NarroSuggestionAsUser->Text->NarroContextAsText->ProjectId, $objProject->ProjectId) : QQ::All());
     $pnlTranslators->AdditionalClauses = array(QQ::Count(QQN::NarroUser()->NarroSuggestionAsUser->SuggestionId, 'suggestion_cnt'), QQ::GroupBy(QQN::NarroUser()->UserId));
     $pnlReviewers = new NarroUserDataGrid($this->tabTop);
     $pnlReviewers->CssClass = 'datagrid';
     $colUsername = $pnlReviewers->MetaAddColumn(QQN::NarroUser()->Username);
     $colUsername->HtmlEntities = false;
     $colUsername->Html = '<?=NarroLink::UserProfile($_ITEM->UserId, $_ITEM->Username)?>';
     $colCnt = new QDataGridColumn(t('Reviewers'));
     $colCnt->Html = '<?=$_ITEM->GetVirtualAttribute("suggestion_cnt")?>';
     $pnlReviewers->SortColumnIndex = 1;
     $pnlReviewers->ShowHeader = false;
     $pnlReviewers->SortDirection = 1;
     $pnlReviewers->AddColumn($colCnt);
     $pnlReviewers->AdditionalConditions = QQ::AndCondition(QQ::GreaterOrEqual(QQN::NarroUser()->NarroContextInfoAsValidatorUser->Modified, $strDate), QQ::Equal(QQN::NarroUser()->NarroContextInfoAsValidatorUser->LanguageId, QApplication::GetLanguageId()), QQ::NotEqual(QQN::NarroUser()->UserId, NarroUser::ANONYMOUS_USER_ID), $objProject ? QQ::Equal(QQN::NarroUser()->NarroContextInfoAsValidatorUser->Context->ProjectId, $objProject->ProjectId) : QQ::All());
     $pnlReviewers->AdditionalClauses = array(QQ::Count(QQN::NarroUser()->NarroContextInfoAsValidatorUser->ValidSuggestionId, 'suggestion_cnt'), QQ::GroupBy(QQN::NarroUser()->UserId));
     $pnlVoters = new NarroUserDataGrid($this->tabTop);
     $pnlVoters->CssClass = 'datagrid';
     $colUsername = $pnlVoters->MetaAddColumn(QQN::NarroUser()->Username);
     $colUsername->HtmlEntities = false;
     $colUsername->Html = '<?=NarroLink::UserProfile($_ITEM->UserId, $_ITEM->Username)?>';
     $colCnt = new QDataGridColumn(t('Reviewers'));
     $colCnt->Html = '<?=$_ITEM->GetVirtualAttribute("suggestion_cnt")?>';
     $pnlVoters->SortColumnIndex = 1;
     $pnlVoters->ShowHeader = false;
     $pnlVoters->SortDirection = 1;
     $pnlVoters->AddColumn($colCnt);
     $pnlVoters->AdditionalConditions = QQ::AndCondition(QQ::GreaterOrEqual(QQN::NarroUser()->NarroSuggestionVoteAsUser->Created, $strDate), QQ::Equal(QQN::NarroUser()->NarroSuggestionVoteAsUser->Suggestion->LanguageId, QApplication::GetLanguageId()), QQ::NotEqual(QQN::NarroUser()->UserId, NarroUser::ANONYMOUS_USER_ID), $objProject ? QQ::Equal(QQN::NarroUser()->NarroSuggestionVoteAsUser->Suggestion->Text->NarroContextAsText->ProjectId, $objProject->ProjectId) : QQ::All());
     $pnlVoters->AdditionalClauses = array(QQ::Count(QQN::NarroUser()->NarroSuggestionVoteAsUser->SuggestionId, 'suggestion_cnt'), QQ::GroupBy(QQN::NarroUser()->UserId));
     $pnlComments = new NarroUserDataGrid($this->tabTop);
     $pnlComments->CssClass = 'datagrid';
     $colUsername = $pnlComments->MetaAddColumn(QQN::NarroUser()->Username);
     $colUsername->HtmlEntities = false;
     $colUsername->Html = '<?=NarroLink::UserProfile($_ITEM->UserId, $_ITEM->Username)?>';
     $colCnt = new QDataGridColumn(t('Reviewers'));
     $colCnt->Html = '<?=$_ITEM->GetVirtualAttribute("suggestion_cnt")?>';
     $pnlComments->SortColumnIndex = 1;
     $pnlComments->ShowHeader = false;
     $pnlComments->SortDirection = 1;
     $pnlComments->AddColumn($colCnt);
     $pnlComments->AdditionalConditions = QQ::AndCondition(QQ::GreaterOrEqual(QQN::NarroUser()->NarroTextCommentAsUser->Created, $strDate), QQ::Equal(QQN::NarroUser()->NarroTextCommentAsUser->LanguageId, QApplication::GetLanguageId()), QQ::NotEqual(QQN::NarroUser()->UserId, NarroUser::ANONYMOUS_USER_ID));
     $pnlComments->AdditionalClauses = array(QQ::Count(QQN::NarroUser()->NarroTextCommentAsUser->TextCommentId, 'suggestion_cnt'), QQ::GroupBy(QQN::NarroUser()->UserId));
     $this->tabTop->Headers = array(t('Translators'), t('Reviewers'), t('Voters'), t('Comments'));
 }