laravel-database-expert

Laravel Database Expert

Priority: P1 (HIGH)

Structure

config/
└── database.php        # Connection & Cluster config
app/
└── Http/
    └── Controllers/    # Query logic entry points

Implementation Guidelines

Advanced Query Builder

• Complex Joins: Prefer joinSub($subquery, 'alias', ...) and whereExists(fn($q) => $q->select(DB::raw(1))...) over raw SQL or whereIn for correlated subqueries.
• Subqueries: Use addSelect with a DB::raw subquery to avoid N+1 issues.
• Aggregates: Use withCount(), withSum(), and withAvg() directly via Eloquent for optimized column-based aggregation.
• Raw Expressions: Always use selectRaw or whereRaw with bindings; never use string concatenation in raw queries.

Caching Strategy (Redis/Memcached)

• Cache-Aside: Utilize Cache::remember('key', $ttl, $closure) for frequently accessed data (e.g., posts.all).
• Redis Tagging: Group related keys using Cache::tags(['posts', 'user:1']) for grouped invalidation.
• Invalidation: Call Cache::tags(['posts'])->flush() to clear specific subsets; never use Cache::flush() globally in production.

Scalability & Infrastructure

• Read/Write Splitting: Configure 'read' and 'write' keys in config/database.php mysql/pgsql connections. Laravel automatically routes SELECT to read and INSERT/UPDATE/DELETE to write; no code changes needed.
• Indices: Ensure correct database indexes are present on all join and aggregate columns.

Anti-Patterns

• No string SQL concatenation: Use bindings or Query Builder.
• No queries in loops: Use subqueries, joins, or aggregates.
• No Cache::flush(): Use tags to target specific cache groups.
• No direct Redis calls: Use Laravel Cache wrappers consistently.

References

• Advanced SQL & Cache Patterns