May 10, 2017

Laravel Database Query Builder

Laravel Database Query Builder – The Laravel Database Query Builder is used to provides a easy way to creating and running database queries.

Laravel Database Query Builder.

Let us understand how to use Laravel Database Query Builder.

Function:-

There are some followings function available in Laravel Database Query Builder.

1. Introduction.
2. Retrieving Results.

1. Introduction.

Laravel database query builder provides a simple and fluent interface to creating and running database queries. It can be used to perform most database operation in your application and perform on all database supported system.

Query builder uses PDO parameter binding to protect your application against SQL injection attack and no need to clean string being passed as bindings.

2. Retrieving Results.

Retrieving All Rows From A Table

You can use the table method on the DB facade to start a query. The table method returns a fluent query builder instance for the given table, allowing you to chain more constraints onto the query and then finally get the results using the get method.

Let’s look at a simple example.

<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;

class UserController extends Controller
{
   
    public function index()
    {
        $users = DB::table('users')->get();

        return view('user.index', ['users' => $users]);
    }
}

The get method returns an Illuminate/support/collection containing the result
where each result is an instance of the PHP StdClass object.

foreach ($users as $user) {
    echo $user->name;
}

Retrieving A Single Row / Column From A Table

If you just need to get a single row from the database table, you can use the first
method. this method will return a single Stdclass object.

$user = DB::table('users')->where('name', 'John')->first();

echo $user->name;

If you do not want to get entire row, you can get exact a single value from a record using the value method.

$email = DB::table('users')->where('name', 'Abhishek')->value('email');

Retrieving A List Of Column Values

If you want to retrieve a collection containing the values of a single column, you can use the pluck method.

$titles = DB::table('roles')->pluck('title');

foreach ($titles as $title) {
    echo $title;
}

You can also specify a custom key column for the returned collection.

$roles = DB::table('roles')->pluck('title', 'name');

foreach ($roles as $name => $title) {
    echo $title;
}

Chunking Results

If you need to work with thousands of data records, you can use chunk method. This method get a small chunk of the result at a time and feeds each chunk into the closure for processing.

DB::table('users')->orderBy('id')->chunk(100, function ($users) {
    foreach ($users as $user) {
        //
    }
});

You can stop further chunks from being processed by returning false from the closure.

DB::table('users')->orderBy('id')->chunk(100, function ($users) {
    // Process the records...

    return false;
});

Aggregates

The query builder also provides a variety of aggregate method like count, max, min, avg and sum.

$users = DB::table('users')->count();

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

You can also combine these method with other clauses.

$price = DB::table('orders')
                ->where('finalized', 1)
                ->avg('price');

Laravel Database Query Builder