Working with Joins

Through the use of relationships and structured queries you can often avoid having to explicitly use database joins within your code. Generally this is good practice–although joins are a powerful SQL feature they can quickly lead to complexity in application logic that deals with their results, and their over-use leads to database optimization headaches.

In some situations, however, joins are inevitable and indeed desirable. One example of this might be for implementing advanced search features, where the queries that are generated might pull in any combination of a number of tables, and the structure of the query is thus determined by the user's input parameters. To help simplify handling these cases, the framework provides the Join abstract class and its two concrete implementations, InnerJoin and LeftOuterJoin.

The Join class provides a simple interface for building joins. For instance, the code below creates an inner join between the Task, and Project classes.

require_once "framework/join.inc";

$join = new InnerJoin();
$join->add(Task);
$join->add(Project);

$joinResults = $join->query("WHERE due_date < now() AND status='Pending' ");

To build the join, you instantiate a new InnerJoin object, then call the add() method to add each of the DataItem classes that you wish to include in the join. You can then query the join via the query() method, passing in any constraints. There are also groupedQuery() and indexedQuery() methods available to return structured join results in much the same fashion as described in the Grouped and Indexed Queries section above.

The InnerJoin query methods return arrays of JoinResults, rather than DataItem-derived classes. The JoinResult class is simply an empty container that is filled with a field corresponding to each joined class. For instance, to continue our example above:

foreach($joinResults as $result)
{
	$task = $result->Task;
	$project = $result->Project;

	echo "{$project->title}: {$task->title}\n";
}

Additionally, there is a helper function called extractJoinResults() to help with extracting the results for just one of the classes in the join. Due to the way joins operate, there could be many duplicate rows, so a helper function is also supplied, called removeDuplicates() that removes duplicate entries from a list of DataItems.

$tasks = extractJoinResults(Task, $joinResults);
$distinctTasks = removeDuplicates($tasks);

Handling Cross-Reference Relationships in Joins

When you are dealing with a many-to-many relationship (implemented by some kind of cross-reference table), you need to add the cross-reference table to the join using the xref() method, rather than the add() method. See the below example, which builds a join from tasks to people, using the cross-referencing table modeled by TaskAssignment.

$join = new InnerJoin();
$join->add(Task);
$join->add(Person);
$join->xref(TaskAssignment);

$joinResults = $join->query("WHERE due_date < now() AND status='Pending' ");

With the xref() method, the fields from the cross-referencing table are not included in the join results.