Database Joins

Adding a simple two table field join is simple.

$db = Database::newDB();

// These are the fields we want from each table - name, address, and phone
$alpha = $db->addTable('alpha');
$alpha->addField('name');

$beta = $db->addTable('beta');
$beta->addField('address');

$delta = $db->addTable('delta');
$delta->addField('phone');

// The field object is needed here because it is used twice in the join
$user_id = $beta->getField('user_id');

// Joining alpha's id against the user_id field I grabbed above.
$db->join($alpha->getField('id'), $user_id);
// Again, using the user_id field against delta's user-id field.
$db->join($user_id, $delta->getField('user_id'));

echo $db->selectQuery();

The result (in MySQL for this example):

SELECT     `alpha`.`name`, `beta`.`address`, `delta`.`phone` 
FROM       `alpha` 
INNER JOIN `beta` ON `alpha`.`id` = `beta`.`user_id` 
INNER JOIN `delta` ON `beta`.`user_id` = `delta`.`user_id`

For more advanced joins, you will need to use the joinResources method instead.

Here is a multiple conditional set up on the same table to get a max versioned row:

$db = Database::newDB();

$alpha1 = $db->addTable('alpha', 't1');
$alpha2 = $db->buildTable('alpha', 't2');

$c1 = $alpha1->getFieldConditional('id', $alpha2->getField('id'));
$c2 = $alpha1->getFieldConditional('revision', $alpha2->getField('revision'), '<');

$db->joinResources($alpha1, $alpha2, new \Database\Conditional($c1, $c2, 'and'), 'inner');

echo $db->selectQuery();

The result:

SELECT t1.* 
FROM   `alpha` AS t1 
           INNER JOIN `alpha` AS t2 
               ON ((`t1`.`id` = `t2`.`id`) 
                   AND (`t1`.`revision` < `t2`.`revision`))

Here is an example of a join on a subselect:

$db = Database::newDB();
$beta = $db->addTable('beta');
$beta->addField('alpha_id');
$ss = new \Database\SubSelect($db, 't1');

$db2 = Database::newDB();
$alpha = $db2->addTable('alpha', 't2');

$c1 = $alpha->getFieldConditional('id', $ss->getField('alpha_id'));

$db2->joinResources($alpha, $ss, $c1, 'left');

echo $db2->selectQuery();

The result:

SELECT t2.* 
FROM   `alpha` AS t2 
       LEFT OUTER JOIN (SELECT `beta`.`alpha_id` FROM `beta`) AS t1 
           ON (`t2`.`id` = t1.`alpha_id`)