Database: Joins

Inner Join Clause

The query builder may also be used to write join statements. To perform a basic "inner join", you may use the join method on a query builder instance. The first argument passed to the join method is the name of the table you need to join to, while the remaining arguments specify the column constraints for the join. Of course, as you can see, you can join to multiple tables in a single query:

$users = $this->db->table('users')
    ->join('contacts', 'users.id', '=', 'contacts.user_id')
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.*', 'contacts.phone', 'orders.price')
    ->fetchRow();

Left Join Clause

If you would like to perform a "left join" or "right join" instead of an "inner join", use the leftJoin or rightJoin methods. These methods have the same signature as the join method:

$users = $this->db->table('users')
    ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
    ->fetchRow();

$users = $this->db->table('users')
    ->rightJoin('posts', 'users.id', '=', 'posts.user_id')
    ->fetchRow();

Advanced Join Clauses

You may also specify more advanced join clauses. To get started, pass a Closure as the second argument into the join method. The Closure will receive a JoinClause object which allows you to specify constraints on the join clause:

$this->db->table('users')
	->join('contacts', function ($join) {
		$join->on('users.id', '=', 'contacts.user_id')
			 ->orOn('users.id', '=', 'orders.user_id');
	})
	->fetchRow();

If you would like to use a "where" style clause on your joins, you may use the where and orWhere methods on a join. Instead of comparing two columns, these methods will compare the column against a value:

$this->db->table('users')
	->join('contacts', function ($join) {
		$join->on('users.id', '=', 'contacts.user_id')
			 ->where('contacts.user_id', '>', 5);
	})
	->fetchRow();