Laravel makes interacting with databases extremely simple across a variety of database backends.
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.
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]
]);
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]
);
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;
}
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');
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;
}
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;
});
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');
$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();
$users = DB::table('users')->select(DB::raw('count(*) as user_count, status'))
->where('status', '=', 1)->groupBy('status')->get();
$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();
$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();
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();
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();
The orderBy method allows you to sort the result of the query by a given column.
$users = DB::table('users')->orderBy('name', 'desc')->get();
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();
The inRandomOrder method can be used to sort the query results randomly.
$randomUser = DB::table('users')->inRandomOrder()->first();
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();
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();
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]);
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']);
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();
You can use truncate method to remove all rows and reset the auto-increment ID to zero.
DB::table('users')->truncate();
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]);
}
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);
The links method will render the links to the rest of the pages in view file.
{{ $users->links() }}
Leave a comment