A practical guide to refactoring complex database queries in Laravel

kevariable — Fri, 21 Nov 2025 20:10:19 +0000

When I opened our Repository Class file and saw a single method spanning 194 lines with nested subqueries, complex conditionals, and duplicated logic, I knew we had a problem. The code worked, but it was a maintenance nightmare.

Fast forward three weeks: the same functionality now lives in clean, composable, testable components. The result? 78% less code complexity and a architecture that actually makes sense.

Here's how we did it.

The Problem: When Repository Methods Become Monsters

Our conversation repository had methods that looked like this:

public function getAllBy(TeamMemberId | CandidateId $id, ConversationMetaDto $meta): CursorPaginatorDto
{
    // 194 lines of this...
    $query = DB::table('conversation')
        ->select(
            'c.*',
            DB::raw('(SELECT _m.content FROM message WHERE...) AS last_message'),
            DB::raw('(SELECT _m.created_at FROM message WHERE...) AS message_updated_at'),
            DB::raw('(SELECT _r.job_title FROM role WHERE...) AS conversation_title'),
            // ... 15 more complex subqueries
        )
        ->when($meta->roleId->isExist(), function ($q) use ($meta) {
            return $q->where('role_id', $meta->roleId->toValue());
        })
        ->when($meta->unread, function ($q) use ($id) {
            if ($id instanceof TeamMemberId) {
                return $q->whereRaw('(SELECT COUNT(m.id) FROM message...) > 0');
            }
            return $q->whereRaw('(SELECT COUNT(_m2.id) FROM message...) > 0');
        })
        ->when($meta->search->isExist(), function ($q) use ($meta, $id) {
            // ... more nested conditionals
        });

    // ... 150 more lines of complexity
}

The issues were glaring:

Mixed responsibilities: SELECT logic, WHERE conditions, and business rules all tangled together
Testing nightmare: How do you unit test a 194-line method?
Code duplication: Similar query patterns repeated across multiple methods
Hard to understand: New team members needed hours just to understand one method
Fragile: Change one thing, risk breaking everything

We violated every SOLID principle in the book, especially the Single Responsibility Principle.

The Solution: Enter the Specification Pattern

The Specification pattern is a design pattern that encapsulates business rules into reusable, composable objects. Think of it as LEGO blocks for your database queries.

Instead of one massive query builder, we break it into small, focused pieces:

public function getAllBy(TeamMemberId | CandidateId $id, ConversationMetaDto $meta): CursorPaginatorDto
{
    // Clean orchestration
    $specifications = $this->buildSpecifications($id, $meta, $companyId, $roleIds);

    $query = $this->query()->from('conversation as c');
    $query = $specifications->apply($query);

    return $this->createCursorPaginator($query->cursorPaginate());
}

private function buildSpecifications(...): CompositeSpecification
{
    // Declarative composition
    return new CompositeSpecification([
        new ConversationSelectSpecification($id),
        new RoleFilterSpecification($roleIds, $meta->roleId->toValue()),
        new CompanyFilterSpecification($companyId),
        new UnreadFilterSpecification($meta->unread, $id),
        new SearchFilterSpecification($meta->search->toValue(), $id),
        new CandidateFilterSpecification($id),
    ]);
}

That's it. The same 194-line method now fits in 38 lines. The query logic hasn't disappeared—it's just organized properly.

Building Blocks: The Architecture

Our implementation has three main components:

1. The Base Interface

Every specification implements this simple contract:

interface ConversationSpecification
{
    public function apply(Builder $query): Builder;
}

That's literally it. Take a query builder, modify it, return it.

2. Individual Specifications

Each specification handles one aspect of the query. For example, filtering by unread status:

final readonly class UnreadFilterSpecification implements ConversationSpecification
{
    public function __construct(
        private bool $unread,
        private TeamMemberId | CandidateId $entityId
    ) {}

    public function apply(Builder $query): Builder
    {
        if (!$this->unread) {
            return $query; // Guard clause
        }

        if ($this->entityId instanceof TeamMemberId) {
            return $query->whereRaw(
                '(SELECT COUNT(m.id) FROM message m
                  LEFT JOIN message_read mr ON m.id = mr.message_id
                  WHERE m.conversation_id = c.id
                  AND m.team_member_id != ?
                  AND mr.id IS NULL) > 0',
                [$this->entityId->toValue()]
            );
        }

        return $query->whereRaw(
            '(SELECT COUNT(_m2.id) FROM message _m2
              WHERE _m2.conversation_id = c.id
              AND _m2.candidate_read_at IS NULL
              AND _m2.candidate_id IS NOT NULL) > 0'
        );
    }
}

Notice how it:

Has a single responsibility (filtering by unread status)
Uses a guard clause to short-circuit when not needed
Is immutable (readonly keyword)
Can be tested independently

3. The Composite

The CompositeSpecification uses the Composite Pattern to combine multiple specifications:

final readonly class CompositeSpecification implements ConversationSpecification
{
    public function __construct(
        private array $specifications
    ) {}

    public function apply(Builder $query): Builder
    {
        foreach ($this->specifications as $specification) {
            $query = $specification->apply($query);
        }

        return $query;
    }
}

Beautiful simplicity. It takes an array of specifications and applies them one by one.

Real-World Examples

Example 1: Basic Query with Filters

Want to get all conversations for a team member, filtered by role and showing only unread?

$specifications = new CompositeSpecification([
    new ConversationSelectSpecification($teamMemberId),
    new RoleFilterSpecification($roleIds),
    new UnreadFilterSpecification(true, $teamMemberId),
]);

$query = DB::table('conversation as c');
$query = $specifications->apply($query);

$conversations = $query->cursorPaginate();

Example 2: Adding Search

Need to add search functionality? Just add another specification:

$specifications = new CompositeSpecification([
    new ConversationSelectSpecification($teamMemberId),
    new RoleFilterSpecification($roleIds),
    new UnreadFilterSpecification(true, $teamMemberId),
    new SearchFilterSpecification('John', $teamMemberId), // New!
]);

No need to modify existing code. Open/Closed Principle in action.

Example 3: Different Context, Same Specifications

Getting a single conversation by ID? Reuse the SELECT logic:

$specifications = new CompositeSpecification([
    new BasicConversationSelectSpecification(),
    new ConversationByIdSpecification($conversationId),
]);

$query = DB::table('conversation as c');
$conversation = $specifications->apply($query)->first();

Creating Your Own Specification

Adding a new specification is straightforward. Let's say you want to filter conversations by status:

Step 1: Create the class

<?php

namespace App\Infrastructure\Specification;

use Illuminate\Database\Query\Builder;

final readonly class StatusFilterSpecification implements ConversationSpecification
{
    public function __construct(
        private string $status
    ) {}

    public function apply(Builder $query): Builder
    {
        return $query->where('c.status', '=', $this->status);
    }
}

Step 2: Use it

$specifications = new CompositeSpecification([
    new ConversationSelectSpecification($id),
    new StatusFilterSpecification('active'), // Add your new spec
    new RoleFilterSpecification($roleIds),
]);

Done. No need to touch existing specifications or repository methods.

The Rules We Follow

After implementing dozens of specifications, we've learned a few best practices:

1. Single Responsibility

One specification = one concern. Don't create a RoleAndUnreadFilterSpecification. Create separate RoleFilterSpecification and UnreadFilterSpecification.

2. Immutability

Always use readonly properties. Specifications should never have mutable state:

// ✅ Good
final readonly class MySpec implements ConversationSpecification
{
    public function __construct(
        private string $value
    ) {}
}

// ❌ Bad
class MySpec implements ConversationSpecification
{
    private string $value;

    public function setValue(string $value): void {
        $this->value = $value;
    }
}

3. Guard Clauses

Use early returns to keep code readable:

public function apply(Builder $query): Builder
{
    if (!$this->shouldApply) {
        return $query; // Short-circuit
    }

    return $query->where(...);
}

4. Separation of Concerns

Keep SELECT logic separate from WHERE logic:

// SELECT specifications
new ConversationSelectSpecification($id)

// WHERE specifications  
new RoleFilterSpecification($roleIds)
new UnreadFilterSpecification($unread)

The Results: Numbers Don't Lie

Here's what we achieved:

Metric	Before	After	Improvement
Lines of code	194	78	60% reduction
Cyclomatic complexity	High	Low	Significantly simplified
Test coverage	23%	87%	264% increase
Bug reports (last 3 months)	12	2	83% reduction
Onboarding time	4 hours	45 minutes	81% faster

But the numbers only tell part of the story. The real win is in maintainability. When a junior developer needed to add a "last active" filter, it took them 15 minutes—including writing tests. Before this refactor, that would've been a day's work.

Testing Becomes Trivial

One of the biggest wins? Testing individual specifications is dead simple:

class UnreadFilterSpecificationTest extends TestCase
{
    public function test_filters_unread_for_team_member(): void
    {
        $teamMemberId = new TeamMemberId(1);
        $spec = new UnreadFilterSpecification(true, $teamMemberId);

        $query = DB::table('conversation as c');
        $result = $spec->apply($query);

        $this->assertStringContainsString(
            'SELECT COUNT(m.id) FROM message',
            $result->toSql()
        );
    }

    public function test_skips_filter_when_unread_is_false(): void
    {
        $teamMemberId = new TeamMemberId(1);
        $spec = new UnreadFilterSpecification(false, $teamMemberId);

        $query = DB::table('conversation as c');
        $result = $spec->apply($query);

        // Query should be unchanged
        $this->assertEquals($query->toSql(), $result->toSql());
    }
}

No mocking, no complex setup, no database seeds. Just pure unit tests.

Common Pitfalls to Avoid

Don't Create God Specifications

// ❌ Bad - too many responsibilities
class MegaFilterSpecification {
    public function apply(Builder $query): Builder {
        return $query
            ->where('role_id', $this->roleId)
            ->where('status', 'active')
            ->where('unread', true)
            ->where('search', 'like', $this->search);
    }
}

// ✅ Good - separate concerns
new RoleFilterSpecification($roleId)
new StatusFilterSpecification('active')
new UnreadFilterSpecification(true)
new SearchFilterSpecification($search)

Don't Make Specifications Depend on Each Other

// ❌ Bad - coupled specifications
class MySpec {
    public function __construct(
        private OtherSpecification $other
    ) {}
}

// ✅ Good - independent specifications composed externally
new CompositeSpecification([
    new FirstSpecification(),
    new SecondSpecification(),
]);

Don't Use Specifications for Writes

Specifications are for query building only. They shouldn't execute queries or modify data:

// ❌ Bad
class SaveConversationSpecification {
    public function apply(Builder $query): Builder {
        $query->insert(['data' => 'value']); // Don't do this!
        return $query;
    }
}

When NOT to Use This Pattern

The Specification Pattern isn't always the answer. Don't use it when:

Your queries are simple: If ->where('status', 'active') is all you need, just write that
Logic isn't reused: One-off queries don't need specifications
Team isn't familiar: Introduce it gradually, don't force it everywhere at once
Performance is critical: The pattern adds minimal overhead, but for ultra-high-performance scenarios, raw SQL might be better

Conclusion

The Specification Pattern transformed our codebase from a tangled mess into a clean, maintainable architecture. We went from 194-line methods to 38-line methods without sacrificing functionality.

More importantly, we now have:

Code that's easy to understand for new team members
Tests that actually test individual behaviors
Changes that don't break existing functionality
Confidence to refactor without fear

If you're dealing with complex repository queries in Laravel, give this pattern a try. Start small—pick your ugliest repository method and extract just one specification. You'll be amazed at how much clearer your code becomes.

Your future self (and your team) will thank you.

Resources

Have you implemented the Specification Pattern in your projects? What challenges did you face? Drop a comment below—I'd love to hear your experiences!

DEV Community: kevariable