Laravel Query Builder | Chaining With Query Builder

Hey Artisan

In this tutorial we will discuss about laravel query builder. Like eloquent orm, we can use laravel query bulder to manage our data from database. I will discuss about multiple where, orWhere and whereExists query in laravel.

I will also show how insert, delete, update and view using Laravel query builder rather than using Laravel eloquent. So having finished this tutorial we will learn how to write code with laravel query builder and  make dynamic our data.

In this tutorial we will see about laravel query builder multiple where. In this laravel query builder example, we will also see about laravel sub query and multiple subquery in laravel 7.

If you are following eloquent orm and don't know how to use query builder in laravel, then you are a right place. This query builder tutorial is for you.

So let's start our laravel custom query bulder tutorial. The query builder makes it possible to chain methods together. At the end of your chain you’ll use some method—likely get()—to trigger the actual execution of the query you’ve just built.

Let’s take a look at a quick example:

$usersOfType = DB::table('users')
        ->where('type', $type)
        ->get();

Here, we built our query—users table, $type type—and then we executed the query and got our result.

Let’s take a look at what methods the query builder allows you to chain. The methods can be split up into what I’ll call constraining methods, modifying methods, and ending returning methods.

Constraining methods

These methods take the query as it is and constrain it to return a smaller subset of possible data:

where()
Allows you to limit the scope of what’s being returned using WHERE. By default, the signature of the where() method is that it takes three parameters—the column, the comparison operator, and the value:

 $newContacts = DB::table('contact')
        ->where('created_at', '>', Carbon::now()->subDay())
        ->get();

Potential confusion with multiple where and orWhere calls

If you are using orWhere() calls in conjunction with multiple where() calls, you need to be very careful to ensure the query is doing what you think it is.

This isn’t because of any fault with Laravel, but because a query like the following might not do what you expect:

$canEdit = DB::table('users')
        ->where('admin', true)
        ->orWhere('plan', 'premium')
        ->where('is_plan_owner', true)
        ->get();

If you want to write SQL that says “if this OR (this and this),” which is clearly the intention in the previous example, you’ll want to pass a closure into the orWhere() call:

  $canEdit = DB::table('users')
        ->where('admin', true)
        ->orWhere(function ($query) {
            $query->where('plan', 'premium')
                ->where('is_plan_owner', true);
        })
        ->get();

whereExists()
Allows you to select only rows that, when passed into a provided subquery, return at least one row. Imagine you only want to get those users who have left at least one comment:

Read Also : Binding Data to Views Using View Composers in Laravel

 $commenters = DB::table('users')
        ->whereExists(function ($query) {
            $query->select('id')
                ->from('comments')
                ->whereRaw('comments.user_id = users.id');
        })
        ->get();

skip() and take()

Most often used for pagination, these allow you to define how many rows to return and how many to skip before starting the return—like a page number and a page size in a pagination system:

$page4 = DB::table('contacts')->skip(30)->take(10)->get();

count()

Returns an integer count of all of the matching results:

$countVips = DB::table('contacts')
        ->where('vip', true)
        ->count();

min() and max()

Return the minimum or maximum value of a particular column:

$highestCost = DB::table('orders')->max('amount');

sum() and avg()

Return the sum or average of all of the values in a particular column:

$averageCost = DB::table('orders')
   ->where('status', 'completed')
   ->avg('amount');

Joins

Joins can sometimes be a pain to define, and there’s only so much a framework can do to make them simpler, but the query builder does its best. Let’s look at a sample:

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

The join() method creates an inner join. You can also chain together multiple joins one after another, or use leftJoin() to get a left join.

Finally, you can create more complex joins by passing a closure into the join() method:

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

Unions

You can union two queries together by creating them first and then using the union() or unionAll() method to union them:

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

Inserts

The insert() method is pretty simple. Pass it an array to insert a single row or an array of arrays to insert multiple rows, and use insertGetId() instead of insert() to  get the autoincrementing primary key ID back as a return:

   $id = DB::table('contacts')->insertGetId([
        'name' => 'Abe Thomas',
        'email' => 'athomas1987@gmail.com',
    ]);
    DB::table('contacts')->insert([
        [
            'name' => 'Tamika Johnson',
            'email' => 'tamikaj@gmail.com'
        ],
        [
            'name' => 'Jim Patterson',
            'email' => 'james.patterson@hotmail.com'
        ],
    ]);

Updates

Updates are also simple. Create your update query and, instead of get() or first(), just use update() and pass it an array of parameters:

  DB::table('contacts')
        ->where('points', '>', 100)
        ->update(
            [
                'status' => 'vip'
            ]
        );

You can also quickly increment and decrement columns using the increment() and decrement() methods. The first parameter of each is the column name, and the second is (optionally) the number to increment/decrement by:

DB::table('contacts')->increment('tokens', 5);
DB::table('contacts')->decrement('tokens');

Deletes

Deletes are even simpler. Build your query and then end it with delete():

DB::table('users')
        ->where('last_login', '<', Carbon::now()->subYear())
        ->delete();

You can also truncate the table, which both deletes every row and also resets the autoincrementing ID:

DB::table('contacts')->truncate();

Hope you have enjoyed this tutorial.