Using Laravel’s Query Builder & Eloquent ORM to Optimize Database Queries

Every developer working with a database faces a fundamental choice: how to interact with it efficiently and securely. For that, Laravel offers two powerful tools: the Query Builder and Eloquent ORM.

The Query Builder offers a fluent, programmatic interface for creating database-agnostic queries, granting precise control. Eloquent ORM implements the active record pattern. So you can interact with your database using intuitive, object-oriented models.

In this blog, we’ll discuss both Laravel’s Query Builder and Eloquent ORM to perform various database operations. Let’s begin.

What is Laravel’s Query Builder?

Laravel’s Query Builder provides a clean, fluent interface for optimizing database queries. It is a programmatic layer that allows you to construct SQL statements using PHP methods. That offers a more readable and secure alternative to writing raw SQL.

Think of it as a powerful translator. Instead of manually writing SELECT * FROM users WHERE active = 1, you use a chain of expressive methods:

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

A key advantage of the Query Builder is its built-in protection against SQL injection attacks, as it automatically parameterizes user inputs. Plus with it, you can handle database compatibility, making it simpler to support different database systems.

Key Features of Laravel’s Query Builder

Laravel’s Query Builder offers several key features that enhance database interaction in your applications:

• Fluent Interface: Laravel’s Query Builder uses a fluent, chainable interface, which makes building of SQL queries clean and readable. Methods can be chained together to build complex queries step-by-step.
• SQL Injection Prevention: The Query Builder automatically escapes input values, significantly reducing the risk of SQL injection attacks. This makes it safer to build SQL queries than manually writing raw SQL.
• Database Agnostic: The Query Builder works seamlessly with various database systems Laravel supports. You can write queries without worrying about specific database syntax variations.
• Inserts, Updates, and Deletes: The Query Builder simplifies the process of performing database operations with methods like insert, update, and delete.
• Grouping and Aggregation: It facilitates grouping results and performing aggregate functions like SUM, COUNT, AVG, etc., for data analysis.
• Filtering Data: It offers a variety of methods for filtering data, including where, orWhere, whereIn, whereNull, whereDate, etc., to filter data.
• Chunking and Lazy Loading: For large datasets, you can retrieve results in smaller chunks or lazily load them on demand. This enhances performance and memory usage.
• Raw Expressions Support: While it eliminates the need for raw SQL, the Query Builder allows using raw expressions when necessary. That gives you the flexibility to insert raw SQL snippets into your queries.
• Transaction Management: It supports database transactions with methods like beginTransaction, commit, and rollBack. These methods enable you to safely perform multiple database operations in a single transaction.
• Debugging: The toSql method allows you to get the raw SQL query string for debugging purposes. Additionally, you can use the getBindings method to retrieve the query bindings.

These features empower you to write secure and maintainable code to manage database interactions in Laravel applications.

What is Laravel Eloquent ORM?

Laravel Eloquent ORM is an advanced, object-oriented implementation of the Active Record pattern. It provides an elegant way to interact with your database. Unlike the Query Builder which works with tables, Eloquent uses “models” to represent your application’s data.

Each Eloquent model corresponds to a single database table. For instance, a User model maps to a users table. This abstraction allows you to interact with your data as if they were native PHP objects.

// Creating a new user is as intuitive as creating a new object.
$user = new User;
$user->name = 'John Doe';
$user->email = 'john@example.com';
$user->save();

// Retrieving data feels like a simple method call.
$activeUsers = User::where('active', 1)->get();

It can simplify complex relationships. Defining and managing connections like “a User hasMany Posts” becomes a declarative task. Eloquent handles the complex SQL joins behind the scenes.

Key Features of Laravel’s Eloquent ORM

• ActiveRecord Implementation: Eloquent provides a straightforward ActiveRecord implementation. That means it offers a corresponding model for each database table, so you can manage data easily.
• Relationships: It allows you to define relationships between database tables, such as one-to-one, one-to-many, and polymorphic relationships. This allows you to easily access related data.
• Fluent Query Builder: Laravel’s Query Builder allows you to build complex database queries using an expressive syntax.
• Eager Loading: Eloquent supports the eager loading of relationships to optimize query performance by reducing the number of queries executed.
• Mutators and Accessors: It allows you to define Eloquent Mutators to alter data before saving or retrieving it from the database. This is useful for data manipulation and formatting.
• Mass Assignment Protection: Eloquent provides mass assignment protection to prevent unintentional updates to your models. You can define guarded properties to control which attributes are mass-assignable.
• Timestamps: It automatically manages created_at and updated_at timestamps, tracking when records are created and updated.
• Soft Deletes: Soft deletes allow you to “delete” records without removing them from the database. The soft deleted records can also be easily restored.
• Casting Attributes: These attributes allow you to cast common data types, such as arrays, dates, and collections. It ensures that attributes are returned in the desired format.
• Serialization: Serialization can be used to convert Eloquent models to arrays or JSON, making it simple to build APIs and work with frontend frameworks
• Event Observers: It supports event observers, allowing you to hook into various events of a model, such as creating, updating, and deleting.
• Global Scopes: You can define and add constraints to all queries executed on a model using global scopes. That provides a convenient way to apply common query conditions.

By leveraging these features, experts can help you build database-driven applications efficiently. It promotes clean code, reduces boilerplate development, and simplifies complex database interactions.

Database Operations in Laravel Query Builder

Query builder allows you to manage databases using various operations. Explore the power of Laravel collections, even if you’re using Eloquent or Query Builder results. Here’s a breakdown of how you can perform database operations using Query Builder, with examples for each:

Selects

Specifies the columns to retrieve from a database table. For example, if you want to select specific columns, you can use the below query:

$usernames = DB::table('users')->select('name')->get(); // Get usernames only

It selects only the name column from the users table and returns a collection containing just the usernames.

Joins

Combines rows from two or more tables based on a related column between them. For example, if you want to combine data from multiple pages, you can use:

$orders = DB::table('orders')
->join('users', 'orders.user_id', '=', 'users.id')
->select('orders.id', 'users.name', 'orders.created_at')
->get(); // Get orders with user names

This joins the orders and users tables based on the user_id column. It then selects specific columns from both tables and retrieves the joined data as a collection.

Advanced Wheres

To add more complex conditions to a query, such as Where clauses or nested conditions. For example, you want to perform complex filtering with multiple conditions:

$activeAdmins = DB::table('users')
->where('role', 'admin')
->where('active', true)
->get(); // Get active users with admin role

We can apply multiple where clauses to filter the results. Here, it retrieves users whose role is admin, and the active flag is true.

Aggregates

It performs operations on a set of values to return a single scalar value. For example, performing calculations on groups of data:

$totalPostsPerUser = DB::table('posts')
->select('user_id', DB::raw('count(*) AS post_count'))
->groupBy('user_id')
->get(); // Get post count per user

Here, we use the count aggregate function to calculate the number of posts for each user. It groups the results by the user_id and retrieves the user ID along with the post count.

Raw Expressions

Allows raw SQL in a query, mainly useful for more complex operations. For example, executing a custom SQL statement directly:

$userWithFullName = DB::table('users')
->select(DB::raw('CONCAT(first_name, " ", last_name) AS full_name'))
->where('id', 1)
->first(); // Get the user full name as a single column

This utilizes DB::raw to construct a custom SQL expression. That concatenates first_name and last_name into a new column named full_name. It then retrieves the user with ID 1 and returns a single object containing only the full name.

Inserts

Used for adding a new record to a database table. For example, to add a new record, you can use the following:

$data = [
'name' => 'John Doe',
'email' => 'john.doe@example.com',
'created_at' => now(), // Use Laravel's `now()` helper
];
DB::table('users')->insert($data); // Insert a new user

It will create a new record in the users table. The $data array contains the values for each column, and now() provides the current timestamp for the created_at field.

Updates

It is used to modify existing records in a database table. For example, to modify the existing record, you can use the following:

$data = [
'email' => 'new_email@example.com',
];
DB::table('users')
->where('id', 1)
->update($data); // Update user with ID 1

This will update the email address of the user with ID 1. Meanwhile, the where clause specifies the record to be modified. The update method applies the changes defined in the $data array.

Deletes

Delete is used to remove records from a database table. For example, to remove a record from the table, you can use the below query:

DB::table('posts')
->where('created_at', '<', now()->subWeek()) // Delete posts older than a week
->delete();

Here, we will delete posts that were created a week ago by using now()->subWeek(). The where clause filters the results, and the delete method removes the matching records.

Unions

It combines the result sets of two or more SELECT statements. For example, to combine results from multiple queries, you can do it as below:

$popularPosts = DB::table('posts')
->where('views', '>', 1000)
->get();
$recentPosts = DB::table('posts')
->orderBy('created_at', 'desc')
->limit(5)
->get();
$allPosts = $popularPosts->union($recentPosts); // Combine popular and recent posts

The above example combines results from two separate queries. It retrieves popular posts with views more than 1000 and orders recent posts by creation date with a limit of 5. Then, it uses the union method to combine the results from both queries into a single collection.

If you need help with executing these queries, you can consult with expert Laravel development services.

Database Operations in Laravel Eloquent ORM

Eloquent ORM offers various database operations, from basic operations to eager loading and model event queries. Implement Laravel Simple Pagination to fetch and display large datasets efficiently in both Query Builder and Eloquent operations. Here are some common operations you can perform using Laravel Eloquent ORM.

Retrieving Data

This operation is used to fetch records from the database. For example, to fetch all records from the database, use:

$users = User::all();

This will retrieve all records from the users table.

Creating New Records

It is used to create new records in your database. For example, you can add a new record using the save method.

$user = new User;
$user->name = 'John Doe';
$user->email = 'john.doe@example.com';
$user->save();

By running the above command, you will create a new instance of the User model with the attributes name and email. Then, the data is saved to the database using the save method.

Updating Existing Records

When you need to update a record, for example, update the name of the user using save.

$user = User::find(1);
$user->name = 'Jane Doe';
$user->save(); // Updates the user with ID 1

This code will retrieve a User record with the primary key 1, change its name attribute, and save the changes.

Deleting Records

It is used to delete a record from the database. For example, you can delete a record using a User ID.

$user = User::find(2);
$user->delete(); // Deletes the user with ID 2

The above query will fetch the user with ID 2 and then delete the corresponding record from the database using the delete method.

Relationships

Eloquent provides a way to define relationships between models, allowing you to retrieve related data easily. For example, to define a One-to-One relation, you can use the below query:

// User model
public function profile()
{
return $this->hasOne(Profile::class);
}

The code above defines a one-to-one relationship between the User and Profile models. The hasOne method indicates that a user can have one profile associated with it.

Eager Loading

A technique to reduce the number of queries executed by loading related models along with the main model.

$users = User::with('posts')->get(); // Fetch users with their posts in one query

Using eager loading, it will retrieve Users and their related posts in a single query. This optimizes performance by reducing the number of database calls required.

Model Events

By using model events, you can define methods in your model to handle events like creating, created, etc. Here is an example:

// User model
public static function boot()
{
parent::boot();
static::creating(function($user) {
// Perform actions before creating the user (e.g., hash password)
$user->password = Hash::make($user->password);
});
}

With the above code, you will be able to create a model event. In this example, the creating event is used to hash a password in Laravel before saving it to the database. This ensures secure password storage.

Scopes

The scopes allow you to define common sets of query constraints that you can reuse throughout your application. Here is an example of creating a scope within the model.

// User model
public function scopeActive($query)
{
return $query->where('active', true);
}
$activeUsers = User::active()->get(); // Fetches active users using the scope

Here, the active scope filters the results to only include users where the active field is true. Then, you can use this scope in your queries for cleaner and reusable code.

Take your database logic to the next level with advanced Laravel Eloquent techniques like eager loading, global scopes, and accessor/mutator functions. These concepts are fundamental to working with Eloquent ORM in Laravel.

Query Builder vs Eloquent ORM

• When to Use Query Builder: Use Query Builder for complex, raw SQL-like queries, joining multiple tables, or when performance optimization is a priority.
• When to Use Eloquent: Use Eloquent for standard CRUD operations, when working with relationships, or when you need an elegant, object-oriented approach for interacting with your database.

Query Builder is a procedural approach in Laravel that provides a fluent interface to construct SQL queries, offering more flexibility and performance for complex queries. Eloquent, on the other hand, is Laravel’s object-relational mapper (ORM) that enables developers to work with database models as objects, emphasizing simplicity and readability.

Best Practices for Using Query Builder and Eloquent ORM

Using Query Builder and Eloquent ORM effectively in Laravel can enhance your application’s performance and maintainability. Here are five best practices to help you get the most out of these tools:

• Choose the Right Tool for the Task: Use Query Builder for performance-critical or complex SQL queries and Eloquent for simple CRUD operations and model-based logic.
• Optimize Database Queries: Use pagination instead of loading all records at once (paginate() in Eloquent or limit and offset in Query Builder) to reduce memory usage.
• Leverage Relationships in Eloquent: Define relationships (e.g., hasOne, belongsTo) properly to avoid repetitive queries and use with() for eager loading to minimize N+1 query issues.
• Write Raw Queries Sparingly: Use raw SQL queries only when necessary, and always sanitize inputs using bindings to prevent SQL injection attacks.
• Index Your Database: Whether using Eloquent or Query Builder, ensure your database tables are indexed on frequently queried columns to improve query performance.

Adopting these best practices ensures better performance and code readability that can make your database interactions both efficient and robust.

5 Tips to Improve Laravel Query Builder Performance

While Laravel’s Query Builder is inherently efficient, its performance hinges on how you use it. Optimizing your queries can drastically reduce page load times and server resource consumption. Here are five essential tips to ensure your Query Builder operations are as fast as possible.

Select Only the Columns You Need

A common performance pitfall is using get() or first() without specifying columns, which executes a SELECT * query. This forces the database to fetch entire rows, including potentially large TEXT or BLOB fields you don’t need.

Instead of:

$users = DB::table('users')->get(); // Fetches all columns

Use select() to specify columns:

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

This reduces the amount of data transferred from the database to your application, resulting in faster query execution.

Leverage Chunking for Large Datasets

When processing thousands of records (e.g., for a report or data export), loading them all into memory at once can exhaust your PHP memory limit and cause crashes.

Use the chunk() method to process results in manageable batches.

DB::table('orders')->where('fulfilled', false)
   ->chunk(200, function ($orders) {
       foreach ($orders as $order) {
           // Process each batch of 200 orders
       }
   });

This approach keeps memory usage low by working on a small subset of records at a time.

Use Indexes Wisely with where() Clauses

The Query Builder’s where() methods translate directly to SQL WHERE clauses. For these to be fast, the columns involved must be indexed. A query on a non-indexed column forces a full table scan, which is slow on large tables.

• Index columns used frequently in where(), orderBy(), or join conditions.
• Avoid using functions on columns in a where clause (e.g., where(DB::raw('YEAR(created_at)'), 2023)), as this prevents the database from using the index. Use range checks instead.

Prefer Lazy Eager Loading for Conditional Relationships

If you are retrieving models and may need to access their relationships conditionally, avoid the N+1 query problem without always loading the relationship. While more common with Eloquent, this pattern applies when mixing Query Builder and relationships.

Instead of manually looping and querying, use a strategy where you gather IDs and load relationships in a single query.

Example (Avoid N+1):

$posts = DB::table('posts')->get();
$authorIds = $posts->pluck('author_id')->unique();
$authors = DB::table('authors')->whereIn('id', $authorIds)->get();
// Then map authors to posts manually in code

This approach uses two queries instead of potentially dozens.

Utilize Caching for Expensive, Repetitive Queries

If a query is complex and its results don’t change frequently, caching the result is the most significant performance boost you can apply.

Laravel’s cache system integrates seamlessly with the Query Builder.

$topUsers = Cache::remember('top_users', 3600, function () { // Cache for 1 hour
    return DB::table('users')
                ->join('logins', 'users.id', '=', 'logins.user_id')
                ->select('users.name', DB::raw('COUNT(logins.id) as login_count'))
                ->groupBy('users.id')
                ->orderBy('login_count', 'DESC')
                ->limit(10)
                ->get();
});

Subsequent requests will retrieve the data from the fast cache store (like Redis or Memcached) instead of executing the expensive database query.

Conclusion

Laravel’s Query Builder and Eloquent ORM offer powerful tools for interacting with your database. Query Builder provides flexibility and control, while Eloquent simplifies common operations and promotes a more object-oriented approach. The choice between them depends on your specific needs.

Use Query Builder if:

• You want to write complex queries.
• You want more filtered and precise data.
• Your database schema doesn’t match Eloquent models.

Use Eloquent ORM if:

• You want to take advantage of database relationships.
• You want to use Eager loading to reduce queries.
• You have a well-defined database schema.

For better solutions and implementation it is best to hire Laravel developers, who can help you manage your Laravel web application effectively.

FAQs About Exploring Query Builder and Eloquent ORM

Mayur Upadhyay

Mayur Upadhyay is a tech professional with expertise in Shopify, WordPress, Drupal, Frameworks, jQuery, and more. With a proven track record in web development and eCommerce development.