PHP QQ::SubSql方法代码示例

    _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 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>Note: the code below generates <a href="http://docs.hp.com/en/36216-90103/ch03s02.html">
	correlated (dependent) subqueries</a>. These are frequently not the
	fastest way to run queries against your SQL engine. If there is an
	opportunity to rewrite your subquery using simple joins, do it - this
	will improve the performance of your applications dramatically.</p>

	<p>In general, it's a good idea to use EXPLAIN statements to determine
	the query execution plan of the SQL statement that QQuery generates
	to determine what the SQL engine will actually do to run your queries.
	This is one of the best ways to improve the performance of your
	database-driven application.</p>
</div>

<div id="demoZone">
	<h2>Select names of project managers whose projects are over budget by at least $20</h2>
<?php 
QApplication::$Database[1]->EnableProfiling();
$objPersonArray = Person::QueryArray(QQ::IsNotNull(QQ::Virtual('over_budget_projects', QQ::SubSql("SELECT COUNT(*)\n\t\t\t\t\t\t\tFROM project\n\t\t\t\t\t\t\tWHERE (spent - budget > 20)\n\t\t\t\t\t\t\t\tAND manager_person_id={1}\n\t\t\t\t\t\t\tGROUP BY manager_person_id", QQN::Person()->Id))), QQ::Clause(QQ::OrderBy(QQ::Virtual('over_budget_projects'), false), QQ::Expand(QQ::Virtual('over_budget_projects'))));
foreach ($objPersonArray as $objPerson) {
    _p($objPerson->FirstName . ' ' . $objPerson->LastName . ': ' . $objPerson->GetVirtualAttribute("over_budget_projects") . " project(s) over budget");
    _p('<br/>', false);
}
?>
	<p><?php 
QApplication::$Database[1]->OutputProfiling();
?>
</p>
</div>

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

    public function dtgProjects_Bind()
    {
        // Get Total Count b/c of Pagination
        $this->dtgProjects->TotalItemCount = Project::CountAll();
        $objClauses = array();
        if ($objClause = $this->dtgProjects->OrderByClause) {
            $objClauses[] = $objClause;
        }
        if ($objClause = $this->dtgProjects->LimitClause) {
            $objClauses[] = $objClause;
        }
        // Create a virtual attribute that lets us know if this Project is related to ACME
        $objClauses[] = QQ::Expand(QQ::Virtual('assn_item', QQ::SubSql('select 
							project_id
					 	from 
					 		related_project_assn
					 	where 
							child_project_id = {1} 
							 and project_id = 1', QQN::Project()->Id)));
        $this->dtgProjects->DataSource = Project::LoadAll($objClauses);
    }

 public function testSubSql()
 {
     $objProject = Project::QuerySingle(QQ::All(), QQ::Clause(QQ::Count(QQ::SubSql('DISTINCT {1}', QQN::Project()->ManagerPersonId), "manager_count")));
     $this->assertEquals(3, $objProject->GetVirtualAttribute("manager_count"), "Project manager count is 3");
 }

 protected function dtrText_Conditions($blnReset = false)
 {
     $this->arrConditions = array(QQ::AndCondition(QQ::Equal(QQN::NarroContextInfo()->LanguageId, QApplication::GetLanguageId()), QQ::Equal(QQN::NarroContextInfo()->Context->Active, true), QQ::Equal(QQN::NarroContextInfo()->Context->File->Active, true)));
     if ($blnReset) {
         $this->intMaxRowCount = 0;
     }
     $this->arrClauses = array(QQ::Expand(QQN::NarroContextInfo()->Context), QQ::Expand(QQN::NarroContextInfo()->Context->Text), QQ::Expand(QQN::NarroContextInfo()->Context->File), QQ::Expand(QQN::NarroContextInfo()->Context->Project), QQ::Expand(QQN::NarroContextInfo()->ValidSuggestion));
     if ($this->lstProject->SelectedValue > 0) {
         $this->arrConditions[] = QQ::Equal(QQN::NarroContextInfo()->Context->ProjectId, $this->lstProject->SelectedValue);
     }
     switch ($this->lstFilter->SelectedValue) {
         case self::SHOW_NOT_TRANSLATED:
             $this->arrConditions[] = QQ::Equal(QQN::NarroContextInfo()->HasSuggestions, false);
             break;
         case self::SHOW_NOT_APPROVED:
             $this->arrConditions[] = QQ::AndCondition(QQ::IsNull(QQN::NarroContextInfo()->ValidSuggestionId), QQ::Equal(QQN::NarroContextInfo()->HasSuggestions, true));
             break;
         case self::SHOW_APPROVED:
             $this->arrConditions[] = QQ::IsNotNull(QQN::NarroContextInfo()->ValidSuggestionId);
             break;
         case self::SHOW_APPROVED_AND_NOT_APPROVED:
             $this->arrConditions[] = QQ::Equal(QQN::NarroContextInfo()->HasSuggestions, true);
             break;
         case self::SHOW_NOT_APPROVED_AND_NOT_TRANSLATED:
             $this->arrConditions[] = QQ::IsNull(QQN::NarroContextInfo()->ValidSuggestionId);
             break;
         case self::SHOW_NOT_APPROVED_AND_WITHOUT_VOTES:
             $this->arrConditions[] = QQ::Equal(QQ::SubSql('SELECT COUNT(*) FROM narro_suggestion_vote, narro_suggestion WHERE narro_suggestion_vote.suggestion_id=narro_suggestion.suggestion_id AND narro_suggestion.text_id={1}', QQN::NarroContextInfo()->Context->TextId), 0);
             break;
         case self::SHOW_NOT_APPROVED_AND_WITH_VOTES:
             $this->arrConditions[] = QQ::NotEqual(QQ::SubSql('SELECT COUNT(*) FROM narro_suggestion_vote, narro_suggestion WHERE narro_suggestion_vote.suggestion_id=narro_suggestion.suggestion_id AND narro_suggestion.text_id={1}', QQN::NarroContextInfo()->Context->TextId), 0);
             break;
         case self::SHOW_IDENTICAL_APPROVED:
             $this->arrClauses[] = QQ::ExpandAsArray(QQN::NarroContextInfo()->Context->Text->NarroSuggestionAsText);
             $this->arrClauses[] = QQ::Distinct();
             $this->arrConditions[] = QQ::AndCondition(QQ::Equal(QQN::NarroContextInfo()->Context->Text->TextValueMd5, QQN::NarroContextInfo()->Context->Text->NarroSuggestionAsText->SuggestionValueMd5), QQ::Equal(QQN::NarroContextInfo()->ValidSuggestionId, QQN::NarroContextInfo()->Context->Text->NarroSuggestionAsText->SuggestionId), QQ::Equal(QQN::NarroContextInfo()->Context->Text->NarroSuggestionAsText->LanguageId, QQN::NarroContextInfo()->LanguageId));
             break;
         case self::SHOW_IDENTICAL:
             $this->arrClauses[] = QQ::ExpandAsArray(QQN::NarroContextInfo()->Context->Text->NarroSuggestionAsText);
             $this->arrClauses[] = QQ::Distinct();
             $this->arrConditions[] = QQ::AndCondition(QQ::Equal(QQN::NarroContextInfo()->Context->Text->TextValueMd5, QQN::NarroContextInfo()->Context->Text->NarroSuggestionAsText->SuggestionValueMd5), QQ::Equal(QQN::NarroContextInfo()->Context->Text->NarroSuggestionAsText->LanguageId, QQN::NarroContextInfo()->LanguageId));
             break;
         case self::SHOW_ALL:
         default:
     }
     if ($this->txtFile->Text != t('all files') && $this->txtFile->Text != '') {
         if (preg_match("/^'.+'\$/", $this->txtFile->Text)) {
             $this->arrConditions[] = QQ::Equal(QQN::NarroContextInfo()->Context->File->FilePath, substr($this->txtFile->Text, 1, -1));
         } elseif (preg_match('/^".+"$/', $this->txtFile->Text)) {
             $this->arrConditions[] = QQ::Equal(QQN::NarroContextInfo()->Context->File->FilePath, substr($this->txtFile->Text, 1, -1));
         } else {
             $this->arrConditions[] = QQ::Like(QQN::NarroContextInfo()->Context->File->FilePath, '%' . $this->txtFile->Text . '%');
         }
     }
     if ($this->txtSearch->Text) {
         if (preg_match("/^'.+'\$/", $this->txtSearch->Text)) {
             $strLikeSearch = substr($this->txtSearch->Text, 1, -1);
         } elseif (preg_match('/^".+"$/', $this->txtSearch->Text)) {
             $strLikeSearch = substr($this->txtSearch->Text, 1, -1);
         } else {
             $strLikeSearch = '%' . $this->txtSearch->Text . '%';
         }
         switch ($this->lstSearchIn->SelectedValue) {
             case self::SEARCH_IN_TEXTS:
                 $this->arrConditions[] = QQ::Like(QQN::NarroContextInfo()->Context->Text->TextValue, $strLikeSearch);
                 break;
             case self::SEARCH_IN_TRANSLATIONS:
                 $this->arrClauses[] = QQ::ExpandAsArray(QQN::NarroContextInfo()->Context->Text->NarroSuggestionAsText);
                 $this->arrConditions[] = QQ::AndCondition(QQ::Like(QQN::NarroContextInfo()->Context->Text->NarroSuggestionAsText->SuggestionValue, $strLikeSearch), QQ::Equal(QQN::NarroContextInfo()->Context->Text->NarroSuggestionAsText->LanguageId, QApplication::GetLanguageId()));
                 break;
             case self::SEARCH_IN_AUTHORS:
                 $this->arrClauses[] = QQ::ExpandAsArray(QQN::NarroContextInfo()->Context->Text->NarroSuggestionAsText);
                 $this->arrConditions[] = QQ::AndCondition(QQ::Like(QQN::NarroContextInfo()->Context->Text->NarroSuggestionAsText->User->RealName, $strLikeSearch), QQ::Equal(QQN::NarroContextInfo()->Context->Text->NarroSuggestionAsText->LanguageId, QApplication::GetLanguageId()));
                 break;
             case self::SEARCH_IN_CONTEXTS:
                 $this->arrConditions[] = QQ::OrCondition(QQ::Like(QQN::NarroContextInfo()->Context->Context, $strLikeSearch), QQ::Like(QQN::NarroContextInfo()->Context->Comment, $strLikeSearch));
                 break;
             case self::SEARCH_IN_ALL:
             default:
                 $this->arrClauses[] = QQ::Distinct();
                 $this->arrConditions[] = QQ::OrCondition(QQ::Like(QQN::NarroContextInfo()->Context->Text->TextValue, $strLikeSearch), QQ::AndCondition(QQ::Like(QQN::NarroContextInfo()->Context->Text->NarroSuggestionAsText->SuggestionValue, $strLikeSearch), QQ::Equal(QQN::NarroContextInfo()->Context->Text->NarroSuggestionAsText->LanguageId, QApplication::GetLanguageId())), QQ::AndCondition(QQ::Like(QQN::NarroContextInfo()->Context->Text->NarroSuggestionAsText->User->RealName, $strLikeSearch), QQ::Equal(QQN::NarroContextInfo()->Context->Text->NarroSuggestionAsText->LanguageId, QApplication::GetLanguageId())), QQ::Like(QQN::NarroContextInfo()->Context->Context, $strLikeSearch), QQ::Like(QQN::NarroContextInfo()->Context->Comment, $strLikeSearch));
         }
     }
     switch ($this->lstSort->SelectedValue) {
         case self::SORT_TEXT:
             $this->arrClauses[] = QQ::OrderBy(QQN::NarroContextInfo()->Context->Text->TextValue, $this->lstSortDir->SelectedValue);
             break;
         case self::SORT_TEXT_LENGTH:
             $this->arrClauses[] = QQ::OrderBy(QQN::NarroContextInfo()->Context->Text->TextWordCount, $this->lstSortDir->SelectedValue);
             break;
         case self::SORT_TRANSLATION:
             $this->arrClauses[] = QQ::OrderBy(QQN::NarroContextInfo()->ValidSuggestion->SuggestionValue, $this->lstSortDir->SelectedValue);
             break;
         case self::SORT_TRANSLATION_DATE:
             $this->arrClauses[] = QQ::OrderBy(QQN::NarroContextInfo()->Modified, $this->lstSortDir->SelectedValue);
             break;
     }
 }

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