/** * 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); }
<h2>Select all People where: the first name is alphabetically "greater than" the last name</h2> <ul> <?php $objPersonArray = Person::QueryArray(QQ::GreaterThan(QQN::Person()->FirstName, QQN::Person()->LastName)); foreach ($objPersonArray as $objPerson) { _p('<li>' . $objPerson->FirstName . ' ' . $objPerson->LastName . '</li>', false); } ?> </ul> <h2>Select all Projects where: the manager's first name is alphabetically "greater than" the last name, or who's name contains "Website"</h2> <ul> <?php $objProjectArray = Project::QueryArray(QQ::OrCondition(QQ::GreaterThan(QQN::Project()->ManagerPerson->FirstName, QQN::Project()->ManagerPerson->LastName), QQ::Like(QQN::Project()->Name, '%Website%'))); foreach ($objProjectArray as $objProject) { _p(sprintf('<li>%s (managed by %s %s)</li>', $objProject->Name, $objProject->ManagerPerson->FirstName, $objProject->ManagerPerson->LastName), false); } ?> </ul> <h2>Select all Projects where: the Project ID <= 2 AND (the manager's first name is alphabetically "greater than" the last name, or who's name contains "Website")</h2> <ul> <?php $objProjectArray = Project::QueryArray(QQ::AndCondition(QQ::OrCondition(QQ::GreaterThan(QQN::Project()->ManagerPerson->FirstName, QQN::Project()->ManagerPerson->LastName), QQ::Like(QQN::Project()->Name, '%Website%')), QQ::LessOrEqual(QQN::Project()->Id, 2))); foreach ($objProjectArray as $objProject) { _p(sprintf('<li>%s (managed by %s %s)</li>', $objProject->Name, $objProject->ManagerPerson->FirstName, $objProject->ManagerPerson->LastName), 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); }
public function testAlias2() { $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'))); $this->assertEquals(1, sizeof($objProjectArray)); $this->verifyObjectPropertyHelper($objProjectArray, 'Name', 'ACME Website Redesign'); }