Automatic Table Linking and Joins
Automating the collection of related data
When designing a database, often some tables are related to others - a membership table would contain a reference to a person's id and the group id that they are a member of. Using the Link methods, you can automatically fetch objects into the parents variables.
Automated links are supported by a databasename.links.ini file. which stores the relations ship between tables, maping one tables column to anothers. This databasename.links.ini file is used by the getLinks() and joinAdd() Method, to either retrieve related information of a primary object, or quickly build complex multitable queries.
The other way of using linking is via the getLink() method, which you can manually use without a database.links.ini file to specify a column, and how it relates to another table and column.
The goal of getlinks and joinAdd is to make connecting two tables as simple and fast as possible, while still ensuring that the code is reasonably comprehensable. In the example below, It is demostrated how getlinks() can be used to fetch more data about an object after the initial fetch, and it can also be used to test the links file prior to building a full blown join Query.
A simple introduction to links and joins
<?php
// just loop and fetch more information
$person = new DataObjects_Person;
$person->eyeColour = 'red';
$person->find();
while ($person->fetch()) {
// this will look up in the cars table for the id matching the person->car value.
$car = $person->getLink('car','cars','id');
echo "{$person->name} has red eyes and owns a {$car->type}\n";
}
// now if you create a database.links.ini file with the car example
// the example would look like this.
$person = new DataObjects_Person;
$person->eyeColour = 'red';
$person->find();
while ($person->fetch()) {
// use the links.ini file to automatically load
// the car object into $person->_car
$person->getLinks();
echo "{$person->name} has red eyes and owns a {$person->_car->type}\n";
}
// and finally the most complex, using SQL joins and select as.
// the example would look like this.
$person = new DataObjects_Person;
$person->eyeColour = 'red';
// first, use selectAs as to make the select clauses match the column names.
$person->selectAs();
// now lets create the related element
$car = new DataObjects_Car;
// and for fun.. lets look for red eys and red cars..
$car->colour = 'red';
// add them together.
$person->joinAdd($car);
// since both tables have the column id in them, we need to reformat the query so
// that the car columns have a different name.
$person->selectAs($car,'car_%s');
$person->find();
while ($person->fetch()) {
echo "{$person->name} has red eyes and owns a {$person->car_type}\n";
}
?>
Using link ini files for table links
DB_DataObject Version 0.3 introduced the ability to create link ini files so you can map column to other database columns using an ini file this ini file should have the name 'databasename.links.ini', and be placed in the same folder as the database schema ini file 'databasename.ini' file that is created automatically by createTables.php
The databasename.links.ini file contains a section for each table, then the column that is linked equal to the table and column that it should locate the column from. It assumes the relationships are non-primary id to primary id, as the example below shows, the person.owner is linked to grp.id. This indicates that running getLinks() on the person object, will fetch a single bit of data from 3 tables - colurs,grp,attachments.
If you use a 'full stop' in the key (link from column), getLinks() will look up in the table with the column name matching the string to the left of the 'full stop', and replace the 'full stop' with and underscore and assign the object variable to that name. Or you may wish to use the selectAs() method to decide how you want columns from different objects to be returned (when using joinAdd())
An example databasename.links.ini file
; for table person [person] ; link value of eycolor to table colors, match name column eyecolor = colors:name ; link value of owner to table grp, match id column owner = grp:id ; link value of picture to table attachments, match id column picture = attachments:id ; for a sales example with multiple links of a single column [sales] ; for autoloading the car object into $sales->_car_id car_id = car:id ; for autoloading the part number object into $sales->_car_id_partnum car_id.partnum = part_numbers:car_id
It is also possible to create joins on keys consisting of more than one column. Use the following syntax:
Linking tables on composite keys
[table_b] field1,field2 = table_a:field1,field2
This will lead to the following select statement (here using the INNER JOIN syntax):
Resulting SQL
<?php
SELECT * FROM table_b INNER JOIN table_a ON table_b.field1 = table_a.field1 AND table_b.field2 = table_a.field2
?>