ExpandAsArray: Multiple Related Tables in One Query

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

So, if you were to look at the schema subsection visually, it would look like this:
Schema Diagram

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..

Well, no more pain. expandAsArray() to your rescue. Note that this is a somewhat advanced topic - so if you're not comfortable with the concepts of QCubed Early Binding and Clauses, read up on those first.

We'll issue one mega-powerful query that will allow you to get BOTH the Address and the Project data (with the related info on the Milestones 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.

Here's that magical expression:


    $arrPersons = Person::loadAll(QQ::clause(
        QQ::expandAsArray(QQN::person()->Address),
        QQ::expandAsArray(QQN::person()->ProjectAsManager),
        QQ::expandAsArray(QQN::person()->ProjectAsManager->Milestone)
    ));

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

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:

(Num of Persons) * (Num of Projects) * (Num of Milestones) * (Num of Addresses)

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.

Projects and Addresses for each Person

Karen Wolfe
Addresses: 1 Pine St; 421 Central Expw;
Projects where this person is a project manager:
ACME Website Redesign (milestones: Milestone A; Milestone B; Milestone C; )
ACME Payment System (milestones: Milestone G; Milestone H; Milestone I; Milestone J; )

Mike Ho
Addresses: none
Projects where this person is a project manager:
State College HR Systzzzz (milestones: Milestone D; Milestone E; )

John Doeooo
Addresses: 1 Love Drive;
Projects where this person is a project manager:
Blueman Industrial Site Architecture (milestones: Milestone F; )

Kendall Public
Addresses: 2 Doves and a Pine Cone Dr.;
Projects where this person is a project manager: none

Ben Robinson
Addresses: 3 Gold Fish Pl.; 323 W QCubed;
Projects where this person is a project manager: none

Alex Smith
Addresses: 22 Elm St;
Projects where this person is a project manager: none

Wendy Smith
Addresses: none
Projects where this person is a project manager: none

Samantha Jones
Addresses: none
Projects where this person is a project manager: none

Linda Brady
Addresses: none
Projects where this person is a project manager: none

Jennifer Smith
Addresses: none
Projects where this person is a project manager: none

Brett Carlisle
Addresses: none
Projects where this person is a project manager: none

Jacob Pratt
Addresses: none
Projects where this person is a project manager: none

5 8
Addresses: none
Projects where this person is a project manager: none

5 8
Addresses: none
Projects where this person is a project manager: none

5 8
Addresses: none
Projects where this person is a project manager: none

57417 87887
Addresses: none
Projects where this person is a project manager: none

Joseph Biden
Addresses: none
Projects where this person is a project manager: none

Joseph Biden
Addresses: none
Projects where this person is a project manager: none

Joseph Biden
Addresses: none
Projects where this person is a project manager: none

sdbv asdgvb
Addresses: none
Projects where this person is a project manager: none

sdbv asdgvb
Addresses: none
Projects where this person is a project manager: none

John Doe
Addresses: none
Projects where this person is a project manager: none

Johnww Doe
Addresses: none
Projects where this person is a project manager: none

ttttttt yyyyyy
Addresses: none
Projects where this person is a project manager: none

ttttttt yyyyyy
Addresses: none
Projects where this person is a project manager: none

ttttttt yyyyyy
Addresses: none
Projects where this person is a project manager: none

ttttttt yyyyyy
Addresses: none
Projects where this person is a project manager: none

ttttttt yyyyyy
Addresses: none
Projects where this person is a project manager: none

ttttttt yyyyyy
Addresses: none
Projects where this person is a project manager: none

PROFILING INFORMATION FOR DATABASE CONNECTION #1: 2 queries performed. Please click here to view profiling detail