public function testSelectSubsetInExpandAsArray()
{
$objPersonArray = Person::LoadAll(QQ::Clause(QQ::Select(QQN::Person()->FirstName), QQ::ExpandAsArray(QQN::Person()->Address, QQ::Select(QQN::Person()->Address->Street, QQN::Person()->Address->City)), QQ::ExpandAsArray(QQN::Person()->ProjectAsManager, QQ::Select(QQN::Person()->ProjectAsManager->StartDate)), QQ::ExpandAsArray(QQN::Person()->ProjectAsManager->Milestone, QQ::Select(QQN::Person()->ProjectAsManager->Milestone->Name))));
foreach ($objPersonArray as $objPerson) {
$this->assertNull($objPerson->LastName, "LastName should be null, since it was not selected");
$this->assertNotNull($objPerson->Id, "Id should not be null since it's always added to the select list");
if (sizeof($objPerson->_AddressArray) > 0) {
foreach ($objPerson->_AddressArray as $objAddress) {
$this->assertNotNull($objAddress->Id, "Address->Id should not be null since it's always added to the select list");
$this->assertNull($objAddress->PersonId, "Address->PersonId should be null, since it was not selected");
}
}
if (sizeof($objPerson->_ProjectAsManagerArray) > 0) {
foreach ($objPerson->_ProjectAsManagerArray as $objProject) {
$this->assertNotNull($objProject->Id, "Project->Id should not be null since it's always added to the select list");
$this->assertNull($objProject->Name, "Project->Name should be null, since it was not selected");
if (sizeof($objProject->_MilestoneArray) > 0) {
foreach ($objProject->_MilestoneArray as $objMilestone) {
$this->assertNotNull($objMilestone->Id, "Milestone->Id should not be null since it's always added to the select list");
$this->assertNull($objMilestone->ProjectId, "Milestone->ProjectId should be null, since it was not selected");
}
}
}
}
}
}
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 testQuerySelectSubsetSkipPK()
{
$objSelect = QQ::Select(QQN::Person()->FirstName);
$objSelect->SetSkipPrimaryKey(true);
$objPersonArray = Person::LoadAll($objSelect);
foreach ($objPersonArray as $objPerson) {
$this->assertNull($objPerson->LastName, "LastName should be null, since it was not selected");
$this->assertNull($objPerson->Id, "Id should be null since SkipPrimaryKey is set on the Select object");
}
}
public function GetColumnAliasHelper(QQueryBuilder $objBuilder, $blnExpandSelection, QQSelect $objSelect = null)
{
// Are we at the Parent Node?
if (is_null($this->objParentNode)) {
// Yep -- Simply return the Parent Node Name
return $this->strName;
} else {
// No -- First get the Parent Alias
$strParentAlias = $this->objParentNode->GetColumnAliasHelper($objBuilder, $blnExpandSelection, $objSelect ? QQ::Select() : null);
// Next, Join the Appropriate Table
$objBuilder->AddJoinItem($this->strTableName, $strParentAlias . '__' . $this->strAlias, $strParentAlias, $this->objParentNode->_PrimaryKey, $this->strPrimaryKey);
// Next, Expand the Selection Fields for this Table (if applicable)
// TODO: If/when we add assn-based attributes, possibly add selectionfields addition here?
// if ($blnExpandSelection) {
// call_user_func(array($this->strClassName, 'GetSelectFields'), $objBuilder, $strParentAlias . '__' . $this->strName);
// }
// Return the Parent Alias
return $strParentAlias . '__' . $this->strAlias;
}
}
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 testHaving()
{
$objItems = Project::QueryArray(QQ::All(), QQ::Clause(QQ::Select(QQN::Project()->Id, QQN::Project()->Name), 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->assertEquals(2, sizeof($objItems), "2 projects found");
$this->assertEquals("State College HR System", $objItems[0]->Name, "Project " . $objItems[0]->Name . " found");
$this->assertEquals(6, $objItems[0]->GetVirtualAttribute('team_member_count'), "6 team members found for project " . $objItems[0]->Name);
}
<?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';
<h2>Get the last names of all the people, and the amount spent on the project they manage (if any), for Projects that
have 'ACME' or 'HR' in it. Sort the result by Last Name, then First Name</h2>
<p><i>Notice how some people may be listed twice, if they manage more than one project.</i></p>
<ul>
<?php
$objPersonArray = Person::QueryArray(QQ::OrCondition(QQ::Like(QQN::Person()->ProjectAsManager->Name, '%ACME%'), QQ::Like(QQN::Person()->ProjectAsManager->Name, '%HR%')), QQ::Clause(QQ::Select(QQN::Person()->LastName), QQ::Expand(QQN::Person()->ProjectAsManager, null, QQ::Select(QQN::Person()->ProjectAsManager->Spent)), QQ::OrderBy(QQN::Person()->LastName, QQN::Person()->FirstName)));
foreach ($objPersonArray as $objPerson) {
printf("<li>%s's project spent \$%0.2f</li>", QApplication::HtmlEntities($objPerson->LastName), QApplication::HtmlEntities($objPerson->_ProjectAsManager->Spent));
}
?>
</ul>
<h3>Projects and Addresses for each Person</h3>
<ul>
<?php
$people = Person::LoadAll(QQ::Clause(QQ::Select(QQN::Person()->FirstName), QQ::ExpandAsArray(QQN::Person()->Address, QQ::Select(QQN::Person()->Address->Street, QQN::Person()->Address->City)), QQ::ExpandAsArray(QQN::Person()->ProjectAsManager, QQ::Select(QQN::Person()->ProjectAsManager->StartDate)), QQ::ExpandAsArray(QQN::Person()->ProjectAsManager->Milestone, QQ::Select(QQN::Person()->ProjectAsManager->Milestone->Name))));
foreach ($people as $person) {
echo "<li><b>" . $person->FirstName . "</b><br />";
assert(is_null($person->LastName));
echo "Addresses: ";
if (sizeof($person->_AddressArray) == 0) {
echo "none";
} else {
foreach ($person->_AddressArray as $address) {
echo $address->Street . ', ' . $address->City . "; ";
assert(is_null($address->PersonId));
}
}
echo "<br />";
echo "Projects where this person is a project manager: ";
if (sizeof($person->_ProjectAsManagerArray) == 0) {
public function testQuerySelectSubset()
{
$objPersonArray = Person::LoadAll(QQ::Select(QQN::Person()->FirstName));
foreach ($objPersonArray as $objPerson) {
$this->assertNull($objPerson->LastName, "LastName should be null, since it was not selected");
$this->assertNotNull($objPerson->Id, "Id should not be null since it's always added to the select list");
}
}
<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);
}
?>
<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);
}
/**
* 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 testSelectSubsetInExpand()
{
Project::ClearCache();
Person::ClearCache();
Milestone::ClearCache();
$objPersonArray = Person::QueryArray(QQ::OrCondition(QQ::Like(QQN::Person()->ProjectAsManager->Name, '%ACME%'), QQ::Like(QQN::Person()->ProjectAsManager->Name, '%HR%')), QQ::Clause(QQ::Select(QQN::Person()->LastName), QQ::Expand(QQN::Person()->ProjectAsManager, null, QQ::Select(QQN::Person()->ProjectAsManager->Spent)), QQ::OrderBy(QQN::Person()->LastName, QQN::Person()->FirstName)));
foreach ($objPersonArray as $objPerson) {
$this->assertNull($objPerson->FirstName, "FirstName should be null, since it was not selected");
$this->assertNotNull($objPerson->Id, "Id should not be null since it's always added to the select list");
$this->assertNotNull($objPerson->_ProjectAsManager->Id, "ProjectAsManager->Id should not be null since id's are always added to the select list");
$this->assertNull($objPerson->_ProjectAsManager->Name, "ProjectAsManager->Name should be null since it was not selected");
}
// generate full objects to load into cache
$objPersonArray = Person::QueryArray(QQ::OrCondition(QQ::Like(QQN::Person()->ProjectAsManager->Name, '%ACME%'), QQ::Like(QQN::Person()->ProjectAsManager->Name, '%HR%')), QQ::Clause(QQ::Expand(QQN::Person()->ProjectAsManager), QQ::OrderBy(QQN::Person()->LastName, QQN::Person()->FirstName)));
$objPersonArray = Person::QueryArray(QQ::OrCondition(QQ::Like(QQN::Person()->ProjectAsManager->Name, '%ACME%'), QQ::Like(QQN::Person()->ProjectAsManager->Name, '%HR%')), QQ::Clause(QQ::Select(QQN::Person()->LastName), QQ::Expand(QQN::Person()->ProjectAsManager, null, QQ::Select(QQN::Person()->ProjectAsManager->Spent)), QQ::OrderBy(QQN::Person()->LastName, QQN::Person()->FirstName)));
foreach ($objPersonArray as $objPerson) {
$this->assertNotNull($objPerson->FirstName, "FirstName should not be null, because it has been cached");
$this->assertNotNull($objPerson->Id, "Id should not be null since it's always added to the select list");
$this->assertNotNull($objPerson->_ProjectAsManager->Id, "ProjectAsManager->Id should not be null since id's are always added to the select list");
$this->assertNotNull($objPerson->_ProjectAsManager->Name, "ProjectAsManager->Name should not be null since it was cached");
}
}
