<?php require_once('../qcubed.inc.php'); ?>
<?php require('../includes/header.inc.php'); ?>

<div id="instructions">
	<h1>ExpandAsArray: Multiple Related Tables in One Swift Query</h1>

	<p>You've certainly had to deal with some sort of hierarchical data in your
	database. Let's say you have a set of <strong>Persons</strong>; each person can be
	a manager for a <strong>Project</strong>. Each <strong>Project</strong> has one or more milestones.
	Oh, wait! And each <strong>Person</strong> has one or more <strong>Addresses</strong>.</p>

	<p>So, if you were to look at the schema subsection visually, it would look like this:<br />
	<img src="expandasarray_schema_diagram.png" alt="Schema Diagram" style="max-width: 100%;" /></p>

	<p>What if you need to display BOTH the project information, and the address
	information, for each of the people in your database? A standard approach
	would be to issue two queries - one for addresses, another one for projects;
	you'd then need to somehow merge the two arrays to be able to output the
	address and the projects of the same person at once. Pain..</p>

	<p>Well, no more pain. <strong>ExpandAsArray</strong> to your rescue. Note that this
	is a somewhat advanced topic - so if you're not comfortable with the
	concepts of <a href="../more_codegen/early_bind.php">QCubed Early Binding</a> and
	<a href="qqclause.php">QQ::Clauses</a>, read up on those first. </p>

	<p>We'll issue one mega-powerful query that will allow you to get BOTH the
	<strong>Address</strong> and the <strong>Project</strong> data (with the related info on
	the <strong>Milestones</strong> for each project) in one powerful sweep. Moreover,
	this will only execute a single query against your database backend.
	Essentially, what will happen here is you'll get an object and ALL
	types of related objects for it - something that SQL isn't really meant
	to do. Object-oriented databases would be an exit, but we love our
	relational systems too much, don't we?</p>

	<p>Here's that magical expression:</p>

	<pre><code>$arrPersons = Person::LoadAll(QQ::Clause(
QQ::ExpandAsArray(QQN::Person()->Address),
QQ::ExpandAsArray(QQN::Person()->ProjectAsManager),
QQ::ExpandAsArray(QQN::Person()->ProjectAsManager->Milestone)
));</code></pre>

	<p>The resulting <strong>$arrPersons</strong> will be an array of objects of type
	<strong>Person</strong>. Each of those objects will have member variables called
	<strong>_AddressArray</strong> (array of <strong>Address</strong> objects) and <strong>_ProjectAsManagerArray</strong>
	(array of <strong>Project</strong> objects). Each of the <strong>Project</strong> objects will also
	have a member variable <strong>_MilestoneArray</strong>, containing an array of <strong>Milestone</strong>
	objects. It's then trivial to iterate through the <strong>$arrPersons</strong> to output all
	of that data - all the <strong>Project</strong> and <strong>Address</strong> is now neatly
	organized under each <strong>Person</strong>.</p>

	<p>NOTE: Be careful around the number of items in each of the tables that will
	be returned by the query that you execute. In the example above, the total
	number of rows returned from SQL in that one query is equal to:</p>
	<p style="text-align:center;"><strong>(Num of Persons) * (Num of Projects) * (Num of Milestones) *
	(Num of Addresses)</strong></p>
	<p>You can see how it can get out of hand quickly - and the performance gains
	you get out of issuing a single query can become a detriment instead, because
	of the amount of data that gets transfered from your database server to PHP.
	Thus, this approach only makes sense if you don't expect to have hundreds of
	items in each of the tables you're extracting the data from. Be sure to look
	at the SQL statement generated by QQuery, and try running it yourself, keeping
	the number of results in mind.</p>
</div>

<div id="demoZone">
	<h2>Projects and Addresses for each Person</h2>
<?php
	QApplication::$Database[1]->EnableProfiling();

	$people = Person::LoadAll(
		QQ::Clause(
			QQ::ExpandAsArray(QQN::Person()->Address),
			QQ::ExpandAsArray(QQN::Person()->ProjectAsManager),
			QQ::ExpandAsArray(QQN::Person()->ProjectAsManager->Milestone)
		)
	);

	foreach ($people as $person) {
		echo "<strong>" . $person->FirstName . " " . $person->LastName . "</strong><br />";
		echo "Addresses: ";
		if (sizeof($person->_AddressArray) == 0) {
			echo "none";
		} else {
			foreach ($person->_AddressArray as $address) {
				echo $address->Street . "; ";
			}
		}
		echo "<br />";

		echo "Projects where this person is a project manager: ";
		if (sizeof($person->_ProjectAsManagerArray) == 0) {
			echo "none<br />";
		} else {
			echo "<br />";
			foreach($person->_ProjectAsManagerArray as $project) {
				echo $project->Name . " (milestones: ";

				if (sizeof($project->_MilestoneArray) == 0) {
					echo "none";
				} else {
					foreach ($project->_MilestoneArray as $milestone) {
						echo $milestone->Name . "; ";
					}
				}
				echo ")<br />";
			}
		}
		echo "<br />";
	}

	QApplication::$Database[1]->OutputProfiling();
?>
</div>

<?php require('../includes/footer.inc.php'); ?>