ClanCats Logo

Hydrahon

SQL Select Joins

Do you like to join things? We too!

#  Basic Join

Let's assume we have two tables, a users table and a comments table. Every comment has a column containing a user id. Now we want to fetch all comment bodies including the name of their creators.

// SQL: 
//   select `c`.`body`, `u`.`name` 
//   from `comments` as `c` 
//   left join `users` as `u` on `c`.`user_id` = `u`.`id`
$h->table('comments as c')
    ->select(['c.body', 'u.name'])
    ->join('users as u', 'c.user_id', '=', 'u.id')
    ->get();

the default method join will generate a left join.

Add a join statement to the current query

->join('avatars', 'users.id', '=', 'avatars.user_id')

Method definition:

public function join($table, $localKey, $operator = null, $referenceKey = null, $type = 'left')

Arguments

Data type Variable name Comment
array|string $table The table to join. (can contain an alias definition.)
string|\Closure $localKey
string $operator The operator (=, !=, <, > etc.)
string $referenceKey
string $type The join type (inner, left, right, outer)

Returns

static The current query builder.

#  Right Join

Alias of the join method with join type right.

Method definition:

public function rightJoin($table, $localKey, $operator = null, $referenceKey = null)

Arguments

Data type Variable name Comment
array|string $table The table to join. (can contain an alias definition.)
string $localKey
string $operator The operator (=, !=, <, > etc.)
string $referenceKey

Returns

static The current query builder.

#  Inner Join

Alias of the join method with join type inner.

Method definition:

public function innerJoin($table, $localKey, $operator = null, $referenceKey = null)

Arguments

Data type Variable name Comment
array|string $table The table to join. (can contain an alias definition.)
string $localKey
string $operator The operator (=, !=, <, > etc.)
string $referenceKey

Returns

static The current query builder.

#  Outer Join

Alias of the join method with join type outer.

Method definition:

public function outerJoin($table, $localKey, $operator = null, $referenceKey = null)

Arguments

Data type Variable name Comment
array|string $table The table to join. (can contain an alias definition.)
string $localKey
string $operator The operator (=, !=, <, > etc.)
string $referenceKey

Returns

static The current query builder.

#  Complex Join

Sometimes you need to join data on more than one condition. Therefor you can pass a callback to the all join methods allowing you to specify more conditions.

// SQL:
//   select `c`.`body`, `u`.`name` from `comments` as `c` 
//   inner join `users` as `u` 
//   on `u`.`id` = `c`.`user_id` 
//   or `u`.`id` = `c`.`moderator_id` 
//   and ( `u`.`active` = 1 and `u`.`deleted_at` is NULL )
$h->table('comments as c')
    ->select(['c.body', 'u.name'])
    ->innerJoin('users as u', function($join) 
    {
        $join->on('u.id', '=', 'c.user_id');
        $join->orOn('u.id', '=', 'c.moderator_id');
        $join->where(function($q) {
            $q->where('u.active', true);
            $q->whereNull('u.deleted_at');
        });
    })
    ->get();