This website uses cookies

Our website, platform and/or any sub domains use cookies to understand how you use our services, and to improve both your experience and our marketing relevance.

ūüĒ䬆Web Growth Summit is here! Learn from industry experts on July 17-18, 2024. REGISTER NOW‚Üí

How to Get the Raw SQL Query From the Laravel Query Builder

Updated on September 26, 2023

5 Min Read
Laravel Query Builder

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.

Level Up Your Laravel Query Builder Skills on Cloudways!

Harness the Full Potential of Laravel Query Builder with Cloudways.

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

Optimization Technique Description
Use Indexes Properly index columns used in WHERE, JOIN, or orderBy clauses to improve query speed.
Limit Column Selection Select only the necessary columns to reduce data transfer and boost query performance.
Eager Loading Prevent N+1 query issues by using eager loading for related data in Eloquent models.
Caching Implement caching mechanisms (e.g., Redis or Memcached) to store frequently used query results.
Database Transactions Wrap multiple queries in a transaction to ensure atomicity and data integrity.
Database Optimization Regularly optimize the database by running maintenance tasks like vacuuming and reindexing.
Avoid N+1 Queries Be cautious of N+1 query problems when fetching related data; use with, has, or whereHas.
Lazy Loading Configure relationships as lazy-loaded when necessary to improve initial query performance.
Database Connection Pooling Use connection pooling to manage and reuse database connections efficiently.
Query Profiling Enable query logging and profiling tools in Laravel to identify and optimize slow queries.

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.

Q) Can I use Laravel Query Builder for inserting, updating, or deleting records?

A) Yes. You can use Laravel Query Builder to perform database operations like inserting, updating, and deleting records by constructing and executing SQL queries.

Q) Are there any limitations to using Laravel Query Builder compared to raw SQL?

A) Yes. Some limitations of Laravel Query Builder compared to raw SQL include potential complexity constraints for very intricate queries, database-specific functionality accessibility, and the need for manual SQL optimization in certain performance-critical scenarios.

Share your opinion in the comment section. COMMENT NOW

Share This Article

×

Get Our Newsletter
Be the first to get the latest updates and tutorials.

Thankyou for Subscribing Us!

×

Webinar: How to Get 100% Scores on Core Web Vitals

Join Joe Williams & Aleksandar Savkovic on 29th of March, 2021.

Do you like what you read?

Get the Latest Updates

Share Your Feedback

Please insert Content

Thank you for your feedback!

Do you like what you read?

Get the Latest Updates

Share Your Feedback

Please insert Content

Thank you for your feedback!

Want to Experience the Cloudways Platform in Its Full Glory?

Take a FREE guided tour of Cloudways and see for yourself how easily you can manage your server & apps on the leading cloud-hosting platform.

Start my tour

CYBER WEEK SAVINGS

  • 0

    Days

  • 0

    Hours

  • 0

    Mints

  • 0

    Sec

GET OFFER

For 4 Months &
40 Free Migrations

For 4 Months &
40 Free Migrations

Upgrade Now