Laravel

Working with Database Query Builder in Laravel

January 30, 2018

author:

Working with Database Query Builder in Laravel

Database Query Builder provided by Laravel is very simple but robust and provides a convenient interface to perform database queries. It can be utilized to run database operations in our application and works similarly for all the supported databases.

It is also important to note that Laravel Query Builder uses the PDO parameter binding to secure our application against SQL injection attacks. With Query Builder, we can perform numerous operations like Join, Union, Order, Group, Offset, etc. In this section though, we will have an overview of some commonly used queries for CRUD operations:

# Create (Insert)

Laravel Query Builder provides a method insert() for inserting single or multiple records in the database table. The insert() method expects an array of column name and values:

# Controller
...
use Illuminate\Support\Facades\DB;

class PostController extends Controller
{    
    public function store(Request $request)
    {
        $post = DB::table('posts')->insert([
                   'title' => 'Laravel Basic', 
                   'content' => 'Laravel is a PHP framework'
                 ]);
        return $post;
    }
}

# Retrieving Result (Read)

Table method of the DB facade can be used to begin a query. It allows chaining of multiple constraints onto the query before getting the final result using get method. get() method returns the results as a collection.

$posts = DB::table('posts')->get(); //here posts is the table name

$titles = DB::table('posts')->select('title')->get();

$conditional= DB::table('posts')->where('category', 'laravel')->get();

# Update Records

Laravel Query Builder also has a method update() to update the existing records. Like insert(), this method also expects an array of column name and values as an argument.

DB::table('posts')
 ->where('id', 1)
 ->update(['title' => 'New title']);

# Delete Records

To delete records, Laravel Query Builder provides delete() method. Similar to old school, we can delete a particular record by specifying it in the constraint,

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

DB::table('posts')->where('comments', '>', 35)->delete();

Conclusion:

In this segment, we saw some queries to build an API. These are basic yet important to work with Laravel application. We used laravel query builder for this purpose, in the coming sessions, we will also work with Eloquent ORM.

Questions & Comments:

Glad you came this far. Hope you enjoyed working with Query Builder in Laravel with this tutorial. If you come across any errors/bugs, post them in the comment section below.

Leave a comment

Your email address will not be published. Required fields are marked *