Querying Data From Multiple Tables. Joins

You can perform queries from multiple tables by calling table() several times and specifying conditions between tables, however you can also use join() method.

Basic table logic

When you call table() first time, it sets $dsql->main_table parameter to either name of that table or alias. It then can be used to produce expression containing the quoted field name.

$q->table('user');

echo 
$q->getField('name');   // will output `user`.`name`

You should note that getField returns object which can be cast to string, but it keeps the link with original query, therefore syntax like his is possible:

$expr $q->getField('name');

$q->table('user');

echo 
$expr;       // will output `user`.`name`

If you call table() several times, it will set $dsql->main_table to "false" and getField won't be able to guess table name anymore. You would need to specify second argument to getField.

$q->table('user');
$q->table('address');

echo 
$q->getField('name','user');   // will output `user`.`name`

If you are using join(), however, it will not reset main_table property, and you can still use getField like before.

$q->table('user');
$q->join('address');

echo 
$q->getField('name');   // will output `user`.`name`
echo $q->getField('name','address');   // will output `address`.`name`

Basic usage. join(table, [field])

The method join() has several ways to call it. The simplest is by specifying only one string argument.

In this case the table is joined and the table_id from main_table will be used in the "ON" condition.

$q->table('user')->join('address');

 
// will produce select user.id from user join address on address.id=user.address_id;

By default the "id" of JOINED table is linked with the table_id in the main table. This can be changed, however. The "id" of JOINED table can be set to a different field if you specify that field with a dot when joining:

$q->table('user')->join('address.user_id');

 
// will produce select user.id from user join address on address.user_id=user.id

As you noticed, the field from the main_table was changed to "id". If we want to specify that field manually, we can use second argument in the join() method:

$q->table('user')->join('address.code','code');

 
// will produce select user.id from user join address on address.code=user.code

Or we can also specify the table which should participate with the join if we use the dot in the 2nd argument also

$q->table('user')->join('manager');
$q->join('address.code','manager.code');

// will produce select user.id from user
//   join manager on manager.id=user.manager_id
//   join address.code=manager.code

Specifying join type

The third argument in the "join" method can be used to specify the type of join. You can use "left", "right" or "outer" or whatever join type is supported by your SQL. By default the join type is not specified.

$q->table('user')->join('manager',null,'left');
$q->join('address.code','manager.code');

// will produce select user.id from user
//   left join manager on manager.id=user.manager_id
//   join address.code=manager.code

Specifying table alias

Similarly to field() you can use array('alias'=>'table') as a first argument to the join()

$q->table('user')->join(array('m'=>'manager'));

// will produce select user.id from user
//   join manager m on m.id=user.manager_id

You can also use multiple values for multiple joins like that.

$q->table('user')->join(array('m'=>'manager','a'=>'address'));

// will produce select user.id from user
//   join manager m on m.id=user.manager_id
//   join address a on a.id=user.address_id