3. Querying in Qcodo
* Qcodo Query: Handling Association Tables
<< * Qcodo Query: The QQ Clause Classes   |   Back to Main   |   * Qcodo Query: Handling Reverse Relationships >>

View Source
will open in a new window
QQ and Association Tables (Many-to-Many Relationships)
One key feature of Qcodo Query is its ability to treat relationships in Association tables just like any other foreign key relationship. QQ has the ability to perform the full set of QQ functionality (including conditions, expansions, ordering, grouping, etc.) on tables related via association tables just as it would on tables related via a direct foreign key.

Naming standards for the many to many relationship are the same as the naming standards for the public methods for associating/unassociating in the class, itself. So just as $objPerson->GetProjectAsTeamMemberArray will retrieve an array of Project objects that are associated to this Person object as a "Team Member", QQN::Person()->ProjectAsTeamMember will refer to the "team_member_project_assn" association table joined against the "person" table.

And again, because all the QQ Nodes are linked together, you can go from there to pull the project table, itself, as well as any columns from that project table. In fact, the linkages can go indefinitely. QQN::Person()->ProjectAsTeamMember->Project->ManagerPerson->FirstName refers to the "first name of the manager of any project that this person is a team member of."

More importantly, when performing Qcodo Queries across association tables, we can Expand on the many-to-many relationship, which would use a special virtual attribute to help describe the individual object, itself, which was involved for the join. In this case, if we were to do a query of the person table, expanding on any ProjectAsTeamMember objects, the actual project that is joined is available to the Person object as $objPerson->_ProjectAsTeamMember.

And finally, on a similar note, you could instead use ExpandAsArray which would do a similar expansion on the associated object, but store it as an array. See below for the differences of each.

Get All People Who Are on a Project Managed by Karen Wolfe (Person ID #7)

Alex 5456
Brett Carlisle
joe Doe
Samantha Jones
Jacob Pratt
Kendall Public
Ben Robinson
Wendy Smith
Karen Wolfe

Get All People Who Are on a Project Managed by Karen Wolfe (Person ID #7)
showing the Project which is involved in the JOIN via Expand()

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.

Alex 5456 (via the "ACME Website Redesign" project)
Alex 5456 (via the "ACME Payment System" project)
Brett Carlisle (via the "ACME Payment System" project)
joe Doe (via the "ACME Payment System" project)
Samantha Jones (via the "ACME Website Redesign" project)
Samantha Jones (via the "ACME Payment System" project)
Jacob Pratt (via the "ACME Payment System" project)
Kendall Public (via the "ACME Website Redesign" project)
Kendall Public (via the "ACME Payment System" project)
Ben Robinson (via the "ACME Payment System" project)
Wendy Smith (via the "ACME Website Redesign" project)
Karen Wolfe (via the "ACME Website Redesign" project)

Same as above, but this time, use ExpandAsArray()

Notice how each person is only listed once... but each person has an internal/virtual _ProjectAsTeamMemberArray which may list more than one project.

Alex 5456
     via: ACME Payment System, ACME Website Redesign
Brett Carlisle
     via: ACME Payment System
joe Doe
     via: ACME Payment System
Samantha Jones
     via: ACME Payment System, ACME Website Redesign
Jacob Pratt
     via: ACME Payment System
Kendall Public
     via: ACME Payment System, ACME Website Redesign
Ben Robinson
     via: ACME Payment System
Wendy Smith
     via: ACME Website Redesign
Karen Wolfe
     via: ACME Website Redesign