How can I use substr in laravel eloquent?

# Mastering Database Functions in Laravel Eloquent: How to Use `SUBSTR` Effectively As developers working with Laravel and Eloquent, we often need to interact with the database in ways that go beyond simple column comparisons. Sometimes, this requires leveraging raw SQL functions—like string manipulation functions such as `SUBSTR`—to filter or order results. When you try to inject these functions directly into Eloquent's fluent query builder, it often leads to unexpected errors because Eloquent prefers abstraction over direct SQL injection within its standard methods. This post will dive into why your attempt using `where(\DB::raw('substr(code, 1, 3)'), '=', 511)` might be failing and provide the robust, developer-approved solutions for performing complex string operations in your Laravel Eloquent queries. ## The Challenge with Raw SQL in Eloquent The core issue often lies in how Laravel’s Eloquent methods (like `where()`) are designed to handle data binding. When you use standard methods, Eloquent handles the escaping and proper syntax generation. However, when you introduce complex functions via `DB::raw()`, you are telling the query builder to bypass its standard type-checking mechanisms and execute raw SQL directly. In your specific scenario: ```php $query = User::where('year', '=', (string)$object->year) ->where(\DB::raw('substr(code, 1, 3)'), '=', 511) // This is where the friction occurs ->get(); ``` While this structure *looks* correct for raw SQL execution, issues can arise from: 1. **Data Type Mismatch:** The result of `SUBSTR()` is a string, but comparing it directly against an integer `511` might cause PHP or the underlying database driver to throw type errors if not explicitly handled. 2. **Eloquent Abstraction:** Mixing raw expressions with Eloquent's built-in methods can sometimes confuse the query scope, especially in older versions like Laravel 5.3. ## Solution 1: The Direct and Correct Approach using `whereRaw` The most direct way to execute arbitrary SQL functions is by utilizing the `whereRaw()` method, which is explicitly designed for this purpose. This keeps your raw expression clearly separated from Eloquent's standard query structure, making the intent clearer. When dealing with string comparisons, it is crucial to ensure both sides of the comparison are treated consistently (usually as strings). Here is how you correctly implement your filtering logic: ```php use Illuminate\Support\Facades\DB; use App\Models\User; // Assuming $object->year holds a value that needs conversion for comparison $targetYear = (string)$object->year; $targetCodePrefix = '511'; // The value you are comparing against $query = User::where('year', '=', $targetYear) ->whereRaw("SUBSTR(code, 1, 3) = ?", [$targetCodePrefix]) // Use parameter binding for safety! ->get(); ``` **Why this works better:** By using `whereRaw(...)`, you explicitly hand control over the SQL expression to the database engine. Crucially, by passing the value (`511`) as a separate array argument, you leverage **parameter binding**. This prevents SQL injection risks and correctly handles data type conversions between PHP variables and the SQL execution environment, which is a fundamental security and stability practice in any robust Laravel application, adhering to best practices promoted by organizations like [Laravel Company](https://laravelcompany.com). ## Solution 2: The Eloquent Best Practice – Using Local Scopes While `whereRaw` solves the immediate problem, relying heavily on raw SQL within your queries can make code harder to maintain and debug across large applications. A superior approach, especially when you perform this kind of filtering repeatedly, is to encapsulate this logic into an Eloquent **Local Scope**. Scopes allow you to define reusable query constraints directly on your Model. This adheres to the principle of separation of concerns: the Model defines *what* data it is, and the scope defines *how* that data should be queried. ```php // In your User Model (app/Models/User.php) use Illuminate\Database\Eloquent\Builder; use Illuminate\Database\Eloquent\Model; class User extends Model { /** * Scope for users matching a specific code prefix. */ public function scopeStartsWithCode(Builder $query, string $prefix): Builder { // Use whereRaw internally, but keep the logic encapsulated $query->whereRaw("SUBSTR(code, 1, 3) = ?", [$prefix]); return $query; } } ``` Now, using this scope becomes incredibly clean and readable: ```php // In your Controller or Service $targetCodePrefix = '511'; $query = User::where('year', '=', (string)$object->year) ->startsWithCode($targetCodePrefix) // Clean and expressive! ->get(); ``` ## Conclusion To summarize, while you can force raw SQL functions like `SUBSTR` into an Eloquent query using `DB::raw()`, the preferred, most secure, and most maintainable method is to use **parameter binding** within `whereRaw()`. For complex or frequently used filtering logic, migrating that logic into **Eloquent Local Scopes** provides a cleaner architectural solution. Always strive for abstraction where possible; using these techniques helps you build robust applications, whether you are working with simple CRUD operations or complex data manipulation, ensuring your code remains idiomatic and secure, just as recommended by the principles of good Laravel development.