public function testAlias1()
{
$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)));
$this->assertEqual(sizeof($objPersonArray), 3);
$this->verifyObjectPropertyHelper($objPersonArray, 'FirstName', 'Kendall');
$this->verifyObjectPropertyHelper($objPersonArray, 'LastName', 'Wolfe');
$this->verifyObjectPropertyHelper($objPersonArray, 'LastName', 'Smith');
}
protected function dtgPersons_Bind()
{
// We must first let the datagrid know how many total items there are
$this->dtgPersons->TotalItemCount = Person::QueryCount($this->dtgPersons->Conditions);
// Next, we must be sure to load the data source, passing in the datagrid's
// limit info into our loadall method.
$this->dtgPersons->DataSource = Person::QueryArray($this->dtgPersons->Conditions, QQ::Clause($this->dtgPersons->OrderByClause, $this->dtgPersons->LimitClause));
}
public function auto_Bind($strFormId, $strControlId, $term)
{
$cond = QQ::OrCondition(QQ::Like(QQN::Person()->FirstName, '%' . $term . '%'), QQ::Like(QQN::Person()->LastName, '%' . $term . '%'));
$a = Person::QueryArray($cond);
$items = array();
foreach ($a as $obj) {
$items[] = new QListItem($obj->FirstName . ' ' . $obj->LastName, $obj->Id);
}
$this->auto2->DataSource = $items;
}
public function dtgItems_Bind()
{
$intYear = QApplication::PathInfo(0);
$dttStart = new QDateTime($intYear . '-01-01');
$dttEnd = new QDateTime($dttStart);
$dttEnd->Year += 1;
$dttStart->SetTime(null, null, null);
$dttEnd->SetTime(null, null, null);
$this->dtgItems->DataSource = Person::QueryArray(QQ::AndCondition(QQ::GreaterOrEqual(QQN::Person()->StewardshipContribution->DateCredited, $dttStart), QQ::LessThan(QQN::Person()->StewardshipContribution->DateCredited, $dttEnd), QQ::IsNull(QQN::Person()->PrimaryAddressText), QQ::IsNull(QQN::Person()->StewardshipAddressId)), QQ::Clause(QQ::Distinct(), QQ::OrderBy(QQN::Person()->LastName, QQN::Person()->FirstName)));
}
public function testAssociationTables()
{
// All People Who Are on a Project Managed by Karen Wolfe (Person ID #7)
$objPersonArray = Person::QueryArray(QQ::Equal(QQN::Person()->ProjectAsTeamMember->Project->ManagerPersonId, 7), QQ::Clause(QQ::Distinct(), QQ::OrderBy(QQN::Person()->LastName, QQN::Person()->FirstName)));
$arrNamesOnly = array();
foreach ($objPersonArray as $item) {
$arrNamesOnly[] = $item->FirstName . " " . $item->LastName;
}
$this->assertEqual($arrNamesOnly, array("Brett Carlisle", "John Doe", "Samantha Jones", "Jacob Pratt", "Kendall Public", "Ben Robinson", "Alex Smith", "Wendy Smith", "Karen Wolfe"), "List managed persons is correct");
}
public function testSelectSubsetInExpand()
{
$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");
}
}
protected function BindData()
{
if ($strFilter = $this->dtgTable->Search["search"]) {
$objCondition = QQ::OrCondition(QQ::Like(QQN::Person()->FirstName, '%' . $strFilter . '%'), QQ::Like(QQN::Person()->LastName, '%' . $strFilter . '%'));
} else {
$objCondition = QQ::All();
}
$this->dtgTable->TotalItemCount = Person::QueryCount($objCondition);
$objClauses[] = $this->dtgTable->OrderByClause;
$objClauses[] = $this->dtgTable->LimitClause;
$this->dtgTable->DataSource = Person::QueryArray($objCondition, $objClauses);
}
public function dtgMembers_Bind()
{
$objCondition = QQ::In(QQN::Person()->GroupParticipation->GroupId, $this->intGroupIdArray);
$objCondition = QQ::AndCondition($objCondition, QQ::IsNull(QQN::Person()->GroupParticipation->DateEnd));
$this->dtgMembers->TotalItemCount = Person::QueryCount($objCondition);
$objClauses = array(QQ::Distinct());
if ($objClause = $this->dtgMembers->LimitClause) {
$objClauses[] = $objClause;
}
if ($objClause = $this->dtgMembers->OrderByClause) {
$objClauses[] = $objClause;
}
$this->dtgMembers->DataSource = Person::QueryArray($objCondition, $objClauses);
}
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'));
}
protected function getList($strTerm = null, $blnHtml = false)
{
if ($strTerm) {
$cond = QQ::OrCondition(QQ::Like(QQN::Person()->FirstName, '%' . $strTerm . '%'), QQ::Like(QQN::Person()->LastName, '%' . $strTerm . '%'));
} else {
$cond = QQ::All();
}
$clauses[] = QQ::OrderBy(QQN::Person()->LastName, QQN::Person()->FirstName);
$lst = Person::QueryArray($cond, $clauses);
$a = array();
foreach ($lst as $objPerson) {
$item = new QListItem($objPerson->FirstName . ' ' . $objPerson->LastName, $objPerson->Id);
if ($blnHtml) {
$item->Label = '<em>' . $objPerson->FirstName . ' ' . $objPerson->LastName . '</em>';
}
$a[] = $item;
}
return $a;
}
public function dtgMembers_Bind()
{
$objConditions = QQ::Equal(QQN::Person()->GroupParticipation->GroupId, $this->objGroup->Id);
$this->dtgMembers->TotalItemCount = Person::QueryCount($objConditions);
if ($strName = trim($this->txtFirstName->Text)) {
$objConditions = QQ::AndCondition($objConditions, QQ::Like(QQN::Person()->FirstName, $strName . '%'));
}
if ($strName = trim($this->txtLastName->Text)) {
$objConditions = QQ::AndCondition($objConditions, QQ::Like(QQN::Person()->LastName, $strName . '%'));
}
$objClauses = array(QQ::Distinct());
if ($objClause = $this->dtgMembers->LimitClause) {
$objClauses[] = $objClause;
}
if ($objClause = $this->dtgMembers->OrderByClause) {
$objClauses[] = $objClause;
}
$this->dtgMembers->DataSource = Person::QueryArray($objConditions, $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';
/**
* Given a valid From EmailAddress, this will lookup and return a "Sender" array, a 3-item array containing:
* Login
* CommunicationListEntry
* Person[]
* that would correspond to this From EmailAddress. Note that any one of those indexes can also be null
* if there is no object corresponding to this From Email Address.
* @return mixed[]
*/
protected function CalculatePotentialSenderArray()
{
$objArrayToReturn = array();
$objArrayToReturn[] = Login::LoadByEmail($this->FromAddress);
$objArrayToReturn[] = CommunicationListEntry::LoadByEmail($this->FromAddress);
// Get all Person objects that have this as an email address
$objArrayToReturn[] = Person::QueryArray(QQ::Equal(QQN::Person()->Email->Address, $this->FromAddress), QQ::Distinct());
return $objArrayToReturn;
}
protected function dtgPerson_Bind()
{
$dtAfterValue = new QDateTime($this->dtxAfterValue->Text);
$dtBeforeValue = new QDateTime($this->dtxBeforeValue->Text);
$objConditions = QQ::All();
$objConditions = QQ::AndCondition($objConditions, QQ::GreaterOrEqual(QQN::Person()->Comment->DatePosted, $dtAfterValue));
$objConditions = QQ::AndCondition($objConditions, QQ::LessOrEqual(QQN::Person()->Comment->DatePosted, $dtBeforeValue));
$objConditions = QQ::AndCondition($objConditions, QQ::Equal(QQN::Person()->Comment->CommentCategory->Name, "Pastoral"));
$this->dtgPerson->TotalItemCount = count(Person::QueryArray($objConditions));
$objPersonArray = Person::QueryArray($objConditions, $this->dtgPerson->LimitClause);
$this->dtgPerson->DataSource = $objPersonArray;
}
public static function LoadArrayBySearch($strName, $objClauses = null)
{
$objCondition = QQ::All();
if (!$objClauses) {
$objClauses = array();
}
self::PrepareQqForSearch($strName, $objCondition, $objClauses);
return Person::QueryArray($objCondition, $objClauses);
}
<p>As you'd expect, there's a better way. Introducing conditional joins: when
you use <strong>QQ::Expand</strong>, you can specify conditions on the table with
which you want to join, and get only those values that you care about.
Remember that a <strong>QQ::Expand</strong> is always a
<a href="http://en.wikipedia.org/wiki/Join_(SQL)#Left_outer_join">left
join</a> - so if a row of a table with which you are joining does not
have a matching record, the left side of your join will still be there,
and the right side will contain nulls.</p>
</div>
<div id="demoZone">
<h2>Names of every person, plus usernames for each person if their Login is active</h2>
<ul>
<?php
QApplication::$Database[1]->EnableProfiling();
$objPersonArray = Person::QueryArray(QQ::All(), QQ::Expand(QQN::Person()->Login, QQ::Equal(QQN::Person()->Login->IsEnabled, 1)));
foreach ($objPersonArray as $objPerson) {
_p('<li>', false);
_p($objPerson->FirstName . ' ' . $objPerson->LastName . ': ');
if ($objPerson->Login) {
_p("<strong>" . $objPerson->Login->Username . "</strong>", false);
} else {
_p("none");
}
_p('</li>', false);
}
?>
</ul>
<p><?php
QApplication::$Database[1]->OutputProfiling();
?>
Redesign and the State College HR System projects. We do this simply by joining
the membership association table twice, each time with a different alias. This
allows us to create a condition that says that one associated project has an
ID of 1, while a <em>different</em> associated project also has an ID of 2.</p>
<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();
?>
public static function LoadArrayForMemberSearch($strDisplayName, $objOptionalClauses = null)
{
return Person::QueryArray(QQ::Like(QQN::Person()->DisplayName, '%' . $strDisplayName . '%'), $objOptionalClauses);
}
<div id="demoZone">
<h2>Get All People, Specifying the Project They Manage (if any), for Projects that have 'ACME' or 'HR' in it</h2>
<p><em>Notice how some people may be listed twice, if they manage more than one project.</em></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::Expand(QQN::Person()->ProjectAsManager), QQ::OrderBy(QQN::Person()->LastName, QQN::Person()->FirstName)));
foreach ($objPersonArray as $objPerson) {
printf('<li>%s %s (managing the "%s" project)</li>', QApplication::HtmlEntities($objPerson->FirstName), QApplication::HtmlEntities($objPerson->LastName), QApplication::HtmlEntities($objPerson->_ProjectAsManager->Name), false);
}
?>
</ul>
<h3>Same as above, but this time, use ExpandAsArray()</h3>
<em>Notice how each person is only listed once... but each person has an internal/virtual <strong>_ProjectAsManagerArray</strong> which may list more than one project.</em></p>
<?php
$objPersonArray = Person::QueryArray(QQ::OrCondition(QQ::Like(QQN::Person()->ProjectAsManager->Name, '%ACME%'), QQ::Like(QQN::Person()->ProjectAsManager->Name, '%HR%')), QQ::Clause(QQ::ExpandAsArray(QQN::Person()->ProjectAsManager), QQ::OrderBy(QQN::Person()->LastName, QQN::Person()->FirstName)));
foreach ($objPersonArray as $objPerson) {
_p('<li>' . $objPerson->FirstName . ' ' . $objPerson->LastName, false);
// Now, instead of using the _ProjectAsManager virtual attribute, we will use
// the _ProjectAsManagerArray virtual attribute, which gives us an array of Project objects
$strProjectNameArray = array();
foreach ($objPerson->_ProjectAsManagerArray as $objProject) {
array_push($strProjectNameArray, QApplication::HtmlEntities($objProject->Name));
}
printf(' via: %s</li>', implode(', ', $strProjectNameArray));
}
?>
</div>
<?php
require '../includes/footer.inc.php';
protected function update_autocompleteList()
{
$strTyped = $this->AjaxAutocomplete->Text;
$cond = QQ::OrCondition(QQ::Like(QQN::Person()->FirstName, '%' . $strTyped . '%'), QQ::Like(QQN::Person()->LastName, '%' . $strTyped . '%'));
$clauses[] = QQ::OrderBy(QQN::Person()->LastName, QQN::Person()->FirstName);
$lst = Person::QueryArray($cond, $clauses);
// If you implement Person::__toString in the model->Person.class.php file, you
// could just pass the $lst to the DataSource.
// Instead, we will build the list using autcomplete list items
//$this->AjaxAutocomplete->DataSource = $lst;
$a = array();
foreach ($lst as $objPerson) {
$a[] = new QListItem($objPerson->FirstName . ' ' . $objPerson->LastName, $objPerson->Id);
}
$this->AjaxAutocomplete->DataSource = $a;
}
<h3>Get All People Who Are on a Project Managed by Karen Wolfe (Person ID #7)<br/>showing the Project which is involved in the JOIN via Expand()</h3>
<i>Notice how some people may be listed twice, once for each project which he or she is part of that is managed by Karen Wolfe.</i><br/><br/>
<?php
$objPersonArray = Person::QueryArray(QQ::Equal(QQN::Person()->ProjectAsTeamMember->Project->ManagerPersonId, 7), QQ::Clause(QQ::Expand(QQN::Person()->ProjectAsTeamMember->Project), QQ::OrderBy(QQN::Person()->LastName, QQN::Person()->FirstName)));
foreach ($objPersonArray as $objPerson) {
printf('%s %s (via the "%s" project)<br/>', QApplication::HtmlEntities($objPerson->FirstName), QApplication::HtmlEntities($objPerson->LastName), QApplication::HtmlEntities($objPerson->_ProjectAsTeamMember->Name));
}
?>
<br/>
<h3>Same as above, but this time, use ExpandAsArray()</h3>
<i>Notice how each person is only listed once... but each person has an internal/virtual <b>_ProjectAsTeamMemberArray</b> which may list more than one project.</i><br/><br/>
<?php
$objPersonArray = Person::QueryArray(QQ::Equal(QQN::Person()->ProjectAsTeamMember->Project->ManagerPersonId, 7), QQ::Clause(QQ::ExpandAsArray(QQN::Person()->ProjectAsTeamMember), QQ::OrderBy(QQN::Person()->LastName, QQN::Person()->FirstName, QQN::Person()->ProjectAsTeamMember->Project->Name)));
foreach ($objPersonArray as $objPerson) {
_p($objPerson->FirstName . ' ' . $objPerson->LastName);
_p('<br/>', false);
// Now, instead of using the _ProjectAsTeamMember virtual attribute, we will use
// the _ProjectAsTeamMemberArray virtual attribute, which gives us an array of Project objects
$strProjectNameArray = array();
foreach ($objPerson->_ProjectAsTeamMemberArray as $objProject) {
array_push($strProjectNameArray, QApplication::HtmlEntities($objProject->Name));
}
printf(' via: %s<br/>', implode(', ', $strProjectNameArray));
}
?>
<?php
require '../includes/footer.inc.php';
<?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';
protected function update_autocompleteList($strFormId, $strControlId, $strParameter)
{
$strLookup = $strParameter;
$objControl = $this->GetControl($strControlId);
$cond = QQ::OrCondition(QQ::Like(QQN::Person()->FirstName, '%' . $strLookup . '%'), QQ::Like(QQN::Person()->LastName, '%' . $strLookup . '%'));
$clauses[] = QQ::OrderBy(QQN::Person()->LastName, QQN::Person()->FirstName);
$lst = Person::QueryArray($cond, $clauses);
/*
* If you implement Person::__toString in the model->Person.class.php file, you
* could just pass the $lst to the DataSource. If you want to add a 'label' item
* to the display, you can override toJsObject in the People.class.php file.
*
* For puposes of this example, we will build a custom list using list items below.
*
*/
//$this->AjaxAutocomplete->DataSource = $lst;
$a = array();
foreach ($lst as $objPerson) {
$item = new QListItem($objPerson->FirstName . ' ' . $objPerson->LastName, $objPerson->Id);
if ($objControl->DisplayHtml) {
$item->Label = '<em>' . $objPerson->FirstName . ' ' . $objPerson->LastName . '</em>';
}
$a[] = $item;
}
$objControl->DataSource = $a;
}
/**
* Load an array of NameItem objects for a given NameItem
* via the person_nameitem_assn table
* @param integer $intNameItemId
* @param QQClause[] $objOptionalClauses additional optional QQClause objects for this query
* @return Person[]
*/
public static function LoadArrayByNameItem($intNameItemId, $objOptionalClauses = null)
{
// Call Person::QueryArray to perform the LoadArrayByNameItem query
try {
return Person::QueryArray(QQ::Equal(QQN::Person()->NameItem->NameItemId, $intNameItemId), $objOptionalClauses);
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
}
</ul>
<h2>Select all People, Ordered by Last Name then First Name, Limited to the first 4 results</h2>
<p><em>Combining QQ::OrderBy and QQ::LimitInfo</em></p>
<ul>
<?php
$objPersonArray = Person::QueryArray(QQ::All(), QQ::Clause(QQ::OrderBy(QQN::Person()->LastName, QQN::Person()->FirstName), QQ::LimitInfo(4)));
foreach ($objPersonArray as $objPerson) {
_p('<li>' . $objPerson->FirstName . ' ' . $objPerson->LastName . '</li>', false);
}
?>
</ul>
<h2>Select all People, those with last name Smith first, then ordered by First Name</h2>
<p><em>Using a QQ::Condition as an ORDER BY clause</em></p>
<ul>
<?php
$objPersonArray = Person::QueryArray(QQ::All(), QQ::Clause(QQ::OrderBy(QQ::NotEqual(QQN::Person()->LastName, 'Smith'), QQN::Person()->FirstName)));
foreach ($objPersonArray as $objPerson) {
_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>
/**
* Main utility method to aid with data binding. It is used by the default BindAllRows() databinder but
* could and should be used by any custom databind methods that would be used for instances of this
* MetaDataGrid, by simply passing in a custom QQCondition and/or QQClause.
*
* If a paginator is set on this DataBinder, it will use it. If not, then no pagination will be used.
* It will also perform any sorting (if applicable).
*
* @param QQCondition $objConditions override the default condition of QQ::All() to the query, itself
* @param QQClause[] $objOptionalClauses additional optional QQClause object or array of QQClause objects for the query
* @return void
*/
public function MetaDataBinder(QQCondition $objCondition = null, $objOptionalClauses = null)
{
// Setup input parameters to default values if none passed in
if (!$objCondition) {
$objCondition = QQ::All();
}
$objClauses = $objOptionalClauses ? $objOptionalClauses : array();
// We need to first set the TotalItemCount, which will affect the calcuation of LimitClause below
if ($this->Paginator) {
$this->TotalItemCount = Person::QueryCount($objCondition, $objClauses);
}
// 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->OrderByClause) {
array_push($objClauses, $objClause);
}
// Add the LimitClause information, as well
if ($objClause = $this->LimitClause) {
array_push($objClauses, $objClause);
}
// Set the DataSource to be a Query result from Person, given the clauses above
$this->DataSource = Person::QueryArray($objCondition, $objClauses);
}
/**
* Gets all the Person objects with a certain last name as an array
*
* @param string $strLastName
* @return Person[]
*/
public function GetPeople($strLastName)
{
return Person::QueryArray(QQ::Equal(QQN::Person()->LastName, $strLastName));
}
<h3>QuerySingle Example</h3>
<?php
$objPerson = Person::QuerySingle(QQ::Equal(QQN::Person()->Id, 1));
// Notice that QuerySingle returned just a single Person object
_p($objPerson->FirstName . ' ' . $objPerson->LastName);
_p('<br/>', false);
?>
<h3>QueryArray Example</h3>
<?php
$objPersonArray = Person::QueryArray(QQ::In(QQN::Person()->Id, array(5, 6, 8)));
// Notice that QueryArray returns an array of Person objects... this will
// be true even if the result set only yields 1 row.=
foreach ($objPersonArray as $objPerson) {
_p($objPerson->FirstName . ' ' . $objPerson->LastName);
_p('<br/>', false);
}
?>
<h3>QueryCount Example</h3>
<?php
$intCount = Person::QueryCount(QQ::In(QQN::Person()->Id, array(5, 6, 8)));
// Notice that QueryCount returns an integer
_p($intCount . ' rows.');
<li>QQ::AndCondition()</li>
<li>QQ::OrCondition()</li>
<li>QQ::Not() - "Not" can only take in one <strong>QQ Condition</strong> class</li>
</ul>
<p>(conditions can be passed in as parameters and/or as arrays)</p>
<p>Because And/Or/Not conditions can take in <i>any</i> other condition, including other And/Or/Not conditions, you can
embed these conditions into other conditions to create what ends up being a logic tree for your entire SQL Where clause. See
below for more information on this.</p>
</div>
<div id="demoZone">
<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>
public function testOrderByExpansion()
{
$objPersonArray = Person::QueryArray(QQ::All(), QQ::OrderBy(QQ::IsNotNull(QQN::Person()->ProjectAsManager->Description), false, QQN::Person()->ProjectAsManager->Id));
$this->assertEquals(7, $objPersonArray[0]->Id, "Found first project with manager");
}
