Query Builder

Building complex queries with Query Builder

graph TD A[Query Builder] --> B[Select Columns] A --> C[Where Conditions] A --> D[Join Tables] A --> E[Group By] A --> F[Order By] A --> G[Limit/Offset] B --> H[Execute Query] C --> H D --> H E --> H F --> H G --> H H --> I[Results Array]

Query Builder is a powerful fluent interface for building and executing database queries. This system allows you to build SQL queries programmatically and type-safely.

Query Builder Features:

Fluent Interface: Readable and chainable way to build queries
SQL Injection Protection: Automatic protection against SQL injection with parameter binding
Database Agnostic: Work with different types of databases without changing syntax
Chainable Methods: Ability to chain methods for complex queries
Raw Queries Support: Ability to use raw SQL when needed
Query Caching: Ability to cache query results
Transaction Support: Support for database transactions

Benefits of Using Query Builder:

Security: Automatic protection against SQL injection
Readability: More readable and understandable code
Maintainability: Easier to maintain and modify
Flexibility: Ability to build dynamic queries
Type Safety: Better than raw SQL strings
Testing: Easier to test queries

Difference from Eloquent:

Query Builder is used for raw database operations
Eloquent is used for object-oriented database operations
Query Builder is faster but Eloquent is more feature-rich
Query Builder returns results as arrays
Eloquent returns results as Model instances

Common Methods:

<code>select()</code>: Select columns
<code>where()</code>: Filter results
<code>join()</code>: Join tables
<code>groupBy()</code>: Group results
<code>orderBy()</code>: Sort results
<code>limit()</code>: Limit results
<code>having()</code>: Filter grouped results
<code>aggregate()</code>: Aggregate functions (count, sum, avg, etc.)

Examples

Simple Query Builder

<?php

use Illuminate\Support\Facades\DB;

$users = DB::table('users')
    ->where('active', 1)
    ->where('age', '>', 18)
    ->orderBy('name')
    ->get();

// Returns collection of stdClass objects

A simple query with Query Builder that returns active users over 18 years old.

Query with Joins

<?php

$posts = DB::table('posts')
    ->join('users', 'posts.user_id', '=', 'users.id')
    ->join('categories', 'posts.category_id', '=', 'categories.id')
    ->select('posts.*', 'users.name as author_name', 'categories.name as category_name')
    ->where('posts.published', true)
    ->get();

Query with multiple joins to get posts with author and category information.

Aggregate Functions

<?php

// Count
$count = DB::table('users')->count();

// Sum
$total = DB::table('orders')->sum('amount');

// Average
$avg = DB::table('products')->avg('price');

// Max/Min
$maxPrice = DB::table('products')->max('price');
$minPrice = DB::table('products')->min('price');

// Group by with aggregate
$stats = DB::table('orders')
    ->select('user_id', DB::raw('SUM(amount) as total'))
    ->groupBy('user_id')
    ->get();

Using aggregate functions for calculations and statistics.

Complex Where Conditions

<?php

$users = DB::table('users')
    ->where('active', 1)
    ->where(function ($query) {
        $query->where('email', 'like', '%@example.com')
              ->orWhere('email', 'like', '%@test.com');
    })
    ->whereBetween('age', [18, 65])
    ->whereIn('role', ['admin', 'moderator'])
    ->whereNotNull('email_verified_at')
    ->get();

Query with complex where conditions including nested queries and multiple operators.

Subqueries

<?php

$users = DB::table('users')
    ->select('*')
    ->whereExists(function ($query) {
        $query->select(DB::raw(1))
              ->from('orders')
              ->whereColumn('orders.user_id', 'users.id');
    })
    ->get();

// Or with subquery in select
$users = DB::table('users')
    ->select('*', DB::raw('(SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) as order_count'))
    ->get();

Using subqueries for complex filtering and calculations.

Raw Queries

<?php

// Raw in select
$users = DB::table('users')
    ->select(DB::raw('COUNT(*) as user_count, status'))
    ->groupBy('status')
    ->get();

// Raw where
$users = DB::table('users')
    ->whereRaw('age > ? AND age < ?', [18, 65])
    ->get();

// Full raw query
$users = DB::select('SELECT * FROM users WHERE active = ?', [1]);

// Raw with bindings
$users = DB::select(
    'SELECT * FROM users WHERE email LIKE ? AND age > ?',
    ['%@example.com', 18]
);

Using raw SQL for complex queries not possible with Query Builder.

Query Caching

<?php

// Cache query result
$users = DB::table('users')
    ->where('active', 1)
    ->remember(60) // Cache for 60 seconds
    ->get();

// Cache with custom key
$users = DB::table('users')
    ->where('active', 1)
    ->remember(60, 'active_users')
    ->get();

// Cache forever
$users = DB::table('users')
    ->where('active', 1)
    ->rememberForever('active_users')
    ->get();

Caching query results to improve performance.

Use Cases

Building dynamic queries based on user input
Complex joins and subqueries
Aggregate calculations and statistics
Raw SQL operations not possible with Eloquent
Performance-critical queries
Database operations without Model overhead

Common Mistakes

Using raw SQL without parameter binding causing SQL injection
Forgetting to use where() for filtering
Using get() for large datasets causing memory problems
Forgetting indexes for frequently queried columns
Using select('*') instead of selecting specific columns
Nested queries without proper indexing

Best Practices

Always use parameter binding for raw SQL
Use select() for selecting specific columns
Use indexes for frequently queried columns
Use chunking for large datasets
Use query caching for frequently accessed data
Use transactions for multiple operations
Put query logic in scopes or repositories

Edge Cases

Queries with very large datasets
Complex joins with multiple tables
Subqueries with circular dependencies
Raw queries with database-specific syntax
Queries with multiple database connections
Dynamic queries with variable table names

Performance Notes

Use select() for selecting specific columns
Use indexes for frequently queried columns
Use query caching for frequently accessed data
Use chunking for large datasets
Use explain() to analyze query performance
Use eager loading for relationships

Security Notes

Always use parameter binding for user input
Use raw SQL with caution
Ensure user input is validated
Use DB::raw() for trusted data
Ensure table names are not from user input
Use prepared statements

Interview Points

What is Query Builder and what are its benefits?
What is the difference between Query Builder and Eloquent?
How can you prevent SQL injection?
How can you use subquery in Query Builder?
What is the difference between where() and whereRaw()?
How can you cache a query?

Version Notes

Laravel 11.x: Improved performance in Query Builder
Laravel 11.x: Better support for subqueries
Laravel 10.x: Improved query caching
Laravel 9.x: Improved join methods