Exploring Laravel 10’s New Query Builder Enhancements

Laravel, known for its elegant syntax and ease of use, has continually refined its core features to improve developer experience. With Laravel 10, one area that has received significant attention is the Query Builder. The new query builder enhancements bring performance improvements, more flexibility, and better readability to writing database queries. In this blog, we’ll explore the new features and enhancements introduced in Laravel 10’s Query Builder.

New Query Builder Enhancements in Laravel 10

New whenNot Method

The when method is commonly used to conditionally apply query constraints. In Laravel 10, the framework introduces a new counterpart: whenNot. This method works similarly but applies the query modification when the given condition is false.

Here’s an example

$users = DB::table('users')
    ->whenNot($active, function ($query) {
        return $query->whereNull('active');
    })
    ->get();

In this example, if $active is false, the query will only retrieve users where the active field is null.

Adding Select Columns with addSelect

Laravel 10 allows you to progressively add columns to your select statements using the new addSelect method without needing to rewrite the entire select clause.

Example:

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

This is useful when building dynamic queries, as you can incrementally add fields to the select list based on your application’s logic.

lazyById for Efficient Large Dataset Processing

Laravel 10 introduces lazyById, which is ideal for handling large datasets with limited memory usage. This method loads records in chunks by primary key, ensuring minimal memory consumption.

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

foreach ($users as $user) {
    // Process each user
}

This method provides better performance than the chunk method for large datasets, especially when working with large ranges of primary keys.

whereFullText Improvements

Laravel 9 introduced the whereFullText method for full-text searches. In Laravel 10, this functionality has been improved with better performance and support for more databases like PostgreSQL and MySQL.

$posts = DB::table('posts')
    ->whereFullText('content', 'Laravel')
    ->get();

This method now offers better cross-database support for full-text searching, providing you with a more robust way of querying large text columns.

Using Subqueries with selectRaw

Although not entirely new, Laravel 10 enhances the flexibility of selectRaw by allowing more complex subqueries. You can now use it to perform calculations or aggregate functions on the database directly within a select statement.

$users = DB::table('users')
    ->selectRaw('(SELECT COUNT(*) FROM posts WHERE posts.user_id = users.id) as post_count')
    ->get();

This improvement makes it easier to write optimized queries that perform calculations directly on the database, reducing the need for post-query data manipulation in PHP.

Enhanced Aggregate Methods

Laravel 10 adds improvements to aggregate methods such as sum, avg, and max. These methods now support conditional aggregation, allowing you to apply conditions directly within the aggregate.

$total = DB::table('orders')
    ->where('status', 'completed')
    ->sum('amount');

This feature gives you more control over how you compute aggregates, enabling you to filter data more efficiently within the query.

New Query Macros

Laravel’s ability to extend the Query Builder through macros is even more powerful in Laravel 10. You can now define custom query methods that you use across different parts of your application.

use Illuminate\Database\Query\Builder;

Builder::macro('whereActive', function () {
    return $this->where('active', 1);
});

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

This enhancement allows you to keep your queries DRY (Don’t Repeat Yourself) by centralizing logic into reusable macros.

Improved Pagination

Laravel 10’s pagination has been enhanced to allow more complex queries and better performance. You can now paginate results for complex queries involving multiple joins, subqueries, and raw expressions more efficiently.

$users = DB::table('users')
    ->join('posts', 'users.id', '=', 'posts.user_id')
    ->select('users.*', DB::raw('count(posts.id) as post_count'))
    ->groupBy('users.id')
    ->paginate(10);

Pagination improvements ensure that large datasets are handled smoothly while maintaining query performance.

toRawSql Method for Debugging

Debugging complex queries has always been tricky. Laravel 10 simplifies this by introducing the toRawSql method, allowing you to output the raw SQL string of the query for debugging purposes.

$query = DB::table('users')->where('active', 1);
dd($query->toRawSql());

This outputs the raw SQL query, complete with bindings, making it easier to debug and optimize queries.

Conditional Relationship Queries

Laravel 10 also improves relationship queries, allowing you to apply conditions directly to relationships in a more readable way.

$users = User::whereHas('posts', function ($query) {
    $query->where('published', true);
})->get();

This enhancement ensures that your Eloquent relationship queries remain clean and performant even when applying complex conditions.

Conclusion:

Laravel 10’s Query Builder enhancements provide developers with more tools to write efficient, readable, and performant database queries. Whether you’re working with large datasets, complex queries, or full-text searches, these new features make it easier to handle your database logic while maintaining the simplicity and elegance Laravel is known for.

By incorporating these enhancements, developers can write more concise and efficient queries, making their applications faster and more maintainable. If you’re upgrading to Laravel 10 or starting a new project, take advantage of these features to improve your query building and database interactions.