Banner Add

Laravel

How to Use DB Raw Query in Laravel

September 02, 2023
admin

In this tutorial, I will show you how to use DB Raw Query in Laravel. I will try to explain DB:raw() in a simple method for using Use - DB::raw().

We're using Raw Query with avg | count | sum | and other functions.

→ Import the DB facade

use Illuminate\Support\Facades\DB;
  • Use the select, insert, update, or delete method of the DB facade to carry out a raw SQL query.

→ Select Query

$results = DB::select('SELECT * FROM users WHERE id = ?', [1]);
  • We are choosing every column in the users table where the id is 1. An array of bindings is provided as the second option to prevent SQL injection.

→ Insert Query

DB::insert('INSERT INTO users (name, email) VALUES (?, ?)', ['John Doe', 'john@example.com']);
  • This query inserts a new user into the users table with the provided name and email values

→ Update Query

DB::update('UPDATE users SET name = ? WHERE id = ?', ['New Name', 1]);
  • For the user with the id of 1, we are updating the name column.

→ Delete Query

DB::delete('DELETE FROM users WHERE id = ?', [1]);
  • This query deletes the user with an id of 1 from the users table.

→ You can also use placeholders in your raw queries

DB::select('SELECT * FROM users WHERE name = :name', ['name' => 'John Doe']);

→ Dealing with Transactions

  • When working with raw queries, you can use the beginTransaction, commit, and rollBack methods to manage transactions:
DB::beginTransaction();

try {
    // Execute your raw queries here

    DB::commit();
} catch (\Exception $e) {
    DB::rollBack();
    // Handle the exception
}

→ Retrieving Results

foreach ($results as $user) {
    echo $user->name;
}

→ Binding Parameters with Named Bindings

  • You can use named bindings for more readability and flexibility when working with raw queries. For instance
DB::select('SELECT * FROM users WHERE name = :name', ['name' => 'John Doe']);

→ Using Joins and Relationships

  • When working with multiple tables or relationships between tables, you can still use raw queries. For instance, if you want to perform a JOIN operation between two tables
DB::select('SELECT users.name, orders.order_number FROM users
            JOIN orders ON users.id = orders.user_id');

→ Pagination

$results = DB::table('users')->paginate(10);

→ Inserting Multiple Records

  • If you need to insert multiple records at once, you can use the insert method with an array of values
$data = [
    ['name' => 'John', 'email' => 'john@example.com'],
    ['name' => 'Jane', 'email' => 'jane@example.com'],
];

DB::table('users')->insert($data);
  • This is more efficient than running separate insert queries for each record

→ Using Database Functions

  • You can utilize database specific functions in your raw queries. For example, if you're using MySQL , you can use functions like NOW() , COUNT() , or SUM() directly in your queries.
DB::select('SELECT COUNT(*) as count FROM users');

→ Database Aggregations

  • Raw queries are useful for complex aggregations. For instance , you can calculate the average salary of employees
DB::select('SELECT AVG(salary) as avg_salary FROM employees');

→ Multiple Statements in a Single Query

  • By separating each SQL statement with a semicolon, you can run several SQL statements simultaneously. However, you should exercise caution when implementing this strategy because it might not be compatible with all database management systems and, if not managed correctly, it could be a security concern
DB::statement('
    INSERT INTO users (name, email) VALUES ("John Doe", "john@example.com");
    UPDATE orders SET status = "processed" WHERE user_id = 1;
');

→ Raw Expressions

  • You can utilize raw expressions in your queries with Laravel. To explicitly incorporate raw SQL expressions in your query, utilize the DB::raw() function. For calculations or complicated expressions, this is helpful
DB::select('SELECT id, name, salary * 12 AS annual_salary FROM employees');

→ Using Subqueries

  • Subqueries can be used to carry out more complicated operations inside of raw SQL queries. You could, for instance, figure out the average wage for workers in a particular department
DB::select('SELECT department, AVG(salary) as avg_salary FROM employees GROUP BY department');

→ Executing Stored Procedures

  • If your database contains stored procedures , you can execute them using raw SQL queries
DB::statement('CALL your_stored_procedure(?, ?)', [$param1, $param2]);
  • Replace your_stored_procedure with the name of your stored procedure and provide any required parameters

→ Working with Temporary Tables

  • Temporary tables can be created and used within raw queries for complicated data transformations. Temporary tables are session-specific and can be useful for streamlining certain activities.
DB::statement('CREATE TEMPORARY TABLE temp_table AS SELECT * FROM users WHERE age > ?', [18]);

→ Using UNION and UNION ALL

  • You can combine the results of multiple queries using UNION or UNION ALL. UNION removes duplicate rows, while UNION ALL includes all rows, even duplicates.
$results = DB::select('SELECT name FROM table1 UNION SELECT name FROM table2');

→ Using Raw SQL in Eloquent Models

  • You can use the selectRaw, orderByRaw, and other methods to incorporate raw SQL into your Eloquent models
$users = User::selectRaw('name, salary * 12 AS annual_salary')->orderByRaw('annual_salary DESC')->get();
  • This enables you to mix the freedom of raw SQL expressions with the power of Eloquent

→ Advanced Join Operations

DB::select('SELECT * FROM orders LEFT JOIN products ON orders.product_id = products.id AND products.price > ?', [100]);
  • This query performs a left join and includes a condition on the products table