Working with an unfamiliar database or inherited codebase? Don’t guess column names — inspect the schema directly with SHOW COLUMNS.

I recently wasted 20 minutes debugging a query that referenced projects.title when the actual column was projects.name. One SQL command would have caught it:

SHOW COLUMNS FROM projects;

This returns the table’s structure:

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| name        | varchar(255) | NO   |     | NULL    |                |
| status      | varchar(50)  | YES  |     | active  |                |
| created_at  | timestamp    | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

Use It Programmatically

In Laravel, you can inspect schemas at runtime:

$columns = DB::select('SHOW COLUMNS FROM ' . $table);

foreach ($columns as $col) {
    echo "{$col->Field} ({$col->Type})\n";
}

This is especially useful for:

• Schema validation: Verify migrations match production
• Legacy databases: Document undocumented systems
• Dynamic queries: Build column lists programmatically
• Debugging: Confirm column existence before querying

Alternative: Laravel’s Schema Facade

Laravel provides a cleaner API for this:

use Illuminate\Support\Facades\Schema;

// Check if column exists
if (Schema::hasColumn('projects', 'title')) {
    // Safe to use
}

// Get all columns
$columns = Schema::getColumnListing('projects');
// ['id', 'name', 'status', 'created_at', 'updated_at']

But SHOW COLUMNS gives you more metadata (type, nullable, defaults) when you need it. Way more reliable than assuming your migrations match production after years of hotfixes.

MySQL’s JSON_CONTAINS function lets you query JSON array columns without rigid schema changes. Need to filter rows based on values inside a JSON array? Use:

WHERE JSON_CONTAINS(config_json, '"USD"')

Notice the double quotes inside single quotes — JSON requires double-quoted strings. This searches for "USD" anywhere in the array.

When to Use JSON Columns

JSON columns are ideal for flexible metadata that doesn’t need structured querying. For example:

• Feature flags per user (varies by account type)
• Configuration options per tenant
• Tags or labels that change frequently

-- Find all users with "premium" feature enabled
SELECT * FROM accounts
WHERE JSON_CONTAINS(features, '"premium"');

The Performance Trade-Off

JSON searches cannot use regular indexes. For frequently-queried paths, consider generated columns with indexes:

-- Add a generated column for faster searches
ALTER TABLE accounts 
ADD feature_list VARCHAR(255) 
AS (JSON_UNQUOTE(JSON_EXTRACT(features, '$.enabled'))) STORED;

-- Index it
CREATE INDEX idx_features ON accounts(feature_list);

This gives you JSON’s flexibility with traditional index performance. Laravel can generate these via migrations:

Schema::table('accounts', function (Blueprint $table) {
    $table->string('feature_list')
          ->storedAs("JSON_UNQUOTE(JSON_EXTRACT(features, '$.enabled'))")
          ->index();
});

Use JSON for truly flexible data, but add generated columns + indexes for hot paths.

Ever shown users options in a dropdown that don’t actually work? I recently debugged a classic denormalization issue where the UI displayed 434 currency options based on a cached summary table, but the backend filter logic checked live data in a separate table — and only 79 of those currencies had available inventory.

The problem occurred because two different data sources were being used:

• Display logic: Read from a forecasts table that stored pre-computed aggregates
• Filter logic: Queried the actual orders table for real-time availability

The fix started with comparing aggregate counts to confirm the mismatch:

-- Backend filter (real-time availability)
SELECT COUNT(DISTINCT product_id) 
FROM orders 
WHERE status = 'available';
-- Result: 79

-- Display logic (cached aggregates)
SELECT COUNT(*) 
FROM forecasts 
WHERE JSON_CONTAINS(metadata, '"currency_code"');
-- Result: 434

The root cause? The forecast table wasn’t being updated when inventory sold out. Users saw 434 options but could only actually use 79 of them.

When to Cache vs. Query Live

Caching computed data is powerful, but it introduces a new problem: cache invalidation. Before building a cache layer, ask:

• What triggers a cache update? (e.g., order placed, inventory restocked)
• Can users tolerate stale data? (product listings: yes; checkout: no)
• Is the performance gain worth the complexity?

In this case, the better approach was to either:

1. Build the dropdown from the same real-time query the filter uses
2. Add proper cache invalidation hooks when inventory changes

Lesson: When you split display and business logic across different data sources, always verify your cache invalidation strategy matches your actual business rules. The mismatch becomes obvious when you compare the numbers.

When building arrays or JSON responses in Laravel, empty strings from operations like implode() or join() can be inconsistent with explicit null values. The Elvis operator (?:) provides a clean way to coalesce empty results to null.

The Problem

Mixing explicit nulls with empty strings creates inconsistency:

$data = [
    'name' => $user->name,  // 'John Doe'
    'email' => $user->email ?: null,  // null if empty
    'tags' => implode(', ', $user->tags),  // '' if no tags (empty string)
];

// JSON: {"name":"John Doe","email":null,"tags":""}
// Inconsistent - some nulls, some empty strings

The Solution

Use the Elvis operator for consistent null handling:

$data = [
    'name' => $user->name,
    'email' => $user->email ?: null,
    'tags' => implode(', ', $user->tags) ?: null,  // Consistent null
];

// JSON: {"name":"John Doe","email":null,"tags":null}
// Better - consistent null handling

Practical Examples

String concatenation:

'full_address' => trim("{$user->street} {$user->city} {$user->zip}") ?: null,

Array operations:

'permissions' => implode(', ', $user->permissions) ?: null,
'roles' => join(' | ', $user->roles->pluck('name')->all()) ?: null,

Filtered collections:

'active_projects' => $user->projects
    ->where('status', 'active')
    ->pluck('name')
    ->implode(', ') ?: null,

Complex string building:

'metadata' => collect([
    $user->department,
    $user->title,
    $user->location,
])
->filter()
->implode(' • ') ?: null,

Why This Matters

1. API Consistency

Frontend code can check if (value === null) instead of if (!value || value === '').

2. Database Consistency

NULL vs empty string handling in database columns is explicit.

3. Type Safety

TypeScript/PHP strict typing works better with explicit nulls:

interface User {
    name: string;
    email: string | null;  // Clear intent
    tags: string | null;   // Not string | null | ''
}

Combine with Null Coalescing for Defaults

Chain the Elvis operator with null coalescing for fallback values:

'display_name' => implode(' ', [
    $user->first_name,
    $user->last_name,
]) ?: $user->email ?? 'Unknown User',

// Evaluation order:
// 1. Try implode (may return '')
// 2. Elvis converts '' to null
// 3. Null coalescing provides fallback

Common patterns:

// Tags with fallback
'tags_display' => implode(', ', $post->tags) ?: 'No tags',

// Joined data with fallback
'categories' => join(' / ', $product->categories) ?: 'Uncategorized',

// Filtered list with fallback
'assigned_to' => $task->assignees
    ->pluck('name')
    ->implode(', ') ?: 'Unassigned',

The pattern makes your API responses predictable and easier to work with on both backend and frontend.

When working with complex query builders or fluent APIs in Laravel, proper formatting makes a huge difference in code maintainability. Breaking long inline chains into multi-line structures with consistent indentation helps future developers (including you) understand the flow at a glance.

Before: Hard to Read

Single-line method chains are difficult to parse:

$data = Model::fromSub(array_reduce([$query1, $query2, $query3], fn($sub, $q) => $sub ? $sub->union($q->toBase()) : $q->toBase()), 'items')->with('relation1', 'relation2')->get();

After: Much Better

The same logic with proper line breaks:

$data = Model::fromSub(
    array_reduce(
        [$query1, $query2, $query3],
        fn($sub, $q) => $sub 
            ? $sub->union($q->toBase()) 
            : $q->toBase()
    ),
    'items'
)
->with('relation1', 'relation2')
->get();

Key Formatting Principles

1. One logical step per line

// Bad
$users = User::with('posts')->where('active', true)->orderBy('name')->get();

// Good
$users = User::with('posts')
    ->where('active', true)
    ->orderBy('name')
    ->get();

2. Indent nested structures consistently

$results = Report::query()
    ->select([
        'reports.*',
        DB::raw('COUNT(comments.id) as comment_count'),
    ])
    ->leftJoin('comments', function ($join) {
        $join->on('comments.report_id', '=', 'reports.id')
            ->where('comments.approved', true);
    })
    ->groupBy('reports.id')
    ->having('comment_count', '>', 5)
    ->get();

3. Align related parameters vertically

$data = Task::with([
    'project',
    'assignee.department',
    'comments.author',
    'attachments',
])
->whereIn('status', [
    'pending',
    'in_progress',
    'review',
])
->get();

4. Break closures into multiple lines when they contain logic

// Single-line is fine for simple closures
$ids = $collection->map(fn($item) => $item->id);

// Multi-line for complex logic
$formatted = $collection->map(function ($item) {
    return [
        'id' => $item->id,
        'name' => $item->name,
        'status' => $item->getStatusLabel(),
    ];
});

Real-World Example

Extract complex nested structures to variables for clarity:

// Extract the union query for readability
$unionQuery = array_reduce(
    [
        Report::where('type', 'daily'),
        Report::where('type', 'weekly'),
        Report::where('type', 'monthly'),
    ],
    fn($sub, $query) => $sub 
        ? $sub->union($query->toBase()) 
        : $query->toBase()
);

// Now the main query is much clearer
$results = Report::withTrashed()
    ->fromSub($unionQuery, 'reports')
    ->with([
        'author',
        'department',
        'approvals.user',
    ])
    ->orderByDesc('created_at')
    ->paginate(50);

Your IDE’s auto-formatter may not always get this right — sometimes manual formatting wins for clarity. The goal is to make the code’s intent obvious at a glance.

When you need to union multiple query builders dynamically, array_reduce provides a clean alternative to chaining .union() calls manually. This is especially useful when the number of queries varies or comes from configuration.

The Problem with Manual Chaining

When building complex queries that combine multiple query builders with UNION, manual chaining becomes verbose:

$query1 = Order::where('status', 'pending')->toBase();
$query2 = Order::where('status', 'processing')->toBase();
$query3 = Order::where('status', 'completed')->toBase();

$combined = $query1->union($query2)->union($query3);

This gets unwieldy when:
– The number of queries changes
– Queries come from configuration
– You’re building queries conditionally

Use array_reduce Instead

array_reduce lets you build the union dynamically:

$queries = [
    Order::where('status', 'pending'),
    Order::where('status', 'processing'),
    Order::where('status', 'completed'),
];

$combined = array_reduce(
    $queries,
    fn($sub, $query) => $sub ? $sub->union($query->toBase()) : $query->toBase()
);

The closure handles the first iteration (when $sub is null) and subsequent iterations (when $sub contains the accumulated union).

Combine with fromSub for Complex Queries

This pattern shines when used with Eloquent’s fromSub():

$data = Task::query()
    ->fromSub(
        array_reduce(
            [
                Task::where('priority', 'high'),
                Task::where('priority', 'urgent'),
                Task::where('status', 'overdue'),
            ],
            fn($sub, $query) => $sub 
                ? $sub->union($query->toBase()) 
                : $query->toBase()
        ),
        'tasks'
    )
    ->with('project', 'assignee')
    ->get();

This gives you a clean subquery with proper eager loading.

Works with Any Number of Queries

The real power is flexibility:

// Configuration-driven queries
$statusQueries = config('report.statuses')
    ->map(fn($status) => Report::where('status', $status));

$results = Report::withTrashed()
    ->fromSub(
        array_reduce(
            $statusQueries->all(),
            fn($sub, $q) => $sub ? $sub->union($q->toBase()) : $q->toBase()
        ),
        'reports'
    )
    ->orderByDesc('created_at')
    ->get();

// Conditional queries
$queries = collect([
    $request->filled('active') ? Item::where('is_active', true) : null,
    $request->filled('pending') ? Item::where('status', 'pending') : null,
    $request->filled('archived') ? Item::onlyTrashed() : null,
])->filter();

$items = Item::fromSub(
    array_reduce(
        $queries->all(),
        fn($sub, $q) => $sub ? $sub->union($q->toBase()) : $q->toBase()
    ),
    'items'
)->paginate();

The pattern keeps your code DRY when query sources change or grow.

Repository vs Service: What Goes Where in Laravel

When refactoring Laravel applications, one common anti-pattern I see is services that directly perform database queries. This violates separation of concerns and makes code harder to test and maintain.

The Problem: Bloated Service Classes

Consider a ReportService that handles business logic for generating reports. Over time, it accumulates methods like:

class ReportService
{
    public function generateReport($projectId, $startDate, $endDate)
    {
        // Business logic here
        $data = $this->fetchReportData($projectId, $startDate, $endDate);
        // More business logic
    }

    private function fetchReportData($projectId, $startDate, $endDate)
    {
        // Direct database query
        return DB::table('reports')
            ->where('project_id', $projectId)
            ->whereBetween('created_at', [$startDate, $endDate])
            ->get();
    }

    private function getProjectSettings($projectId)
    {
        // Another direct database query
        return DB::table('project_settings')
            ->where('project_id', $projectId)
            ->first();
    }
}

This service is doing two jobs: orchestrating business logic AND querying the database.

The Solution: Move Database Queries to Repositories

Repositories should handle all database access. Services should orchestrate business logic by calling repositories.

Create a Repository:

class ReportRepository
{
    public function findReportData($projectId, $startDate, $endDate)
    {
        return DB::table('reports')
            ->where('project_id', $projectId)
            ->whereBetween('created_at', [$startDate, $endDate])
            ->get();
    }

    public function getProjectSettings($projectId)
    {
        return DB::table('project_settings')
            ->where('project_id', $projectId)
            ->first();
    }
}

Clean up the Service:

class ReportService
{
    public function __construct(
        private ReportRepository $reportRepo
    ) {}

    public function generateReport($projectId, $startDate, $endDate)
    {
        // Pure business logic - no database queries
        $data = $this->reportRepo->findReportData($projectId, $startDate, $endDate);
        $settings = $this->reportRepo->getProjectSettings($projectId);
        
        // Apply business rules, transformations, etc.
        return $this->processReportData($data, $settings);
    }
}

The Rule

Services orchestrate. Repositories query.

If your service has DB::table() or Eloquent queries, move them to a repository. Your service should read like a business workflow, not a database script.

Bonus: Testing Becomes Easier

With this separation, you can mock the repository in tests:

public function test_generates_report()
{
    $mockRepo = Mockery::mock(ReportRepository::class);
    $mockRepo->shouldReceive('findReportData')->once()->andReturn(collect([...]));
    
    $service = new ReportService($mockRepo);
    $result = $service->generateReport(1, '2024-01-01', '2024-12-31');
    
    $this->assertInstanceOf(Report::class, $result);
}

Clean separation = easier testing + clearer code architecture.

When building systems with both user interfaces and internal logic, explicitly separating display names from system identifiers prevents cascading changes when marketing decides to rename a feature.

The pattern: use stable identifiers (type, id, slug) for code/database/filenames, and store display names (name, label) separately in configuration:

// config/features.php
return [
    'available_reports' => [
        [
            'type' => 'sales_report',               // Stable system identifier
            'name' => 'Quarterly Sales Analysis',   // User-facing display name
            'permission' => 'view_sales_reports',
        ],
        [
            'type' => 'inventory_report',
            'name' => 'Stock Level Summary',
            'permission' => 'view_inventory',
        ],
    ],
];

In your controllers:

class ReportController
{
    public function index()
    {
        $reports = collect(config('features.available_reports'))
            ->filter(fn ($report) => auth()->user()->can($report['permission'] ?? ''))
            ->map(fn ($report) => [
                'id' => $report['type'],      // Internal ID for API/routes
                'label' => $report['name'],   // Display name for UI
            ]);
            
        return view('reports.index', compact('reports'));
    }
    
    public function generate(string $reportType)
    {
        // Use 'type' for routing, job dispatch, filename generation
        $job = match ($reportType) {
            'sales_report' => new GenerateSalesReport(),
            'inventory_report' => new GenerateInventoryReport(),
            default => throw new InvalidArgumentException(),
        };
        
        dispatch($job);
    }
}

Job classes use the stable identifier:

class GenerateSalesReport implements ShouldQueue
{
    public function handle()
    {
        $filename = 'sales_report_' . now()->format('Y-m-d') . '.pdf';
        
        Storage::put("exports/{$filename}", $this->generatePdf());
        
        // Filename: 'sales_report_2024-03-05.pdf' — never changes
    }
}

Why this matters:

• Marketing freedom: “Quarterly Sales Analysis” can become “Revenue Insights Dashboard” without touching code
• Stability: Database queries, API endpoints, and filenames don’t break when display names change
• A/B testing: Easily test different labels for the same feature
• Internationalization: Display names can be translated while system identifiers stay English

What NOT to do:

// ❌ DON'T couple display names to class constants
class GenerateSalesReport
{
    public const DISPLAY_NAME = 'Quarterly Sales Analysis';
    
    public function getFilename()
    {
        return self::DISPLAY_NAME . '_' . now()->format('Y-m-d') . '.pdf';
        // Filename: 'Quarterly Sales Analysis_2024-03-05.pdf' — spaces, changes when label changes
    }
}

// ❌ DON'T hardcode display names in multiple places
// Controller
$reportName = 'Quarterly Sales Analysis';

// Blade view
Quarterly Sales Analysis

// Email notification
Mail::send(..., ['report' => 'Quarterly Sales Analysis']);

// Now you have 3+ places to update when marketing changes the name

The right approach:

• Store display names in config/*.php or database tables where non-developers can update them
• Use system identifiers everywhere in code (sales_report, not "Quarterly Sales Analysis")
• Fetch display names at runtime from the centralized source

Your codebase becomes more flexible, and non-technical stakeholders can update user-facing labels without opening a pull request.

When generating filenames from dynamic input—user-provided names, database values, or API responses—a three-step Laravel string helper chain ensures clean, filesystem-safe output.

The pattern combines Str::lower(), Str::slug(), and Str::title() to handle edge cases you might not think of:

use Illuminate\Support\Str;

class FileGenerator
{
    public static function sanitizeFilename(string $name): string
    {
        return Str::title(Str::slug(Str::lower($name), '_'));
    }
}

// Examples
FileGenerator::sanitizeFilename('Q4 Sales Report');
// Returns: 'Q4_Sales_Report'

FileGenerator::sanitizeFilename('User Analytics (2024)');
// Returns: 'User_Analytics_2024'

FileGenerator::sanitizeFilename('Employee List - HR Dept.');
// Returns: 'Employee_List_Hr_Dept'

FileGenerator::sanitizeFilename('Données françaises');
// Returns: 'Donnees_Francaises'

Why this three-step chain works:

1. Str::lower() normalizes case to avoid filesystem issues on case-sensitive systems (Linux servers are case-sensitive, Windows/Mac are not)
2. Str::slug() converts to URL-safe format, replacing spaces and special characters with your chosen separator (underscore here)
3. Str::title() capitalizes words for readable filenames without breaking filesystem compatibility

Real-world usage with timestamps:

class ReportExporter
{
    public function export(string $reportName, array $data): string
    {
        $filename = FileGenerator::sanitizeFilename($reportName) 
                    . '_' . now()->format('Y-m-d_His') 
                    . '.csv';
        
        Storage::put("exports/{$filename}", $this->toCsv($data));
        
        return $filename;
    }
}

// Output: 'Monthly_Revenue_2024-03-05_093045.csv'

Why not just use Str::slug() alone?

Plain Str::slug() would give you 'q4-sales-report' (all lowercase). The Str::title() step makes filenames more readable when users download them. Compare:

• Without title case: employee_performance_report.csv
• With title case: Employee_Performance_Report.csv

The second is clearer at a glance in file explorers.

Alternative separators:

You can use hyphens instead of underscores by changing the second parameter to Str::slug():

return Str::title(Str::slug(Str::lower($name), '-'));

This is common for web-facing URLs. Underscores are traditional for downloaded files, but both work fine for filesystems.

If you’re building multiple Laravel AI agents, you’ll notice a lot of repetitive setup code in every agent class:

// ❌ Every agent repeats the same middleware setup
class CustomerSupport implements Agent, HasMiddleware
{
    use Promptable;

    public function middleware(): array
    {
        return [
            RateLimitMiddleware::class,
            LoggingMiddleware::class,
            RetryMiddleware::class,
        ];
    }
}

class ReportGenerator implements Agent, HasMiddleware
{
    use Promptable;

    public function middleware(): array
    {
        return [
            RateLimitMiddleware::class,
            LoggingMiddleware::class,
            RetryMiddleware::class,
        ];
    }
}

DRY it up with a base agent:

// app/Ai/Agents/BaseAgent.php
abstract class BaseAgent implements Agent, HasMiddleware
{
    use Promptable;

    public function middleware(): array
    {
        return [
            RateLimitMiddleware::class,
            LoggingMiddleware::class,
            RetryMiddleware::class,
        ];
    }
}

Now every agent just extends BaseAgent:

// ✅ Clean and minimal
class CustomerSupport extends BaseAgent
{
    // Just your agent-specific logic
}

class ReportGenerator extends BaseAgent
{
    // Just your agent-specific logic
}

Benefits:

• Add/remove middleware in one place – affects all agents instantly
• Onboarding a new agent? One line: extends BaseAgent
• Override middleware for specific agents if needed (just override the middleware() method)

Example override for special cases:

// Most agents use BaseAgent defaults
class BulkProcessor extends BaseAgent
{
    // Different rate limit for bulk operations
    public function middleware(): array
    {
        return [
            BulkRateLimitMiddleware::class,
            ...parent::middleware(),
        ];
    }
}

Pattern applies beyond agents: Same concept works for controllers (BaseController), jobs (BaseJob), or any class hierarchy where you have shared setup.