DEV Community

ByteHire profile image kevariable
kevariable for ByteHire

Posted on with Paul Dragoonis

A practical guide to refactoring complex database queries in Laravel

#laravel #php #ddd #architecture

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
}
Enter fullscreen mode Exit fullscreen mode

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),
    ]);
}
Enter fullscreen mode Exit fullscreen mode

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;
}
Enter fullscreen mode Exit fullscreen mode

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'
        );
    }
}
Enter fullscreen mode Exit fullscreen mode

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;
    }
}
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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!
]);
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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);
    }
}
Enter fullscreen mode Exit fullscreen mode

Step 2: Use it

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

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;
    }
}
Enter fullscreen mode Exit fullscreen mode

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(...);
}
Enter fullscreen mode Exit fullscreen mode

4. Separation of Concerns

Keep SELECT logic separate from WHERE logic:

// SELECT specifications
new ConversationSelectSpecification($id)

// WHERE specifications  
new RoleFilterSpecification($roleIds)
new UnreadFilterSpecification($unread)
Enter fullscreen mode Exit fullscreen mode

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());
    }
}
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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(),
]);
Enter fullscreen mode Exit fullscreen mode

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;
    }
}
Enter fullscreen mode Exit fullscreen mode

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!

Top comments (0)