query builder

Laravel makes interacting with databases extremely simple across a variety of database backends.

RAW SQL QUERIES

After configuring the database, you can run queries using the DB facade. The DB facade provides methods for each type of query: select, update, insert, delete and statement.

QUERY BUILDER

Laravel's query builder provides much convenient and fluent interface to create and run database queries. It uses PDO parameter binding to prevent SQL injection attacks, so, there's no need to clean strings being passed as bindings.

INSERT

The query builder provides an insert method for inserting records into the database table that accepts an array of column names and values.

DB::table('users')->insert(
    ['email' => 'john@example.com', 'satatus' => 1]
);

You can even insert several records into the table by passing an array of arrays. Each array represents a row to be inserted into the table.

DB::table('users')->insert([
    ['email' => 'james@laravel.com', 'satatus' => 1],
    ['email' => 'john@laravel.com', 'satatus' => 1]
]);

Auto-Incrementing IDs

If the table has an auto-increment id, you can use the insertGetId method to insert a record and then retrieve the ID.

$id = DB::table('users')->insertGetId(
    ['email' => 'john@laravel.com', 'satatus' => 1]
);

RETRIEVE ALL DATA FROM A TABLE

public function index()
{
    $users = DB::table('users')->get();
    return view('user.index', ['users' => $users]);
}

The get() method returns a collection containing the results where each result is an instance of the PHP StdClass object. You can access each column's value by accessing the column as a property of the object.

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

RETRIEVE SINGLE ROW / COLUMN FROM A TABLE

The first() method will return a single StdClass object.

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

The value() method will return the value of the column directly.

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

RETRIEVING LIST OF COLUMN VALUES

The pluck() method can be used to retrieve collection of values of a single column.

$names = DB::table('users')->pluck('name');

foreach ($names as $name) {
    echo $name;
}

CHUNKING RESULTS

Chunk method can be used to retrieve a small chunk of the resultset at a time and feed each chunk into a closure for processing.

DB::table('users')->orderBy('id')->chunk(50, 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(50, function ($users) {
    

    return false;
});

AGGREGATES

The query builder also provides a variety of aggregate methods such as count, max, min, avg and sum.

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

SELECT CLAUSES

$users = DB::table('users')->select('name', 'email as user_email')->get();

The distinct() method allows you to return distinct results.

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

Addselect() method can be used to add a column to an existing query builder instance.

$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();

RAW EXPRESSIONS

$users = DB::table('users')->select(DB::raw('count(*) as user_count, status'))
->where('status', '=', 1)->groupBy('status')->get();

JOINS

$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();

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

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

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

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

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

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

WHERE CLAUSES

$users = DB::table('users')->where('status', '=', 1)->get();

For convenience, if you simply want to verify that a column is equal to a given value, you may pass the value directly as the second argument to the where method.

$users = DB::table('users')->where('status', 1)->get();

$users = DB::table('users')->where('status', '>=', 1)->get();

$users = DB::table('users')->where('name', 'like', 'j%')->get();

$users = DB::table('users')->where([
    ['status', '=', '1'],
    ['subscribed', '<>', '0'],
])->get();

OR STATEMENTS

You can chain WHERE clauses together as well as add OR clauses to the query. The ORWHERE method accepts the same arguments as the where method.

$users = DB::table('users')->where('id', '>', 100)->orWhere('name', 'John')->get();

ADDITIONAL WHERE CLAUSES

Let's have a look a some additional where clauses that we can use in the query builder.

$users = DB::table('users')->whereBetween('id', [1, 100])->get();

$users = DB::table('users')->whereNotBetween('votes', [1, 100])->get();

$users = DB::table('users')->whereIn('id', [1, 2, 3])->get();

$users = DB::table('users')->whereNotIn('id', [1, 2, 3])->get();

$users = DB::table('users')->whereNull('updated_at')->get();

$users = DB::table('users')->whereNotNull('updated_at')->get();

$users = DB::table('users')->whereDate('created_at', '2016-12-31')->get();

$users = DB::table('users')->whereMonth('created_at', '12')->get();

$users = DB::table('users')->whereDay('created_at', '31')->get();

$users = DB::table('users')->whereYear('created_at', '2016')->get();

$users = DB::table('users')->whereColumn('first_name', 'last_name')->get();

An array of multiple conditions can also be passed in the whereColumn method.

$users = DB::table('users')->whereColumn([
    ['first_name', '=', 'last_name'],
    ['updated_at', '>', 'created_at']
])->get();

ORDERBY

The orderBy method allows you to sort the result of the query by a given column.

$users = DB::table('users')->orderBy('name', 'desc')->get();

LATEST & OLDEST

The latest and oldest methods allows to order results by date. By default, result will be ordered by the created_at column.

$user = DB::table('users')->latest()->first();

INRANDOMORDER

The inRandomOrder method can be used to sort the query results randomly.

$randomUser = DB::table('users')->inRandomOrder()->first();

GROUPBY

The groupBy and having methods can be used to group the query results. The having method is similar to the where method.

$users = DB::table('users')->groupBy('account_id')->having('account_id', '>', 100)->get();

The havingRaw method can be used to set a raw string as the value of the having clause.

$users = DB::table('orders')->select('department', DB::raw('SUM(price) as total_sales'))
->groupBy('department')->havingRaw('SUM(price) > 2500')->get();

SKIP & TAKE

The skip and take methods can be used to limit or skip a given number of results in the query.

$users = DB::table('users')->skip(10)->take(5)->get();

Alternatively, you can use the limit and offset methods.

$users = DB::table('users')->offset(10)->limit(5)->get();

UPDATES

The query builder update method can be used to update existing records. It accepts an array of column and value pairs containing the columns to be updated. You may constrain the update query using where clauses too.

DB::table('users')->where('id', 1)->update(['status' => 0]);

INCREMENT & DECREMENT

The query builder also provides convenient methods for incrementing or decrementing the value of a given column.

DB::table('users')->increment('orders');

DB::table('users')->increment('orders', 5);

DB::table('users')->decrement('orders');

DB::table('users')->decrement('orders', 5);

//You can also specify additional columns to update during the operation:

DB::table('users')->increment('orders', 1, ['name' => 'John']);

DELETES

The query builder can be used to delete records from the table using the delete method. You can constrain delete statements by adding where clauses.

DB::table('users')->delete();

DB::table('users')->where('orders', '>', 100)->delete();

TRUNCATE TABLE

You can use truncate method to remove all rows and reset the auto-increment ID to zero.

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

PAGINATION

Laravel provides convenient pagination for database results which is compatible with the Bootstrap CSS framework. The paginate method takes care of setting the proper limit and offset based on the current page being viewed by the user. The only argument passed to the paginate method is the number of data rows you would like displayed per page.

public function index()
{
    $users = DB::table('users')->paginate(15);
	return view('user.index', ['users' => $users]);
}

Simple Pagination

You can use the simplaePaginate method instead if you want to display only Next and Previous links in your pagination view.

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

DISPLAY PAGINATION LINKS IN VIEW

The links method will render the links to the rest of the pages in view file.

{{ $users->links() }}

0 Like 0 Dislike 0 Comment Share

Leave a comment