Laravel has an excellent query builder, that can help build very complicated queries. But in some cases you may want to use a new functionality or a specific methods of you database. In this case you should use raw SQL queries. It is possible.
You can create entire SQL query from scratch or use raw insertions in query builder. In this article we will dive into how to run raw SQL query in Laravel in different situations.
Table of Contents
How to use Raw SQL Query in Laravel
1. Raw Query
You can execute a raw query using Illuminate\\Support\\Facades\\DB or using an instance of the Illuminate\\Database\\Connection class resolved by the container. For example, let’s create RunRawQueryCommand:
php artisan make:command RunRawQueryCommand
Import the Connection class:
use Illuminate\Database\Connection;
Change the handle() method definition to this:
public function handle(Connection $connection){
}
You can use the select() method if you want to fetch some data from the query, or use the statement() method for executing a query without fetching data. For example, you can fetch the newest record from the articles table:
$connection->select(
"SELECT * FROM articles WHERE true ORDER BY created_at DESC LIMIT 1"
);
Also you can use bindings here. For example, let’s find an article, containing the word ‘He’ in the title:
$connection->select(
"SELECT * FROM articles WHERE title like :title ORDER BY created_at DESC LIMIT 1",
["title" => "%he%"]
);
You will get the array of stdClass classes where class properties will contain record fields:
If you want to delete this article, you can use the statement() method:
$connection->statement(
"DELETE FROM articles WHERE id = '8d303032-3cb6-11ed-8cd2-d108af57a73f'"
);
Here, you will get only boolean indicating that the query have been executed successfully. Alternatively, you can use Illuminate\Support\Facades\DB facade instead of the database connection:
DB::statement(
"DELETE FROM articles WHERE id = '8d303032-3cb6-11ed-8cd2-d108af57a73f'"
);
2. Raw in Select
As you can see, if you use an entire raw expression for fetching data, you lose all benefits of using Eloquent models and collections. So, if you want to add a raw query into select, in some cases you can use selectRaw() static method with model class. For example, if you want to count the title length, you can use this query:
$result = Article::query()
->select("*")
->selectRaw("LENGTH(title) as title_length")
->get();
You should add select(‘*’) to load all default fields, and additional raw query for specific calculations. You will get a collection of articles that will have a calculated field:
3. Raw in Where
If you want to use a complicated condition in where, you can also use whereRaw() method. But consider that Eloquent has a very powerful query builder, that allows doing plenty of things without using raw queries. We will have a look at examples in another article. Here, let’s imagine that you want to select articles with the title length of more than 97 symbols:
$result = Article::query()
->whereRaw("LENGTH(title) > 97")
->get();
You will get a regular collection with articles, but only articles with title length longer than 97 symbols will be present.
Wrapping Up
In this article we have explained how to run a raw SQL query in Laravel using Eloquent. Also, you can get a PDO connection and use it, but in most cases, you can just use Eloquent. If you want to optimize your database queries using raw SQL query, consider, that you can use with() eloquent method for optimization in some cases.
