The Laravel Query Builder is a powerful, expressive tool that allows developers to interact with their database using PHP syntax rather than writing raw SQL. It provides a fluent, convenient interface for creating and running database queries, making your code cleaner, more readable, and less vulnerable to SQL injection attacks. This guide will walk you through everything you need to know, from the basics of fetching data to performing complex joins and updates.

Table of Contents

1. Understanding the Laravel Query Builder

The Laravel Query Builder is an essential tool for any Laravel developer. It acts as an abstraction layer between your PHP code and the raw SQL queries sent to the database.

What is Query Builder?

At its core, the Laravel DB Query Builder is a set of PHP methods that map directly to standard SQL operations. Instead of writing:

SELECT * FROM users WHERE status = 'active' ORDER BY created_at DESC;

You write a fluent PHP chain:

DB::table('users')->where('status', 'active')->orderBy('created_at', 'desc')->get();

Eloquent vs Query Builder in Laravel: The Key Difference

This is a common point of confusion for beginners.

The fundamental difference between Eloquent and Query Builder is that Eloquent works with Models, while the Query Builder works directly with the database via the DB facade.

Getting Started: The DB Facade

To use the Laravel Query Builder, you primarily rely on the static DB facade, specifically the ::table() method, which specifies the table you want to query.

Syntax:

use Illuminate\Support\Facades\DB;

// Start a query on the 'users' table
$query = DB::table('users');

2. Basic Data Retrieval: The SELECT Operations

Retrieving data is the most fundamental operation. We'll use a conceptual products table for our examples.

Example Table: products

(i)- Selecting All Columns (->get())

The ->get() method executes the query and retrieves all results as an array of stdClass objects.

Logic: Get everything from the products table.

Syntax:

$allProducts = DB::table('products')->get();
/*
[
    { id: 1, name: 'Laptop Pro', ... },
    { id: 2, name: 'Office Desk', ... },
    ...
]
*

(ii)- Specific Column Selection (->select())

Use the ->select() method to explicitly choose which columns you want to retrieve. This improves performance as you fetch less data.

Logic: Get only the name and price of all products.

Syntax:

$productInfo = DB::table('products')
                 ->select('name', 'price')
                 ->get();
/*
[
    { name: 'Laptop Pro', price: 1200.00 },
    { name: 'Office Desk', price: 250.00 },
    ...
]
*/

You can also use an alias with ->select() for columns to rename them in the result set:

$productInfo = DB::table('products')
                 ->select('name', 'price AS current_price')
                 ->get();
// The price column will be named 'current_price' in the results.

(iii)- Retrieving a Single Row or Value

Sometimes you only need one item, like when querying by ID.

• ->first(): Retrieves the first row found by the query as a single stdClass object.

  • Logic: Get the details for the product with id = 1.

  PHP

  $product = DB::table('products')->where('id', 1)->first();
  // Returns { id: 1, name: 'Laptop Pro', price: 1200.00, ... }
  

• ->value('column'): Retrieves a single value from the first row of the query.

  • Logic: Get just the name of the product with id = 1.

  PHP

  $productName = DB::table('products')->where('id', 1)->value('name');
  // Returns 'Laptop Pro' (a string)
  

(iv)- The ->toArray() and ->toSql() Methods

These methods are extremely useful for debugging and format conversion.

3. Conditional Queries: The WHERE Clauses

The ->where() method is used to filter records based on specific conditions, corresponding to the WHERE clause in SQL.

(i)- Simple WHERE

The basic ->where() method takes three arguments: the column name, an operator (optional, defaults to =), and the value.

Logic: Find products where the price is less than or equal to 500.

Syntax:

$affordable = DB::table('products')
                ->where('price', '<=', 500)
                ->get();

(ii)- The WHERE NOT Clause

While there isn't a direct single method called ->whereNot(), you achieve the equivalent logic using operators or the ->whereNotIn() (covered next) method.

Logic: Find products that are not in the 'Electronics' category.

Syntax (using operator):

$nonElectronics = DB::table('products')
                    ->where('category', '!=', 'Electronics')
                    ->get();

(iii)- ->whereIn() and ->whereNotIn()

These methods are used to check if a column's value is present (or not present) in an array of values.

(iv)- ->whereNull() and ->whereNotNull()

Used to check for columns with a NULL value.

(v)- Using ->whereBetween()

This handy method selects records where a column's value falls between two values (inclusive).

Logic: Find products with a price between 200 and 1000.

Syntax:

$midRange = DB::table('products')
              ->whereBetween('price', [200, 1000])
              ->get();

(vi)- Combining Conditions: ->orWhere()

The ->orWhere() method is used to combine conditions using the logical OR operator in SQL.

Logic: Find products that are either 'Electronics' OR have a price greater than 1000.

Syntax:

$specialItems = DB::table('products')
                  ->where('category', 'Electronics') // AND
                  ->orWhere('price', '>', 1000)      // OR price > 1000
                  ->get();

For more complex groupings (e.g., WHERE (conditionA AND conditionB) OR conditionC), you should pass a Closure to the ->where() method:

$complexQuery = DB::table('products')
    ->where('stock', '>', 10) // WHERE stock > 10 AND
    ->where(function ($query) {
        $query->where('category', 'Electronics') // (category = 'Electronics'
              ->orWhere('price', '>', 1000);   // OR price > 1000)
    })
    ->get();

(vii)- The ->whereLike() and ->where('column', 'like', 'value')

To perform pattern matching, you use the SQL LIKE operator, often combined with the % wildcard.

Logic: Find products whose name LIKE starts with 'Laptop'.

Syntax (Standard):

$laptops = DB::table('products')
             ->where('name', 'LIKE', 'Laptop%')
             ->get();

Note: While Laravel doesn't have a specific ->whereLike() method in the core Query Builder, using 'LIKE' as the operator is the standard, secure way to implement it.

4. Data Manipulation: INSERT, UPDATE, and DELETE

The Query Builder isn't just for reading data; it's also used to change data.

(i)- Inserting New Records (->insert())

The ->insert() method takes an associative array where keys are column names and values are the data to be inserted.

Logic: Add a new product, 'Mechanical Keyboard'.

Syntax:

DB::table('products')->insert([
    'name' => 'Mechanical Keyboard',
    'price' => 150.00,
    'stock' => 75,
    'category' => 'Electronics'
]);
// Inserts one row.

(ii)- Updating Existing Records (->update())

The ->update() method is paired with a ->where() clause to specify which records to modify.

Logic: Update the price of the 'USB Drive' to $20.00.

Syntax:

$updatedRows = DB::table('products')
                 ->where('name', 'USB Drive')
                 ->update([
                     'price' => 20.00,
                     'stock' => 90 // Optional: update multiple columns
                 ]);
// $updatedRows will contain the number of rows affected (e.g., 1).

(iii)- ->increment() and ->decrement()

These are specialized update methods to quickly increase or decrease a numeric column's value.

Logic: Increase the stock of the 'Laptop Pro' by 10 units.

Syntax:

DB::table('products')
  ->where('name', 'Laptop Pro')
  ->increment('stock', 10); // Increases 'stock' by 10.
// Use ->decrement('stock', 5) to reduce stock by 5.

(iv)- Deleting Records (->delete())

The ->delete() method removes records from the table, also typically used with a ->where() clause.

Logic: Remove the 'Office Desk' product.

Syntax:

$deletedRows = DB::table('products')
                 ->where('name', 'Office Desk')
                 ->delete();
// $deletedRows will contain the number of rows deleted.

Danger! If you call DB::table('products')->delete(); without a ->where() clause, all records in the products table will be deleted.

5. Advanced Querying Techniques

(i)- Limiting and Ordering Results

• ->limit(): Used to restrict the number of records returned.

• ->orderBy(): Sorts the results by a specific column.

Logic: Get the 5 most expensive products.

Syntax:

$top5 = DB::table('products')
          ->orderBy('price', 'desc') // 'desc' for descending, 'asc' for ascending
          ->limit(5)
          ->get();

(ii)- Aggregation Methods

These methods calculate a single value based on the results of the query.

(iii)- Grouping Results: ->groupBy() and ->having()

• ->groupBy('col'): Groups rows that have the same values in specified columns into summary rows.

• ->having(): Filters groups (similar to WHERE but applied after ->groupBy()).

Logic: Find the total number of items in stock for each category.

Syntax:

$stockByCategory = DB::table('products')
    ->select('category', DB::raw('SUM(stock) as total_stock')) // Use DB::raw() for SUM
    ->groupBy('category')
    ->get();
/*
[
    { category: 'Electronics', total_stock: 225 },
    { category: 'Furniture', total_stock: 20 },
]
*/

Using ->having(): Find categories where the total stock is over 50.

$highStockCategories = DB::table('products')
    ->select('category', DB::raw('SUM(stock) as total_stock'))
    ->groupBy('category')
    ->having('total_stock', '>', 50)
    ->get();
// This would exclude 'Furniture' from the previous example.

(iv)- Checking for Existence: ->exists() and ->doesntExist()

These methods are extremely useful for performance, as they only execute a fast SELECT 1 query and return a simple boolean (check if record exists).

(v)- ->distinct() Selection

The ->distinct() method ensures that the results returned only contain unique values for the selected columns.

Logic: Get a list of all unique categories present in the products table.

Syntax:

$uniqueCategories = DB::table('products')
                      ->select('category')
                      ->distinct()
                      ->get();
// [ { category: 'Electronics' }, { category: 'Furniture' } ]

(vi)- ->chunk() for Large Datasets

When dealing with thousands or millions of records, fetching everything at once can cause memory issues. The ->chunk() method retrieves a "chunk" of results at a time and feeds them into a Closure for processing.

Logic: Process 100 products at a time without overloading memory.

Syntax:

DB::table('products')->where('status', 'active')->chunk(100, function ($products) {
    foreach ($products as $product) {
        // Process the product here, e.g., send an email, update a field.
        // You get 100 products at a time until all are processed.
    }
});

6. Working with Raw Expressions

Sometimes, the Query Builder's fluent methods don't support a specific, complex SQL feature. For these cases, you use Raw SQL.

(i)- DB::raw() and ->rawSql()

The DB::raw() method allows you to inject a raw SQL string into your query securely (Laravel still performs some basic binding).

(ii)- Complex Queries with ->union()

The ->union() method appends the results of another query to the current query.

Logic: Combine a list of product names and a list of category names into a single result set.

Syntax:

// First query: Get all product names
$products = DB::table('products')->select('name AS title');

// Second query: Get all category names
$categories = DB::table('categories')->select('category_name AS title');

// Combine the two queries using UNION
$combinedTitles = $products->union($categories)->get();

(iii)- ->subQuery() Usage

You can use the Query Builder to define a query and then use its result as a subquery within another statement, typically for SELECT or WHERE clauses.

Logic: Find products that are in the category with the highest id.

Syntax:

// 1. Define the subquery to find the MAX category ID
$maxIdSubquery = DB::table('categories')->select(DB::raw('MAX(id)'))->toSql();

// 2. Use the subquery in the main query
$latestProducts = DB::table('products')
                    // This is where a subquery would be used securely
                    ->where('category_id', 'IN', DB::raw("({$maxIdSubquery})"))
                    ->get();

7. Paging Results: ->paginate()

The ->paginate() method is an essential tool for displaying large result sets across multiple pages (e.g., a list of products). It automatically handles the LIMIT and OFFSET clauses and provides the necessary metadata for generating pagination links.

Logic: Display 15 products per page.

Syntax:

// In a Controller:
$products = DB::table('products')->paginate(15);

// Pass $products to your Blade View.
// In the View, you can display the results and the links:
// {{ $products->links() }}

This method automatically builds the URL with the necessary query string parameters (like ?page=2).

Conclusion

The Laravel Query Builder is a robust and flexible tool that simplifies database interactions by offering a fluent, expressive, and safe alternative to raw SQL. By mastering methods like ->where(), ->join(), ->select(), and the various aggregation methods, you gain significant control over your database queries. While Eloquent is perfect for Model-centric operations, the DB Query Builder provides necessary flexibility and performance for complex, non-Model related, or raw-SQL style queries.

This guide provides a strong foundation for any absolute beginner to confidently start building powerful queries in Laravel.