Joining multiple tables

Normally, you’ll want to join the secondary tables to the main table. This allows you to display that data. (The different between a foreign key and a join is subtle. A foreign key can have multiple possible values on the other end. A join cannot. A join is basically creating one table out of two tables.)

If one value on the left and two on the right should create two rows in the list view, use join. Otherwise, use foreign keys. Foreign keys are necessary for choices on forms, for example. A join won’t get saved to the database when a form is submitted.

There are two ways to join tables.

<? $db->join('foreign table', 'columns'); $db->join('foreign table', 'columns', 'means'); ?>

• The foreign table is the table on the right if this were a SQL query. It’s the table your joining to the local table.
• Columns is the column or list of columns you want from the foreign table.
• The means are the means by which to join the tables. There are three ways to specify the means:
• Leave it blank. The system will use the foreign table’s name as an ID column in the local table. It will try to singularize the foreign table’s name. For example, if your foreign table is “cities”, it will look for a column called ‘city’ in the local table, and match that to the ID column in ‘cities’.
• The name of the column in the local table that matches the ID field of the foreign table.
• A two-value array. The first item in the array is the local table’s column, and the second item is the foreign table’s column.

Left and right joins

If the table you’re joining to does not have a corresponding entry to every record in your main table, those “orphaned” entries won’t show up. If you need them to, you need to use a left join instead of a join. The syntax is exactly the same, but use “$db->leftjoin();” instead of “join()”.

Similarly, if you need every record on the right even if nothing appears on the left, use “$db->rightjoin();”.

Unjoining tables

Sometimes you may want to display joined data and then display unjoined data. Use “$db->unjoin(foreignTable)” to remove a join.

Extra join restrictions

If you need to specify extra restrictions on the join, you can do so in the same manner as you do for adding extra restrictions to the main table.

<? $db->join('foreign table', 'columns'); $db->addRestriction(array('field'=>'value'), 'foreign table'); ?>

For example:

<? $db->rightjoin('companies', array('name', 'ceo')); $restriction = array('city'=>'San Diego'); $db->addRestriction($restriction, 'companies'); ?>

The only difference is that you have to specify which table you’re adding the restriction to.

Lookup in the middle

Sometimes you’ll have a lookup table between your left table and your right table. The classic example is a lookup table that has two columns: an ID column for the left table, and an ID column for the right table.

For example, you might have a table called “members” and a table called “groups”. You can’t just have a “group” field in the members table, because a member might be in more than one group. So you have a lookup table called member_groups that contains both a groupID and a memberID.

To take advantage of the plug-in’s defaults, the lookup table should be called “left table” underscore “right table”, where left table (but not right table) is singular. The two ID fields will both be the singular versions of the table names, with “ID” added to the end.

In the above example, if you wanted to join the members table to the groups table in order to get the group name and the group’s chair, use longjoin as follows:

<? $db->longjoin('groups', array('name', 'chair')); ?>

It’s the same syntax as the other joins. You can use “addRestrictions” on the longjoin once it is created. The restrictions will apply to the rightmost table’s fields.

If the default name for the lookup table doesn’t work, you can specify the lookup table name as a third option:

<? $db->longjoin('groups', array('name', 'chair'), 'group_members'); ?>

This will use “group_members” as the lookup table, but still expect “memberID” and “groupID” as the lookup fields.

Means

There is an option for the means of joining the table with longjoin(), similar to the means with join().

• If you give it one item, this is the field in the lookup table that matches the left table.
• If you give it a second item, this is the field in the lookup table that matches the right table.
• If you give it a third item, this is the field in the left table that matches the lookup table.
• If you give it a fourth item, this is the field in the right table that matches the lookup table.

For example:

<? $groupFields = array('name', 'chair'); $groupTable = 'group_members'; $groupMeans = array('userID', 'groupID'); $db->longjoin('groups', $groupFields, $groupTable, $groupMeans); ?>

This will do the lookup based on the ID field of the main table matching the userID field of the lookup table; and the groupID field of the lookup table matching the ID field of the “groups” table.

Left long join

There is also a “leftlongjoin()” method for when you want to keep items on the left that don’t have any counterparts in the lookup table.

Distinct rows

Sometimes your joins will contain multiple rows that, because of the fields you’ve chosen to display, are complete duplicates. Use the distinct method to remove the duplicate rows.