Raw expressions in Laravel - Step-by-Step Execution

Concept Flow - Raw expressions

Start Query Builder

↓

Add Raw Expression

↓

Raw SQL Injected

↓

Execute Query

↓

Get Results

This flow shows how Laravel's query builder accepts raw SQL expressions, injects them directly, and executes the query.

Execution Sample

Laravel

$users = DB::table('users')
    ->select(DB::raw('count(*) as user_count'))
    ->whereRaw('age > ?', [25])
    ->get();

This code builds a query using raw expressions to count users older than 25.

Execution Table

Step	Action	Raw Expression Used	Query Part	Result
1	Start query builder on 'users' table	N/A	FROM users	Query builder ready
2	Add select with DB::raw('count(*) as user_count')	count(*) as user_count	SELECT count(*) as user_count	Select clause set
3	Add whereRaw('age > ?', [25])	age > ?	WHERE age > 25	Where clause set with parameter
4	Execute get()	N/A	Full SQL: SELECT count(*) as user_count FROM users WHERE age > 25	Query executed, results fetched
5	Return results	N/A	N/A	Collection of results with user_count

💡 Query executed and results returned, no more steps.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
$users	undefined	Query builder instance with select raw	Query builder instance with whereRaw added	Executed query, results fetched	Collection with user_count

Key Moments - 3 Insights

Why do we use DB::raw inside select()?

DB::raw tells Laravel to insert the SQL exactly as written without escaping or modifying it, as shown in step 2 of the execution_table.

How does whereRaw differ from where?

whereRaw allows writing raw SQL conditions with placeholders, which Laravel inserts directly, unlike where which builds conditions from parameters. See step 3 in execution_table.

Is it safe to use raw expressions?

Raw expressions can cause SQL injection if user input is not properly bound. Using parameter binding like in step 3 (age > ?) keeps it safe.

Visual Quiz - 3 Questions

Look at the execution_table, what raw expression is used in step 2?

A SELECT * FROM users

B age > ?

C count(*) as user_count

D get()

💡 Hint

Check the 'Raw Expression Used' column in step 2 of execution_table.

At which step is the parameter 25 bound to the query?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look at the 'Action' and 'Query Part' columns in step 3 of execution_table.

If we remove DB::raw from select(), what happens?

A Laravel will escape the expression as a string

B The query will fail to run

C The raw SQL will still be injected

D The whereRaw will be ignored

💡 Hint

Think about how Laravel treats strings in select() without DB::raw, referencing step 2.

Concept Snapshot

Laravel Raw Expressions:
- Use DB::raw() to insert raw SQL in queries.
- Use whereRaw() for raw WHERE clauses with bindings.
- Bind parameters to avoid SQL injection.
- Raw expressions bypass Laravel's query builder escaping.
- Useful for complex SQL not supported by builder methods.

Full Transcript

This lesson shows how Laravel's query builder uses raw expressions to insert direct SQL snippets. The flow starts with building a query on the users table, then adds a raw select expression to count users. Next, a raw where clause filters users older than 25 with parameter binding. Finally, the query executes and returns results. Key points include using DB::raw to avoid escaping, whereRaw for raw conditions, and always binding parameters to keep queries safe. The execution table traces each step, showing how the raw SQL is integrated and executed.

Practice

1. What is the primary purpose of using DB::raw() in Laravel queries?

easy

A. To insert raw SQL expressions directly into the query builder

B. To automatically escape all user inputs

C. To convert Eloquent models into arrays

D. To cache query results for faster access

Solution

1. Step 1: Understand the role of DB::raw()

   DB::raw() allows you to write raw SQL expressions inside Laravel's query builder without modification.

2. Step 2: Compare with other options

   Options B, C, and D describe different Laravel features unrelated to raw SQL expressions.

3. Final Answer:

   To insert raw SQL expressions directly into the query builder -> Option A

4. Quick Check:

   DB::raw() = raw SQL insertion [OK]

Hint: DB::raw() means write SQL exactly as is inside queries [OK]

Common Mistakes:

• Thinking DB::raw() escapes inputs automatically
• Confusing DB::raw() with model conversion
• Assuming DB::raw() caches queries

2. Which of the following is the correct syntax to use DB::raw() in a Laravel query builder to select the current date?

easy

A. DB::table('users')->select(DB::raw('CURRENT_DATE'))->get();

B. DB::table('users')->select('CURRENT_DATE')->get();

C. DB::table('users')->selectRaw('CURRENT_DATE')->get();

D. DB::table('users')->selectRaw(DB::raw('CURRENT_DATE'))->get();

Solution

1. Step 1: Identify correct raw expression usage

   DB::raw() wraps the raw SQL string inside select() to insert it as is.

2. Step 2: Analyze other options

   DB::table('users')->select('CURRENT_DATE')->get(); treats 'CURRENT_DATE' as a column name, not raw SQL. DB::table('users')->selectRaw('CURRENT_DATE')->get(); uses selectRaw() correctly but without DB::raw(), which is valid but not the exact syntax asked. DB::table('users')->selectRaw(DB::raw('CURRENT_DATE'))->get(); nests DB::raw() inside selectRaw(), which is incorrect.

3. Final Answer:

   DB::table('users')->select(DB::raw('CURRENT_DATE'))->get(); -> Option A

4. Quick Check:

   Use DB::raw() inside select() for raw SQL [OK]

Hint: Use DB::raw('SQL') inside select() for raw SQL [OK]

Common Mistakes:

• Using select() with string instead of DB::raw()
• Confusing selectRaw() and DB::raw() usage
• Nesting DB::raw() inside selectRaw() incorrectly

3. What will be the output of the following Laravel query?

DB::table('orders')
  ->select('id', DB::raw('price * quantity AS total'))
  ->where('status', 'completed')
  ->get();

medium

A. An empty collection because raw expressions are not supported in where clauses

B. A syntax error because raw expressions cannot have aliases

C. A collection of orders with id and a calculated total price (price * quantity)

D. A collection with only id and price fields, ignoring quantity

Solution

1. Step 1: Understand the select with DB::raw()

   The query selects 'id' and a raw SQL expression calculating 'price * quantity' as 'total'. This is valid and returns those fields.

2. Step 2: Check the where clause and output

   The where clause filters orders with status 'completed'. The output is a collection of matching orders with 'id' and 'total' fields.

3. Final Answer:

   A collection of orders with id and a calculated total price (price * quantity) -> Option C

4. Quick Check:

   DB::raw() with alias returns calculated fields [OK]

Hint: DB::raw() can calculate fields with aliases in select [OK]

Common Mistakes:

• Thinking raw expressions cannot have aliases
• Assuming raw expressions affect where clause support
• Ignoring the calculated field in output

4. Identify the error in this Laravel query using raw expressions:

DB::table('users')
  ->where(DB::raw('LENGTH(name)'), '>', 5)
  ->get();

medium

A. Raw expressions cannot be used inside where clauses

B. The syntax is correct; no error present

C. DB::raw() should be wrapped inside a closure for where

D. The comparison operator '>' is invalid in this context

Solution

1. Step 1: Review usage of DB::raw() in where clause

   Using DB::raw() inside where to apply SQL functions like LENGTH() is valid syntax in Laravel.

2. Step 2: Check operators and syntax

   The '>' operator is valid for comparison. No wrapping closure is required here.

3. Final Answer:

   The syntax is correct; no error present -> Option B

4. Quick Check:

   DB::raw() works in where clauses with operators [OK]

Hint: DB::raw() can be used directly in where with operators [OK]

Common Mistakes:

• Assuming raw expressions are disallowed in where
• Thinking a closure is needed for DB::raw() in where
• Misunderstanding comparison operator validity

5. You want to update all users' scores by adding 10 points using a raw SQL expression in Laravel. Which code snippet correctly achieves this safely?

hard

A. DB::table('users')->update(DB::raw('score = score + 10'));

B. DB::table('users')->update(['score' => 'score + 10']);

C. DB::table('users')->updateRaw('score = score + 10');

D. DB::table('users')->update(['score' => DB::raw('score + 10')]);

Solution

1. Step 1: Understand how to update with raw expressions

   To update a column by adding a value, use DB::raw() inside the update array to write the SQL expression.

2. Step 2: Analyze each option

   DB::table('users')->update(['score' => DB::raw('score + 10')]); correctly uses DB::raw('score + 10') as the new value. DB::table('users')->update(['score' => 'score + 10']); treats it as a string, not SQL. DB::table('users')->update(DB::raw('score = score + 10')); passes DB::raw() directly to update(), which expects an array, causing error. DB::table('users')->updateRaw('score = score + 10'); uses a non-existent updateRaw() method.

3. Final Answer:

   DB::table('users')->update(['score' => DB::raw('score + 10')]); -> Option D

4. Quick Check:

   Use DB::raw() inside update array for calculations [OK]

Hint: Use DB::raw() inside update array for arithmetic updates [OK]

Common Mistakes:

• Passing raw SQL as string without DB::raw()
• Calling non-existent updateRaw() method
• Passing DB::raw() directly instead of in array