Exploring Query Builder and Eloquent ORM: Complete Guide

One of the most significant elements of any website or web app is its data, and interacting with it involves complex database queries. With Laravel, you get a host of built-in tools, including Query Builder and Eloquent ORM. They streamline database operations, saving you time and keeping your code clean.

In this blog, I’ll explain what these two tools are and how the Laravel development services use them to perform various database queries.

What is Laravel’s Query Builder?

Laravel’s Query Builder is a powerful and flexible tool for interacting with your database. It provides a fluent interface for building and optimizing Laravel database queries. It allows you to construct SQL queries programmatically without writing raw SQL. This improves the readability and maintainability of your code.

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 (Object-Relational Mapping) is a powerful and elegant ActiveRecord implementation for working with databases. It acts as a bridge between your applications and the underlying relational database. Eloquent makes it easy to interact with your database using objects, making interactions more intuitive and efficient.

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, Laravel developers can help you build database-driven applications efficiently. It promotes clean code, reduces boilerplate development, and simplifies complex database interactions.

How Do You Perform Various Database Operations in Laravel Query Builder?

Query builder allows you to manage databases using various operations. 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 our Laravel experts.

How Do You Perform Various Database Operations in Laravel Eloquent ORM?

Eloquent ORM offers various database operations, from basic operations to eager loading and model event queries. 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.

These concepts are fundamental to working with Eloquent ORM in Laravel. By using these core concepts, our Laravel development company manages data within your Laravel applications.

FAQs About Exploring Query Builder and Eloquent ORM

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.

By choosing the right technique and hiring Laravel developers, you can ensure an effective solution to interact with databases within your Laravel applications.