public function testSelect() { $objTest = new TypeTest(); $objTest->TestFloat = 2.0; $objTest->Save(); $objTest2 = new TypeTest(); $objTest2->TestFloat = 3.0; $objTest2->Save(); $objResArray = TypeTest::QueryArray(QQ::GreaterThan(QQ::Virtual('power2', QQ::Power(QQN::TypeTest()->TestFloat, 2.0)), 1.0), QQ::Clause(QQ::OrderBy(QQ::Virtual('power2')), QQ::Expand(QQ::Virtual('power2')), QQ::Select(QQ::Virtual('power2')))); $this->assertEquals(2, count($objResArray)); if (2 == count($objResArray)) { $objRes = $objResArray[0]; $this->assertNotNull($objRes); if ($objRes) { $this->assertNull($objRes->TestFloat); $this->assertEquals(4.0, $objRes->GetVirtualAttribute('power2')); } $objRes = $objResArray[1]; $this->assertNotNull($objRes); if ($objRes) { $this->assertNull($objRes->TestFloat); $this->assertEquals(9.0, $objRes->GetVirtualAttribute('power2')); } } $objTest->Delete(); $objTest2->Delete(); }
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); }
<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 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); }
<?php QApplication::$Database[1]->EnableProfiling(); $objPersonArray = Person::QueryArray(QQ::GreaterThan(QQ::Virtual('diff', QQ::MathOp('+', QQN::Person()->ProjectAsManager->Spent, QQ::Neg(QQN::Person()->ProjectAsManager->Budget))), 20), QQ::Clause(QQ::OrderBy(QQ::Virtual('diff'), 'DESC'), QQ::Expand(QQ::Virtual('diff')), QQ::Select(array(QQ::Virtual('diff'), QQN::Person()->FirstName, QQN::Person()->LastName)))); foreach ($objPersonArray as $objPerson) { _p($objPerson->FirstName . ' ' . $objPerson->LastName) . ' : ' . $objPerson->GetVirtualAttribute('diff'); _p('<br/>', false); } ?> <p><?php QApplication::$Database[1]->OutputProfiling(); ?> </p> <h2>SQL Function Example</h2> <p>Use the QQ::Abs and QQ::Sub functions to retrieve projects both over-budget and under-budget by $20.</p> <?php QApplication::$Database[1]->EnableProfiling(); $objPersonArray = Person::QueryArray(QQ::GreaterThan(QQ::Virtual('absdiff', QQ::Abs(QQ::Sub(QQN::Person()->ProjectAsManager->Spent, QQN::Person()->ProjectAsManager->Budget))), 20), QQ::Clause(QQ::OrderBy(QQ::Virtual('absdiff'), 'DESC'), QQ::Expand(QQ::Virtual('absdiff')), QQ::Select(array(QQ::Virtual('absdiff'), QQN::Person()->FirstName, QQN::Person()->LastName)))); foreach ($objPersonArray as $objPerson) { _p($objPerson->FirstName . ' ' . $objPerson->LastName) . ' : ' . $objPerson->GetVirtualAttribute('diff'); _p('<br/>', false); } ?> <p><?php QApplication::$Database[1]->OutputProfiling(); ?> </p> </div> <?php require '../includes/footer.inc.php';
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();'); }
/** * Tests to ensure the example to work */ public function testExample() { $objPersonArray = Person::QueryArray(QQ::GreaterThan(QQ::Sub(QQN::Person()->ProjectAsManager->Spent, QQN::Person()->ProjectAsManager->Budget), 20)); $this->assertGreaterThan(0, count($objPersonArray)); foreach ($objPersonArray as $objPerson) { $this->assertNotNull($objPerson->FirstName); $this->assertNotNull($objPerson->LastName); } $objPersonArray = Person::QueryArray(QQ::GreaterThan(QQ::Virtual('diff', QQ::Sub(QQN::Person()->ProjectAsManager->Spent, QQN::Person()->ProjectAsManager->Budget)), 20), QQ::Clause(QQ::OrderBy(QQ::Virtual('diff'), 'DESC'), QQ::Expand(QQ::Virtual('diff')))); $this->assertGreaterThan(0, count($objPersonArray)); foreach ($objPersonArray as $objPerson) { $this->assertNotNull($objPerson->FirstName); $this->assertNotNull($objPerson->LastName); $this->assertNotNull($objPerson->GetVirtualAttribute('diff')); } $objPersonArray = Person::QueryArray(QQ::GreaterThan(QQ::Virtual('diff', QQ::MathOp('-', QQN::Person()->ProjectAsManager->Spent, QQN::Person()->ProjectAsManager->Budget)), 20), QQ::Clause(QQ::OrderBy(QQ::Virtual('diff'), 'DESC'), QQ::Expand(QQ::Virtual('diff')), QQ::Select(array(QQ::Virtual('diff'), QQN::Person()->FirstName, QQN::Person()->LastName)))); $this->assertGreaterThan(0, count($objPersonArray)); foreach ($objPersonArray as $objPerson) { $this->assertNotNull($objPerson->FirstName); $this->assertNotNull($objPerson->LastName); $this->assertNotNull($objPerson->GetVirtualAttribute('diff')); } $objPersonArray = Person::QueryArray(QQ::GreaterThan(QQ::Virtual('absdiff', QQ::Abs(QQ::Sub(QQN::Person()->ProjectAsManager->Spent, QQN::Person()->ProjectAsManager->Budget))), 20), QQ::Clause(QQ::OrderBy(QQ::Virtual('absdiff'), 'DESC'), QQ::Expand(QQ::Virtual('absdiff')), QQ::Select(array(QQ::Virtual('absdiff'), QQN::Person()->FirstName, QQN::Person()->LastName)))); $this->assertGreaterThan(0, count($objPersonArray)); foreach ($objPersonArray as $objPerson) { $this->assertNotNull($objPerson->FirstName); $this->assertNotNull($objPerson->LastName); $this->assertNotNull($objPerson->GetVirtualAttribute('absdiff')); } }
public function __construct($strName, $strAttribute = null) { parent::__construct($strName); if ($strAttribute) { $this->strAttribute = $strAttribute; } $this->OrderByClause = QQ::OrderBy(QQ::Virtual($strAttribute)); $this->ReverseOrderByClause = QQ::OrderBy(QQ::Virtual($strAttribute), false); }