Laravel Query Builder: Detailed Guide with Code Examples + Screenshots

Laravel Query Builder makes it easier to work with databases in your Laravel projects. Instead of writing long and complex SQL statements, you can use simple PHP methods to create, read, update, and delete data. It gives you more control than Laravel’s Eloquent ORM, while still being easy to read and write.

To get started with Laravel’s Query Builder, you’ll mainly use the DB facade along with a set of methods that help you build queries like select(), where(), insert(), and more.

In this tutorial, we’re going to walk through how to set up Laravel with SQLite and use the Query Builder to interact with a database. We’ll break things down step-by-step: what to do, where to write the code, and how to see the output in your browser.

Let’s get started…

Setting Up and Using Laravel Query Builder (Step-by-Step)

Now for the best part. In this section, you will learn to set up your environment, populate a database with sample data, and effectively use Query Builder for data retrieval, filtering, and manipulation.

Let’s start with the prerequisites…

Prerequisites

Before we begin, here’s what you’ll need:

• PHP (version 8 or above)
• Composer
• Laravel installed (composer create-project laravel/laravel your-app-name)
• A code editor (e.g., VS Code)
• SQLite or another Laravel-supported database like MySQL, PostgreSQL, or SQL Server
• Command Prompt or Terminal for running commands

I’ll be running all commands in the command prompt on my Windows machine. Also, I’ll be using SQLite in this tutorial because it requires no setup and works out of the box.

Step 1: Setting Up Your Laravel Project with SQLite

Let’s get your project ready to talk to a database.

1. Create a New Laravel Project

Open your terminal or command prompt and type these commands:

composer create-project laravel/laravel laravel-query-builder-demo

This will create a brand-new Laravel application in a folder called laravel-query-builder-demo.

Once the Laravel project is created, navigate into your project folder using:

cd laravel-query-builder-demo

This command takes you inside your newly created Laravel project directory, where you’ll run all your setup and development commands moving forward.

2. Create an SQLite Database File

Next, we need an empty file for our SQLite database. While still in your project’s directory, run:

touch database/database.sqlite

Windows users: Since I’m using a Windows machine, I’ll run this command: type nul > database\database.sqlite.

This command creates an empty file named database.sqlite silently without displaying any confirmation message inside the database folder of your project.

We’ll fill it up soon using Laravel’s built-in tools.

3. Tell Laravel to Use SQLite

Now, we need to tell Laravel to use our new SQLite file instead of the default MySQL.

Open the .env file (it’s right in the main folder of your project). You’ll find lines that look like this:

DB_CONNECTION=mysql

DB_HOST=127.0.0.1

DB_PORT=3306

DB_DATABASE=laravel

DB_USERNAME=root

DB_PASSWORD=

Change them to:

DB_CONNECTION=sqlite

DB_DATABASE=[absolute path to your SQLite database file]

A quick tip: If you’re not sure about the full path to your database.sqlite file, you can often use a relative path instead, which is usually simpler. I know the exact path so I’ve used that as you can see in the screenshot above.

4. Run Laravel’s Default Migrations and Add Custom Columns

Laravel comes with some default database tables ready to go, like a users table. Let’s create them first:

Open command prompt and run the migration command below

php artisan migrate

Now, to make our examples work even better, we need to add a couple of new columns to our users table: role and status. These will let us demonstrate filtering more clearly.

Let’s create a new migration for these columns. Go back to the command prompt and run this command to create a new migration file.

php artisan make:migration add_role_and_status_to_users_table --table=users

Open the new migration file (it will be in database/migrations/ and have a timestamp in its name—the numbers will be the current date and time).

Open this new migration file in your code editor. I’ll use the notepad. And replace the entire code with the new code snippet below:

<?php

use Illuminate\Database\Migrations\Migration;

use Illuminate\Database\Schema\Blueprint;

use Illuminate\Support\Facades\Schema;

return new class extends Migration

{

/**

* Run the migrations.

*/

public function up(): void

{

// We're modifying the 'users' table.

Schema::table('users', function (Blueprint $table) {

// Add a 'role' column as a string, with a default value of 'user'.

// The ->after('email') part puts it right after the 'email' column.

$table->string('role')->default('user')->after('email');

// Add a 'status' column as a string, with a default value of 'active'.

// It goes right after the 'role' column.

$table->string('status')->default('active')->after('role');

});

}

/**

* Reverse the migrations.

*/

public function down(): void

{

// This method is for "undoing" the changes if you ever need to rollback.

Schema::table('users', function (Blueprint $table) {

// Remove the 'role' and 'status' columns.

$table->dropColumn('role');

$table->dropColumn('status');

});

}

};

After saving this file, go back to your command prompt one last time and run the migration command again.

php artisan migrate

Awesome! You now have an empty Laravel project with a working SQLite database, a users table, and the necessary role and status columns ready for the examples we’ll take a look at in just a bit.

Step 2: Displaying Users Using Query Builder

Time to fetch some data and show it in your browser!

1. Add Multiple Test Users

It’s always good to have some data to play with, especially a variety of data to show off different filters and sorts. Let’s add several users to our users table.

Run this command in your terminal:

php artisan tinker

The > prompt means Tinker is running and waiting for your input. Inside Tinker, type (or copy-paste) these code blocks for each user one by one, pressing Enter after each insert block:

DB::table('users')->insert([

'name' => 'John Doe',

'email' => '[email protected]',

'password' => bcrypt('password'),

'role' => 'admin', // This user is an admin

'status' => 'active',

'created_at' => now(), // Set creation time for sorting later

'updated_at' => now(),

]);

DB::table('users')->insert([

'name' => 'Jane Smith',

'email' => '[email protected]',

'password' => bcrypt('password'),

'role' => 'user', // This user is a regular user

'status' => 'active',

'created_at' => now()->subDays(5), // Created 5 days ago

'updated_at' => now()->subDays(5),

]);

DB::table('users')->insert([

'name' => 'Peter Jones',

'email' => '[email protected]',

'password' => bcrypt('password'),

'role' => 'editor', // This user is an editor

'status' => 'active',

'created_at' => now()->subHours(12), // Created 12 hours ago

'updated_at' => now()->subHours(12),

]);

DB::table('users')->insert([

'name' => 'Sarah Connor',

'email' => '[email protected]',

'password' => bcrypt('password'),

'role' => 'user', // Another regular user

'status' => 'inactive', // This user is inactive

'created_at' => now()->subMonths(1), // Created 1 month ago

'updated_at' => now()->subMonths(1),

]);

The = true response means the user you added was successfully inserted into the users table.

Now to exit Tinker, type this command in CMD: exit;

We’ve added four diverse users! This will be great for our filtering examples.

2. Modify the Route to Display Users

Routes tell Laravel what to do when someone visits a specific URL. Let’s change our main route (/) to show all our users.

Open the file routes/web.php.

You’ll see some default code. Replace it with this:

<?php

use Illuminate\Support\Facades\Route;

use Illuminate\Support\Facades\DB; // We need to "use" DB here!

Route::get('/', function () {

// This is our first Query Builder command!

// It selects all records from the 'users' table.

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

// We'll return the users as JSON, which is a common data format.

return response()->json($users);

});

Here’s what’s happening:

• use Illuminate\Support\Facades\DB;: This line is important! It tells our file that we want to use the DB (Database) “facade,” which is Laravel’s way of making database interactions easy.

• DB::table(‘users’): This tells Query Builder that we want to work with the users table.

• ->get(): This is the magic command that says, “Go get all the records from this table!”

• return response()->json($users);: This converts the data we got into a JSON format and sends it to the browser.

3. Serve Your Application

Now, let’s see it in action! In your terminal, run:

php artisan serve

Then, open your web browser and go to:

http://localhost:8000

You should now see a JSON array containing all four users you just added, each with their name, email, role, and status. You’ll notice they’re probably ordered by id by default.

Congratulations! You’ve just written your very first Laravel Query Builder query and displayed data in your browser! See, that wasn’t so hard, right?

Tip: Check mark Pretty-print option if the output in your browser doesn’t look properly formatted.

Step 3: Filtering, Selecting, and Sorting Data

The Query Builder can do much more than just get() all data. Let’s refine what we fetch!

You’ll be adding these new routes to your routes/web.php file, just like we did with the first one.

Select Specific Columns

What if you only want the user’s name and email, but not their password or creation date?

Route::get('/users-name-email', function () {

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

return response()->json($users);

});

Now, visit http://localhost:8000/users-name-email and you’ll see a JSON array where each user object only contains their name and email.

Filter with where()

The where() method lets you filter records based on certain conditions. Let’s find all the users who are admins.

Route::get('/admins', function () {

$admins = DB::table('users')->where('role', 'admin')->get();

return response()->json($admins);

});

Visit http://localhost:8000/admins. You should now see only “John Doe” in the JSON output, because he’s the only one we set as an admin. If you had more admins, they would all show up here!

Chain Conditions

You can easily chain multiple where() clauses together to create more specific filters. Let’s find users who are both active AND have the user role.

Route::get('/active-users-role', function () {

$users = DB::table('users')

->where('status', 'active') // First condition

->where('role', 'user') // Second condition

->get();

return response()->json($users);

});

Visit http://localhost:8000/active-users-role. You should see “Jane Smith” but not “Sarah Connor” (because Sarah is inactive), even though both have the user role. This shows how chaining conditions narrows down your results.

Sort the Results

Want your data ordered alphabetically or by date? Use orderBy(). Let’s sort our users by name, from A to Z.

Route::get('/sorted-users', function () {

$users = DB::table('users')

->orderBy('name', 'asc') // Sort by 'name' in ascending (A-Z) order

->get();

return response()->json($users);

});

Visit http://localhost:8000/sorted-users. You’ll see the users ordered alphabetically by name: John Doe, Jane Smith, Peter Jones, Sarah Connor. You can use ‘desc’ for descending (Z-A) order.

Limit the Results

Sometimes you only want a few records, like the first 3 users.

Route::get('/limited-users', function () {

$users = DB::table('users')->limit(3)->get(); // Get only the first 3 users

return response()->json($users);

});

Visit http://localhost:8000/limited-users. You’ll see the first three users based on their default ID order (usually John, Jane, Peter).

Step 4: More Advanced Query Builder Tricks

Let’s explore some even cooler features of the Query Builder!

Conditional Logic with when()

The when() method is super handy for building queries that change based on certain conditions (like if a user provides a specific search filter from a form).

Open the routes/web.php file in your code editor. Add the following code block to the routes/web.php file, ideally below your previous routes.

Route::get('/filter-dynamic', function () {

$users = DB::table('users')

->when(request('role'), function ($query, $role) {

// This code only runs IF a 'role' parameter exists in the URL

return $query->where('role', $role);

})

->when(request('status'), function ($query, $status) {

// This code only runs IF a 'status' parameter exists in the URL

return $query->where('status', $status);

})

->get();

return response()->json($users);

});

How to test this:

• Visit http://localhost:8000/filter-dynamic (no filters, so it returns all users).

• Visit http://localhost:8000/filter-dynamic?role=admin (now it only returns “John Doe”).

• Visit http://localhost:8000/filter-dynamic?status=inactive (now it only returns “Sarah Connor”).

• Visit http://localhost:8000/filter-dynamic?role=user&status=active (now it returns only “Jane Smith”).

See how flexible that when() method is?

Joining Tables

Databases are powerful because you can link information across different tables. For example, a user might have many orders.

First, let’s quickly create an orders table.

Run in your terminal:

php artisan make:migration create_orders_table

Open the new migration file (it will be in database/migrations/ and have a timestamp in its name). Find the up() method and add this code inside:

// In database/migrations/YYYY_MM_DD_HHMMSS_create_orders_table.php

use Illuminate\Database\Migrations\Migration;

use Illuminate\Database\Schema\Blueprint;

use Illuminate\Support\Facades\Schema;

return new class extends Migration

{

public function up(): void

{

Schema::create('orders', function (Blueprint $table) {

$table->id();

$table->unsignedBigInteger('user_id'); // This will link to our users table

$table->string('order_details'); // Changed to details for more descriptive data

$table->string('order_date');

$table->timestamps();

// Add a foreign key constraint for good practice (optional for this demo, but good to know)

$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');

});

}

public function down(): void

{

Schema::dropIfExists('orders');

}

};

Now, run the migration to create the table:

php artisan migrate

Let’s add some sample orders in Tinker (just like we added users):

php artisan tinker

Inside Tinker:

// Assuming John Doe is ID 1, Jane Smith is ID 2

DB::table('orders')->insert([

'user_id' => 1,

'order_details' => 'Laptop and Mouse',

'order_date' => '2024-06-20',

'created_at' => now(),

'updated_at' => now(),

]);

DB::table('orders')->insert([

'user_id' => 2,

'order_details' => 'Keyboard',

'order_date' => '2024-06-22',

'created_at' => now(),

'updated_at' => now(),

]);

DB::table('orders')->insert([

'user_id' => 1,

'order_details' => 'Monitor',

'order_date' => '2024-06-23',

'created_at' => now(),

'updated_at' => now(),

]);

Exit tinker with this code as before: exit;

After exiting, don’t forget to run this command: php artisan serve.

Now, we can use join() to fetch user names along with their order details and dates!

In routes/web.php:

Route::get('/user-orders', function () {

$usersWithOrders = DB::table('users')

->join('orders', 'users.id', '=', 'orders.user_id') // Join 'users' and 'orders' tables

->select('users.name', 'orders.order_details', 'orders.order_date') // Select specific columns from both

->orderBy('users.name', 'asc') // Order by user name for clarity

->get();

return response()->json($usersWithOrders);

});

Visit http://localhost:8000/user-orders. You should see a list showing John Doe’s two orders and Jane Smith’s one order, linking the user’s name to their specific order details.

Aggregates

Need to count records, get the average, or find the maximum value? Aggregate functions are your friends!

Route::get('/user-count', function () {

$count = DB::table('users')->count(); // Get the total number of users

return "Total users: " . $count;

});

Visit http://localhost:8000/user-count and you’ll see “Total users: 4” (because we added four users!).

Raw Expressions

Sometimes you need to use a function specific to your database (like SQLite’s strftime for date formatting) or perform more complex SQL that Query Builder’s helper methods don’t directly cover. That’s where DB::raw() comes in.

Let’s find out how many users registered each year (even though all ours are recent, this shows the concept).

Route::get('/registration-years', function () {

$registrations = DB::table('users')

// Use DB::raw to get the year from the 'created_at' timestamp

->select(DB::raw('strftime("%Y", created_at) as registration_year'), DB::raw('COUNT(*) as user_count'))

->groupBy('registration_year') // Group results by year

->get();

return response()->json($registrations);

});

Visit http://localhost:8000/registration-years. You'll see an output like:

[

{

"registration_year": "2024", // Or whatever current year is

"user_count": 4

}

]

If you had users from different years, you’d see multiple entries here, each with their respective counts.

Step 5: Query Scopes for Reusability

As your application grows, you’ll often find yourself writing the same where() clauses or sets of conditions over and over. Query Scopes let you define these common queries in one place and reuse them easily.

Let’s add a scope to our User model.

Open the file app/Models/User.php. Inside the User class, add these methods:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;

use Illuminate\Foundation\Auth\User as Authenticatable;

use Illuminate\Notifications\Notifiable;

use Laravel\Sanctum\HasApiTokens;

class User extends Authenticatable

{

use HasApiTokens, HasFactory, Notifiable;

/**

* The attributes that are mass assignable.

*

* @var array<int, string>

*/

protected $fillable = [

'name',

'email',

'password',

'role', // Make sure to add 'role' and 'status' here!

'status',

];

/**

* The attributes that should be hidden for serialization.

*

* @var array<int, string>

*/

protected $hidden = [

'password',

'remember_token',

];

/**

* The attributes that should be cast.

*

* @var array<string, string>

*/

protected $casts = [

'email_verified_at' => 'datetime',

'password' => 'hashed',

];

/**

* Scope a query to only include active users.

*

* @param \Illuminate\Database\Eloquent\Builder $query

* @return \Illuminate\Database\Eloquent\Builder

*/

public function scopeActive($query)

{

return $query->where('status', 'active');

}

/**

* Scope a query to only include users of a given role.

*

* @param \Illuminate\Database\Eloquent\Builder $query

* @param string $role

* @return \Illuminate\Database\Eloquent::Builder

*/

public function scopeByRole($query, $role)

{

return $query->where('role', $role);

}

}

Now, you can use these scopes in your routes (or anywhere else you query User models) like this:

In routes/web.php:

use App\Models\User; // Make sure to use your User model!

Route::get('/active-users-scope', function () {

// This will now use the 'scopeActive' method we defined

$users = User::active()->get();

return response()->json($users);

});

Route::get('/admins-by-scope', function () {

// This will use 'scopeByRole' and pass 'admin' as the role

$admins = User::byRole('admin')->get();

return response()->json($admins);

});

Visit http://localhost:8000/active-users-scope. You should see John Doe, Jane Smith, and Peter Jones, but not Sarah Connor (who is inactive).

Visit http://localhost:8000/admins-by-scope. You should see only John Doe.

Pretty neat, right? You’ve created reusable query parts that make your code cleaner and easier to maintain!

Level Up Your Laravel Query Builder Skills on Cloudways!

Harness the Full Potential of Laravel Query Builder with Cloudways.

TRY NOW

Wrapping Up

By now, you’ve seen how Laravel’s Query Builder makes working with databases feel a lot less intimidating. From setting up SQLite and adding test data to writing queries for filtering, sorting, and joining tables — you’ve covered a solid range of practical use cases.

If you’re feeling confident, try pushing things a bit further. Maybe build a simple CRUD app or start connecting your queries to real frontend views. The more you get your hands dirty, the easier it becomes to write clean, efficient code using Laravel’s tools.