PHP Project::QueryArray方法代码示例

 /**
  * This constructor will only be executed once - afterwards,
  * the state of the control will be stored into the $_SESSION
  * and, on future loads, populated from the session state.
  */
 public function __construct($objParentObject)
 {
     parent::__construct($objParentObject);
     $projects = Project::QueryArray(QQ::All(), QQ::OrderBy(QQN::Project()->Name));
     foreach ($projects as $project) {
         $this->AddItem($project->Name, $project->Id);
     }
 }

 protected function dtgProjects_Bind()
 {
     // We must first let the datagrid know how many total items there are
     $this->dtgProjects->TotalItemCount = Project::QueryCount($this->dtgProjects->Conditions);
     // Next, we must be sure to load the data source, passing in the datagrid's
     // limit info into our loadall method.
     $this->dtgProjects->DataSource = Project::QueryArray($this->dtgProjects->Conditions, QQ::Clause($this->dtgProjects->OrderByClause, $this->dtgProjects->LimitClause));
 }

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

 /**
  * This constructor will only be executed once - afterwards,
  * the state of the control will be stored into the $_SESSION
  * and, on future loads, populated from the session state.
  */
 public function __construct($objParentObject, $strControlId)
 {
     parent::__construct($objParentObject, $strControlId);
     $projects = Project::QueryArray(QQ::All(), QQ::OrderBy(QQN::Project()->Name));
     foreach ($projects as $project) {
         $this->AddItem($project->Name, $project->Id);
     }
     // Reset the status of the parent form's label to indicate
     // that the query was actually run
     $objParentObject->lblStatus->Text = "The query was executed";
 }

 public function testDbTypeCasting()
 {
     $dt1 = new QDateTime('Jan 15 2006');
     $dt2 = new QDateTime('Mar 15 2006');
     $cond = QQ::Between(QQN::Project()->StartDate, $dt1, $dt2);
     $a = Project::QueryArray($cond);
     $this->assertEquals(2, count($a), "Between 2 QDateTime types works");
     $cond = QQ::Between(QQN::Project()->Budget, 2000, 3000);
     $a = Project::QueryArray($cond);
     $this->assertEquals(1, count($a), "Between 2 int types works");
     $cond = QQ::Between(QQN::Project()->Name, 'A', 'C');
     $a = Project::QueryArray($cond);
     $this->assertEquals(3, count($a), "Between 2 string types works");
 }

 /**
  * Add the items to the project list.
  */
 public function lstProjects_Bind()
 {
     $clauses[] = QQ::ExpandAsArray(QQN::Project()->PersonAsTeamMember);
     $objProjects = Project::QueryArray(QQ::All(), $clauses);
     foreach ($objProjects as $objProject) {
         $item = new QHListItem($objProject->Name);
         $item->Tag = 'ol';
         $item->GetSubTagStyler()->OrderedListType = QOrderedListType::LowercaseRoman;
         foreach ($objProject->_PersonAsTeamMemberArray as $objPerson) {
             /****
              * Here we add a sub-item to each item before adding the item to the main list.
              */
             $item->AddItem($objPerson->FirstName . ' ' . $objPerson->LastName);
         }
         $this->lstProjects->AddItem($item);
     }
 }

    _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';

	<h3>Custom Load Query: Select all Projects with Budgets over $5000, ordered by Descending Budget</h3>
<?php 
// Custom Load Queries must have a resultset that returns all the fields of the
// table which corresponds to the ORM class you want to instantiate
// Note that because the InstantiateDb* methods in your ORM classes are code generated
// it is actually safe to use "SELECT *" for your Custom Load Queries.
$strQuery = 'SELECT project.* FROM project WHERE budget > 5000 ORDER BY budget DESC';
// perform the query
$objDbResult = $objDatabase->Query($strQuery);
// Use the Project::InstantiateDbResult on the $objDbResult to get an array of Project objects
$objProjectArray = Project::InstantiateDbResult($objDbResult);
// Iterate through the Project Array as you would any other ORM object
foreach ($objProjectArray as $objProject) {
    _p($objProject->Name . ' has a budget of $' . $objProject->Budget);
    _p('<br/>', false);
}
?>



	<h3>Qcodo Query: Select all Projects which have a Budget over $5000 and under $10000, ordered by Descending Budget</h3>
<?php 
// Perform the Query using Project::QueryArray, which will return an array of Project objects
// given a QQ Condition, and any optional QQ Clauses.
$objProjectArray = Project::QueryArray(QQ::AndCondition(QQ::GreaterThan(QQN::Project()->Budget, 5000), QQ::LessThan(QQN::Project()->Budget, 10000)), QQ::Clause(QQ::OrderBy(QQN::Project()->Budget, false)));
// Iterate through the Project Array like last time
foreach ($objProjectArray as $objProject) {
    _p($objProject->Name . ' has a budget of $' . $objProject->Budget);
    _p('<br/>', false);
}
require '../includes/footer.inc.php';

 public static function LoadArrayByBudgetMinimum($fltBudgetMinimum, $objOptionalClauses = null)
 {
     return Project::QueryArray(QQ::GreaterOrEqual(QQN::Project()->Budget, $fltBudgetMinimum), $objOptionalClauses);
 }

	<p>In a slightly more complex example 2 below, we are looking for all projects that
	are associated with two other projects (each is specified by name). We use the
	same technique with <b>QQ::Alias()</b> as in example 1, except that we now
	mix it in with relationships expanded to other tables.</p>
</div>

<div id="demoZone">
	<h2>Example 1: Project members whose are in both project 1 and 2</h2>
<?php 
QApplication::$Database[1]->EnableProfiling();
$objPersonArray = Person::QueryArray(QQ::AndCondition(QQ::Equal(QQ::Alias(QQN::Person()->ProjectAsTeamMember, 'pm1')->ProjectId, 1), QQ::Equal(QQ::Alias(QQN::Person()->ProjectAsTeamMember, 'pm2')->ProjectId, 2)));
foreach ($objPersonArray as $objPerson) {
    _p($objPerson->FirstName . ' ' . $objPerson->LastName);
    _p('<br/>', false);
}
?>

	<h2>Example 2: Projects that are related to both 'Blueman Industrial Site Architecture' and 'ACME Payment System' projects</h2>
<?php 
$objProjectArray = Project::QueryArray(QQ::AndCondition(QQ::Equal(QQ::Alias(QQN::Project()->ProjectAsRelated, 'related1')->Project->Name, 'Blueman Industrial Site Architecture'), QQ::Equal(QQ::Alias(QQN::Project()->ProjectAsRelated, 'related2')->Project->Name, 'ACME Payment System')));
foreach ($objProjectArray as $objProject) {
    _p($objProject->Name . " (" . $objProject->Description . ")");
    _p('<br/>', false);
}
QApplication::$Database[1]->OutputProfiling();
?>
</div>

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

 public function testConditionalExpansionAssociation()
 {
     // Conditional expansion on association nodes really can only work with the PK of the join.
     // Get all projects, and also expand on related projects if the id is 1
     $a = Project::QueryArray(QQ::All(), [QQ::ExpandAsArray(QQN::Project()->ParentProjectAsRelated, QQ::Equal(QQN::Project()->ParentProjectAsRelated->ProjectId, 1)), QQ::ExpandAsArray(QQN::Project()->ProjectAsRelated, QQ::Equal(QQN::Project()->ProjectAsRelated->Project->Id, 1)), QQ::OrderBy(QQN::Project()->Id)]);
     $this->assertEquals(1, $a[2]->_ParentProjectAsRelatedArray[0]->Id);
 }

 public function btnGo_Click($strFormId, $strControlId, $strParameter)
 {
     //get a list of project ids that have had their status changed
     $changedIds = $this->colProjectSelected->GetChangedIds();
     //load all the changed project objects at once so we can avoid multiple DB hits
     $temp = Project::QueryArray(QQ::In(QQN::Project()->Id, array_keys($changedIds)));
     //Put them in an associated list so we can find the needed ones easily later
     $changedItems = array();
     foreach ($temp as $item) {
         $changedItems[$item->Id] = $item;
     }
     foreach ($changedIds as $id => $blnSelected) {
         //look up the appropriate item using the handily indexed array we built earlier
         $item = $changedItems[$id];
         if ($blnSelected) {
             //Associate this Project
             QApplication::DisplayAlert('Associating ' . $item->Name);
         } else {
             //Unassociate this Project
             QApplication::DisplayAlert('Unassociating ' . $item->Name);
         }
     }
 }

$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';

 protected function Go_Click()
 {
     QApplication::$blnLocalCache = false;
     $timeNoCache = -microtime(true);
     $a = Person::LoadAll();
     // noncached loads
     $timeNoCache += microtime(true);
     QApplication::$blnLocalCache = true;
     $timeLoad1Cached = -microtime(true);
     $a = Person::LoadAll();
     // noncached loads
     $timeLoad1Cached += microtime(true);
     $timeLoad2Cached = -microtime(true);
     $a = Person::LoadAll();
     // cached loads
     $timeLoad2Cached += microtime(true);
     QApplication::$blnLocalCache = new QCacheProviderLocalMemory(array());
     $timeLoad3Cached = -microtime(true);
     $a = Person::LoadAll();
     // noncached loads
     $timeLoad3Cached += microtime(true);
     $timeLoad4Cached = -microtime(true);
     $a = Person::LoadAll();
     // cached loads
     $timeLoad4Cached += microtime(true);
     $this->pnlTiny->Text = sprintf("Load No Cache: %2.1f%% \n", 100 * $timeNoCache / $timeNoCache) . sprintf("Populate Cache: %2.1f%% \n", 100 * $timeLoad1Cached / $timeNoCache) . sprintf("Load With Cache: %2.1f%% \n", 100 * $timeLoad2Cached / $timeNoCache) . sprintf("Populate LocalCacheProvider: %2.1f%% \n", 100 * $timeLoad3Cached / $timeNoCache) . sprintf("Load LocalCacheProvider: %2.1f%% \n", 100 * $timeLoad4Cached / $timeNoCache);
     $cond = QQ::Equal(QQN::Project()->ProjectStatusTypeId, ProjectStatusType::Open);
     $clauses[] = QQ::Expand(QQN::Project()->ManagerPerson);
     Project::ClearCache();
     Person::ClearCache();
     QApplication::$blnLocalCache = false;
     $timeNoCache = -microtime(true);
     $a = Project::QueryArray($cond, $clauses);
     // noncached loads
     $timeNoCache += microtime(true);
     QApplication::$blnLocalCache = true;
     $timeLoad1Cached = -microtime(true);
     $a = Project::QueryArray($cond, $clauses);
     // noncached loads
     $timeLoad1Cached += microtime(true);
     $timeLoad2Cached = -microtime(true);
     $a = Project::QueryArray($cond, $clauses);
     // cached loads
     $timeLoad2Cached += microtime(true);
     QApplication::$blnLocalCache = new QCacheProviderLocalMemory(array());
     $timeLoad3Cached = -microtime(true);
     $a = Project::QueryArray($cond, $clauses);
     // noncached loads
     $timeLoad3Cached += microtime(true);
     $timeLoad4Cached = -microtime(true);
     $a = Project::QueryArray($cond, $clauses);
     // cached loads
     $timeLoad4Cached += microtime(true);
     $this->pnlBig->Text = sprintf("Load No Cache: %2.1f%% \n", 100 * $timeNoCache / $timeNoCache) . sprintf("Populate Cache: %2.1f%% \n", 100 * $timeLoad1Cached / $timeNoCache) . sprintf("Load With Cache: %2.1f%% \n", 100 * $timeLoad2Cached / $timeNoCache) . sprintf("Populate LocalCacheProvider: %2.1f%% \n", 100 * $timeLoad3Cached / $timeNoCache) . sprintf("Load LocalCacheProvider: %2.1f%% \n", 100 * $timeLoad4Cached / $timeNoCache);
 }