Joins

Joins are used to combine rows from two or more tables, based on a common field between them. Opis Database allows you to perform four type of joins: INNER join by using the join method, LEFT join by using the leftJoin method, RIGHT join by using the rightJoin method and FULL join by using the fullJoin method.

Performing a join is done in a similar manner for all four types of joins. All join methods accepts two arguments: the first argument represents the joined table and the second argument must be an anonymous callback function, which will receive as an argument an object that will be further used to set the join’s conditions to be met.

Adding a join condition is done by using the on method. This methods accepts two arguments, representing the two columns on which the join will be performed, and optionally a third argument representing the comparison operator which can be one of the following: =, !=, >, <, >= and <=. If no comparison operator is specified, the = operator will be used by default.

$result = $db->from('users')
             ->join('profiles', function($join){
                $join->on('users.id', 'profiles.id');
             })
             ->select()
             ->all();
SELECT * FROM `users` INNER JOIN `profiles` ON `users`.`id` = `profiles`.`id`

Adding multiple join conditions is done by using the andOn and orOn method. Depending on which method you use, the join condition will be combined with the previous declared join condition using the AND or the OR operator.

To add an additional condition to your join expression, that combines with the previous declared condition by using an AND operator, use the andOn method.

$result = $db->from('users')
             ->join('profiles', function($join){
                $join->on('users.id', 'profiles.id')
                     ->andOn('users.email', 'profile.primary_email');
             })
             ->select()
             ->all();
SELECT * FROM `users`
    INNER JOIN `profiles`
        ON `users`.`id` = `profiles`.`id`
        AND `users`.`email` = `profile`.`primary_email`

To add an additional condition to your join expression, that combines with the previous declared condition by using an OR operator, use the orOn method.

$result = $db->from('users')
             ->join('profiles', function($join){
                $join->on('users.id', 'profiles.id')
                     ->orOn('users.email', 'profile.primary_email');
             })
             ->select()
             ->all();
SELECT * FROM `users`
    INNER JOIN `profiles`
        ON `users`.`id` = `profiles`.`id`
        OR `users`.`email` = `profile`.`primary_email`

You can also group your join conditions, by passing as an argument to the on, andOn and orOn methods, an anonymous callback function.

$result = $db->from('users')
             ->join('profiles', function($join){
                $join->on('users.id', 'profiles.id')
                     ->andOn(function($join){
                        $join->on('users.email', 'profiles.primary_email')
                             ->orOn('users.email', 'profiles.secondary_email');
                     });
             })
             ->select()
             ->all();
SELECT * FROM `users`
    INNER JOIN `profiles`
        ON `users`.`id` = `profiles`.`id`
        AND (`users`.`email` = `profiles`.`primary_email`
                OR
             `users`.`email` = `profiles`.`secondary_email`)

Aliasing the table name used within a join, is done by passing a key => value mapped array to the used join method, where the key represents the table’s name and the value represents the alias name.

$result = $db->from('users')
             ->join(['profiles' => 'p'], function($join){
                $join->on('users.id', 'p.id');
             })
             ->select()
             ->all();
SELECT * FROM `users` INNER JOIN `profiles` AS `p` ON `users`.`id` = `p`.`id`