Query scopes and query builder: best practices explained

Query scopes and query builder: best practices explained

Every Laravel application accumulates query patterns. Some filters get repeated across controllers, others belong in multiple places, and suddenly you're duplicating where clauses across your codebase. Query scopes and query builder mastery solve this problem elegantly. Understanding when to use local scopes, global scopes, the raw query builder, and raw SQL safely is what separates maintainable Laravel code from code that becomes a burden.

Local scopes — scopeActive becomes Product::active()

Local scopes are instance methods on your Eloquent model that encapsulate common query filters. They're the first tool to reach for when you have a query pattern you'll use in multiple places within your application. Laravel's naming convention is straightforward: define a method prefixed with scope, and it becomes available as a chainable method without the prefix.

Consider a products table where you frequently need to filter for active items:

// app/Models/Product.php
public function scopeActive($query)
{
    return $query->where('status', 'active');
}

// Usage in controller
$products = Product::active()->get();
$featuredProducts = Product::active()
    ->where('featured', true)
    ->latest()
    ->paginate(15);

The benefit here isn't just avoiding repetition—it's readability. Product::active() tells another developer exactly what you're doing without needing to read the underlying SQL. Local scopes also provide a single source of truth. If your business rule for "active" changes, you update it once in the scope, and every query using it updates automatically.

You can make scopes more flexible by accepting parameters:

public function scopeStatus($query, $status)
{
    return $query->where('status', $status);
}

public function scopeCreatedAfter($query, $date)
{
    return $query->where('created_at', '>=', $date);
}

// Usage
$products = Product::status('active')
    ->createdAfter(now()->subMonths(3))
    ->get();

This approach scales well. As your model accumulates business logic, scopes become the interface through which the rest of your application queries data. Keep scopes focused on a single responsibility—they should represent a single business concept, not combine multiple concerns.

Global scopes — Automatically apply filters like tenant id

Global scopes are more powerful and more dangerous. They automatically apply to every query on a model unless explicitly removed. They're indispensable for multi-tenant applications, soft deletes, and other system-wide filters that should always be present.

Implementing a global scope requires creating a class that implements the Scope contract:

// app/Scopes/TenantScope.php
namespace App\Scopes;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Scope;

class TenantScope implements Scope
{
    public function apply(Builder $builder, Model $model): void
    {
        $builder->where('tenant_id', auth()->user()->tenant_id);
    }
}

// app/Models/Project.php
protected static function booted(): void
{
    static::addGlobalScope(new TenantScope);
}

Now every query on the Project model automatically filters by the authenticated user's tenant ID. This prevents accidental data leaks across tenants—a critical security pattern in SaaS applications.

The flexibility comes in when you need to temporarily bypass a global scope:

// Include results from all tenants (admin context only)
$allProjects = Project::withoutGlobalScopes()->get();

// Remove only the TenantScope, keep others
$allProjects = Project::withoutGlobalScope(TenantScope::class)->get();

Use global scopes sparingly. They're invisible to the next developer reading your code, which makes them both powerful and dangerous. If someone forgets that a global scope is applied, they might write queries that behave differently than expected. Document them clearly and reserve them for truly system-wide concerns like multi-tenancy and soft deletes.

Query builder — Drop down from Eloquent for reports and heavy queries

Eloquent is elegant, but it carries overhead. For heavy analytical queries, complex joins, and reporting, dropping down to the query builder—Laravel's fluent database abstraction layer—often makes sense. You lose the model benefits, but you gain performance and clarity.

Consider generating a sales report that needs to join orders, order items, products, and customers, with aggregations and conditional logic:

// Eloquent approach: instantiates models, lots of memory
$report = Order::with(['items.product', 'customer'])
    ->whereBetween('created_at', [$start, $end])
    ->get()
    ->map(function ($order) {
        return [
            'order_id' => $order->id,
            'customer' => $order->customer->name,
            'total' => $order->items->sum('price'),
            'items_count' => $order->items->count(),
        ];
    });

// Query builder approach: returns raw data, minimal overhead
$report = DB::table('orders')
    ->join('customers', 'orders.customer_id', '=', 'customers.id')
    ->join('order_items', 'orders.id', '=', 'order_items.order_id')
    ->join('products', 'order_items.product_id', '=', 'products.id')
    ->whereBetween('orders.created_at', [$start, $end])
    ->select(
        'orders.id as order_id',
        'customers.name as customer',
        DB::raw('SUM(order_items.price) as total'),
        DB::raw('COUNT(order_items.id) as items_count')
    )
    ->groupBy('orders.id', 'customers.name')
    ->get();

The query builder version is faster because it doesn't hydrate model instances. It's also easier to understand at a glance—the SQL structure is obvious. For large datasets or queries that run frequently, this difference compounds.

You can still organize query builder queries into repositories or service classes to keep your controllers clean:

// app/Services/ReportService.php
public function salesReport($start, $end)
{
    return DB::table('orders')
        ->join('customers', 'orders.customer_id', '=', 'customers.id')
        ->whereBetween('orders.created_at', [$start, $end])
        ->select('orders.id', 'customers.name', DB::raw('COUNT(*) as count'))
        ->groupBy('orders.id', 'customers.name')
        ->get();
}

// In controller
$report = app(ReportService::class)->salesReport($start, $end);

The rule of thumb: use Eloquent for typical CRUD operations and business logic. Use the query builder when you're doing complex joins, aggregations, or need to squeeze performance.

Raw SQL safely — Always bind parameters — never interpolate user input

Sometimes even the query builder can't express what you need. Raw SQL exists, but it's a loaded weapon. The cardinal rule: never interpolate user input into raw SQL. Always use parameter binding.

This is vulnerable to SQL injection:

// NEVER DO THIS
$userId = request('user_id');
$results = DB::select("SELECT * FROM users WHERE id = $userId");

An attacker passing 1 OR 1=1 as the user ID would fetch every row. Instead, always bind:

// Safe: positional binding
$results = DB::select('SELECT * FROM users WHERE id = ?', [$userId]);

// Safe: named binding
$results = DB::select(
    'SELECT * FROM users WHERE id = :id AND status = :status',
    ['id' => $userId, 'status' => 'active']
);

// Safe: statement binding
DB::statement('UPDATE users SET verified = true WHERE id = ?', [$userId]);

Binding works because the database driver sends the SQL structure and data separately. The database knows the difference between SQL code and data, preventing injection.

For complex raw queries that need to stay maintainable, consider using prepared statements or database views. Views push the query complexity into your database schema, where it's versioned and tested independently:

// Create a database view for complex logic
DB::select('CREATE OR REPLACE VIEW user_summary AS
    SELECT 
        u.id,
        u.name,
        COUNT(o.id) as order_count,
        SUM(o.total) as lifetime_value
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id, u.name'
);

// Then query it safely
$summary = DB::table('user_summary')->get();

Common mistakes

• Over-applying global scopes: Adding too many global scopes makes queries unpredictable. Developers forget they're applied and end up debugging queries that behave unexpectedly.
• Naming scope parameters ambiguously: A scope named scopeFilter tells you nothing. Use concrete names: scopeByStatus, scopeCreatedAfter, scopeForUser.
• Mixing concerns in a single scope: Don't create a scope that filters by status and tenant and date range. Break it into separate, composable scopes.
• Querying in a loop: Using Eloquent in a loop defeats the purpose of the query builder. Use chunk() for memory efficiency or the query builder for better performance.
• Forgetting to bind in raw SQL: This is the most dangerous. Code review should catch it, but automation is better—use static analysis tools to flag unbound raw queries.

Key takeaways

• Local scopes encapsulate reusable query filters and make your code self-documenting. Use them liberally for business logic.
• Global scopes automatically apply filters across all queries on a model. Reserve them for system-wide concerns like multi-tenancy.
• Drop to the query builder for complex joins, aggregations, and performance-critical queries where Eloquent overhead matters.
• Raw SQL is necessary sometimes, but always bind parameters using ? or named placeholders. Never interpolate user input.
• Organize query logic in repositories or service classes to keep controllers thin and queries testable.