Extracting Raw SQL Queries in Laravel

Laravel Query Builder is a tool in Laravel’s ecosystem that simplifies database interactions and empowers developers to write cleaner, more maintainable code.

It is essentially an interface to create and run database queries and abstracts the underlying SQL syntax, making it easier to build complex queries without writing raw SQL statements.

To get started with Laravel’s Query Builder, you need to understand two basic components: the DB facade and the various query-building methods available.

You can use the DB facade to initiate database connections and access Query Builder, which is a versatile tool for crafting queries. This blog thoroughly explores Laravel Query Builder – basic and advanced query techniques, query scopes, and performance optimization methods.

Getting Started with the Laravel Query Builder

To create a simple query using Laravel’s Query Builder, you can start with a table and then chain methods to define conditions, select columns, order results, and more.

For example, to retrieve all users from a “users” table where their status is “active,” you can write:

$users = DB::table('users')
            where('status', 'active')
            get();

This concise and readable syntax is a hallmark of Laravel’s Query Builder and makes database operations simple, even for those new to web development.

Basic Query Building in Laravel

Query Builder is normally used to create and run database queries and abstract the underlying SQL syntax.

Initiating the Query Builder

To start building a query in Laravel, you typically use the DB facade or an Eloquent model. The DB facade is used when you want to work with raw SQL or perform queries that don’t involve models. For example, to query all records from a “users” table using the DB facade:

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

• If you’re working with an Eloquent model, you can use the model’s query method:

$users = User::query()->get();

Selecting Columns

You can specify which columns to retrieve using the select method:

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

Filtering Results

To filter results, you can use the where method. For example, to retrieve users where the “role” column is equal to “admin,” use the following command:

$admins = DB::table('users')->where('role', 'admin')->get();

You can also chain multiple where conditions together to create more complex queries, like:

$users = DB::table('users')
            ->when($role, function ($query) use ($role) {
                return $query->where('role', $role);
            })
            ->get();

Ordering Results

You can use the orderBy method to order the results:

$users = DB::table('users')->orderBy('name', 'asc')->get();

Limiting and Paginating Results

To limit the number of records returned, you can use the limit method:

$users = DB::table('users')->limit(10)->get();
For pagination, Laravel provides the paginate method:
$users = DB::table('users')->paginate(15);

Executing the Query

To retrieve the results of your query, use the get method. You can also use first to retrieve the first matching record or count to get the count of records that match the query.

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

Advanced Query Techniques in Laravel

Advanced query techniques in Laravel empower developers to handle complex database interactions with elegance and precision. By leveraging these features, you can create highly efficient, readable, and maintainable code, ultimately enhancing the performance and functionality of your Laravel applications.

Eager Loading

Eager loading is a technique used to reduce the number of database queries when retrieving related data. It’s particularly useful when dealing with one-to-many or many-to-many relationships. Instead of executing separate queries for each related record, you can eager load them using the with method. For example:

$posts = Post::with('comments')get();

Subqueries

Laravel’s Query Builder also allows you to create subqueries within your SQL statements. You can use the selectSub method to add a subquery to your query. This is useful for scenarios where you need to perform complex calculations or retrieve data from related tables within your main query.

$users = DB::table('users')
            select('name', DB::raw('(SELECT COUNT(*) FROM posts WHERE posts.user_id = users.id) as post_count'))
            get();

Aggregating Data with Laravel Query Builder

Laravel supports various aggregate functions like count, sum, avg, min, and max. These functions are handy for obtaining statistical information from your data. For instance, you can easily calculate the average rating of products:

$averageRating = Product::avg('rating');

Raw Expressions

Sometimes, you might need to write raw SQL expressions within your queries. Laravel provides the DB::raw method for this purpose. It allows you to inject custom SQL directly into your query while still benefiting from Laravel’s query builder features.

$users = DB::table('users')
            select(DB::raw('YEAR(created_at) as registration_year'))
            groupBy('registration_year')
            get();

Conditional Clauses

Laravel’s Query Builder offers conditional clauses like when, orWhere, and orWhereRaw, allowing you to conditionally add clauses to your query based on specific conditions. This makes it easy to construct dynamic and flexible queries

$query = DB::table('orders')
            where('status', 'shipped')
            when($requestinput('customer_id'), function ($query, $customerId) {
                return $querywhere('customer_id', $customerId);
            })
            get();

Joining Tables with Laravel Query Builder

Laravel supports various types of joins, such as inner joins, left joins, and right joins, through the join method. You can perform complex data retrieval by combining tables based on relationships.

$users = DB::table('users')
            ->join('orders', 'users.id', '=', 'orders.user_id')
            ->select('users.name', 'orders.order_date')
            ->get();

Laravel Query Scopes: Reusable Query Logic

By encapsulating query logic within scopes, you not only make your code more readable but also ensure consistency and maintainability throughout your application.

Additionally, Laravel Query Scopes are incredibly valuable for adhering to the DRY (Don’t Repeat Yourself) principle, as you can reuse the same logic across various parts of your application without duplicating code.

This approach simplifies debugging, reduces errors, and facilitates changes or updates to your query logic in a single, centralized location.

With Laravel Query Scopes, you can encapsulate complex conditions within your models. For example, suppose you often need to retrieve “active” users from your “users” table. In that case, you can define a scope like this within your User model:

public function scopeActive($query)

{

    return $query->where('status', 'active');

}

Once defined, you can easily use this scope in your queries, making your code more expressive and self-explanatory:

$activeUsers = User::active()->get();

Query Scopes can also accept parameters, allowing you to make them even more versatile. For instance, you can create a scope that retrieves users based on their roles:

public function scopeByRole($query, $role)

{

    return $query->where('role', $role);

}

Then, you can use this scope with different roles:

$admins = User::byRole('admin')->get();

$managers = User::byRole('manager')->get();

Optimizing Performance with Laravel Query Builder

Conclusion

In conclusion, Laravel Query Builder empowers developers to write efficient and expressive database queries, offering a robust solution for data retrieval and manipulation. Keep experimenting, exploring, and applying these examples to your own projects, and you’ll find that Laravel Query Builder is a valuable asset in your web development toolkit.

In this blog, we’ve covered a range of practical examples, from basic querying to more advanced techniques, such as joins, subqueries, and conditional clauses.

These examples have demonstrated how Query Builder can make complex database operations accessible, all while maintaining clean, readable code.

Inshal Ali

Inshal is a Content Marketer at Cloudways. With background in computer science, skill of content and a whole lot of creativity, he helps business reach the sky and go beyond through content that speaks the language of their customers. Apart from work, you will see him mostly in some online games or on a football field.

Thankyou for Subscribing Us!