Home » Laravel » How to Run Raw SQL Query in Laravel

How to Run Raw SQL Query in Laravel

Laravel uses Eloquent to interact with the database. With it, you can perform quite complex queries. But sometimes you need to use database features that are not supported by Eloquent. In such cases, you can execute a SQL query.

In Laravel, you can execute a simple SQL query and get the result as an array, or insert certain parts of SQL into a query built with Eloquent. In this article, I will show you how to execute raw SQL query in Laravel.


Table of Contents

How to execute a SQL query in Laravel

To execute the examples from this article, let’s create a RunRawQueryCommand command with the following code:

php artisan make:command RunRawQueryCommand

app/Console/Commands/RunRawQueryCommand.php<?php namespace App\Console\Commands; use Illuminate\Console\Command; use Illuminate\Database\Connection; class RunRawQueryCommand extends Command { protected $signature = 'app:run-raw-query'; protected $description = 'Command description'; public function handle(Connection $connection) { // } }

There are two classes you can use in Laravel to directly access a database. These are Illuminate\Database\Connection and Illuminate\Support\Facades\DB. The Connection class can be obtained via Dependency Injection as shown above, and the DB facade is available globally for the entire project.

1. SQL query for data retrieval

To retrieve data, you can use the select() method.

app/Console/Commands/RunRawQueryCommand.php$result = $connection->select( "SELECT * FROM users WHERE true ORDER BY created_at DESC LIMIT 1" ); dump($result);

In the variable $result you will get an array of results that looks like this:

If you need to substitute some data from the variables in the query, it is better to insert them not directly, but using a bindings:

app/Console/Commands/RunRawQueryCommand.php$result = $connection->select( "SELECT * FROM users WHERE email like :email ORDER BY created_at DESC LIMIT 1", ["email" => "%admin%"] ); dump($result);

2. SQL query without getting the data

If you don’t need to change the data, and only care whether the query was executed successfully or not, you can use the statement() method. For example:

app/Console/Commands/RunRawQueryCommand.php$result = $connection->statement( "DELETE FROM users WHERE id = 1" ); dump($result);

Or the same action using the DB facade:

app/Console/Commands/RunRawQueryCommand.php\DB::statement( "DELETE FROM users WHERE id = 2" );

3. Adding SQL in Eloquent select()

When you get a list of models in Eloquent, you can specify which fields you want to get. You can also add a SQL query here to get additional data based on some aggregation function using the selectRaw() method. For example:

app/Console/Commands/RunRawQueryCommand.php$result = \App\Models\User::query() ->select("*") ->selectRaw("LENGTH(email) as email_length") ->get(); dump($result);

To get all the fields of the model, you should add select(‘*’) before your query. Then you will get all the fields that are in the table, as well as your additional ones.

4. Adding SQL to Eloquent where()

If you need to add a complex condition, for example with an aggregation function, you can use the whereRaw() method. For example, let’s select users whose email is longer than 10 characters:

app/Console/Commands/RunRawQueryCommand.php$result = \App\Models\User::query() ->whereRaw("LENGTH(email) > 5") ->get(); dump($result);

In this case, you will get a collection with objects of class App\Models\User, but filtered by the specified condition.

Don’t forget that Elqouent supports a lot of functions without the need to write SQL queries. Moreover, new features will be added in the future. Check if what you want to do is already implemented.

Conclusion

In this article, I showed you how to run raw SQL queries in Laravel. Of course, you can also get a PDO database connection object from the Connection class, but usually everything described above is enough.

Leave a Comment

Exit mobile version