May 11, 2017

Laravel Database Joins And Unions

Laravel Database Joins And Unions – The Laravel Database Joins And Unions is used to combine the two table in the database.

Laravel Database Joins And Unions.

Let us understand how to use Laravel Database Joins And Unions.

Function:-

There are some followings function available in Laravel Database Joins And Unions.

1. Joins.
2. Unions.

1. Joins.

Inner Join Clause

The query builder can also be used to write join statements. To perform basic inner join, you can use the join method on query builder. The first argument describe to the join method is the name of the table you need to join. The remaining argument specify column restriction for the join. Then you can see, you can join multiple table in a single query.

$users = DB::table('users')
            ->join('contacts', 'users.id', '=', 'contacts.user_id')
            ->join('orders', 'users.id', '=', 'orders.user_id')
            ->select('users.*', 'contacts.phone', 'orders.price')
            ->get();

Left Join Clause

If you want to perform a Left join altenate of the inner join, you can use leftJoin method.

$users = DB::table('users')
            ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
            ->get();

Cross Join Clause

To perform cross join in two table, You can use crossJoin method with the name of the table you wnat to cross to. Cross join generate the relayion between the first table and the join table.

$users = DB::table('sizes')
            ->crossJoin('colours')
            ->get();

Advanced Join Clauses

You can also use more advanced join clauses like this:-

DB::table('users')
        ->join('contacts', function ($join) {
            $join->on('users.id', '=', 'contacts.user_id')->orOn(...);
        })
        ->get();

If you want to use a where style clause on your joins, you can use the where
and orWhere methods on a join.

DB::table('users')
        ->join('contacts', function ($join) {
            $join->on('users.id', '=', 'contacts.user_id')
                 ->where('contacts.user_id', '>', 5);
        })
        ->get();

2. Unions.

The query builder also provides a quick way to union two query together. you can create an initial query and use the union method to union it with a second query.

$first = DB::table('users')
            ->whereNull('first_name');

$users = DB::table('users')
            ->whereNull('last_name')
            ->union($first)
            ->get();

Laravel Database Joins And Unions