Query Optimization

Guidelines for writing performant database queries in the CPR backend.

Eager Loading

The most common performance issue in Laravel is the N+1 query problem. Always eager load relationships when you know they'll be accessed.

In Repositories

The AbstractFilterableRepository base class supports eager loading in buildQuery():

protected function buildQuery(?string $search, ?array $filters): Builder
{
    return Transaction::query()
        ->with(['items', 'patient', 'branch', 'paymentMethod'])
        ->when($search, fn ($q) =>
            $q->where('transaction_number', 'like', "%{$search}%")
        );
}

Common Eager Loading Needs

Model	Commonly Loaded Relations
Transaction	items, patient, branch, paymentMethod
QueueTicket	visit.patient, service, billingTransactions
PatientVisit	patient, branch, queueTickets, plans
PurchaseOrder	details.pharmacyItem, supplier
Delivery	details, branch

In Resources

Use whenLoaded() in API Resources to conditionally include relations:

// Only serialized if the relation was eager-loaded
'patient' => PatientResource::make($this->whenLoaded('patient')),

Pagination

All list endpoints use pagination via AbstractFilterableRepository::getPaginated():

$results = $repository->getPaginated(
    search: $request->input('search'),
    filters: $request->input('filters', []),
    perPage: $request->input('perPage', 15)
);

Never Use Unbounded Queries

Never use ->get() on unbounded queries in production. Always paginate or limit results.

Query Scopes

Models use query scopes for reusable, composable filtering logic:

Patient::query()
    ->search($search)
    ->filterByGender($gender)
    ->filterByCivilStatus($status)
    ->filterByBirthDate($from, $to)
    ->paginate($perPage);

This keeps filtering logic in the model and out of controllers/services.

Counting Relations

Use withCount() instead of loading all related records just to count:

// Good - single query with subquery count
$branches = Branch::withCount('users')->get();
// Access: $branch->users_count

// Bad - loads all users into memory
$branches = Branch::with('users')->get();
// Access: $branch->users->count()

The BranchRepository already uses withCount('users') in its buildQuery().

Database Transactions

Wrap multi-table writes in database transactions for atomicity:

DB::transaction(function () use ($data) {
    $transaction = Transaction::create($data);
    foreach ($data['items'] as $item) {
        $transaction->items()->create($item);
    }
    // Stock deduction happens here too
});

The TransactionService wraps all create/update operations in DB::transaction().

Indexing

• All _id foreign key columns are automatically indexed by Laravel
• Add indexes for columns used in WHERE clauses, sorting, or search
• The patients table benefits from indexes on last_name, first_name, pin

Best Practices

1. Don't use all() in production code - Always paginate or add limits
2. Don't query inside loops - Use eager loading or batch queries
3. Don't load unnecessary columns - Use select() for large tables when appropriate
4. Use chunk() for batch processing - Process large datasets in memory-efficient chunks
5. Cache expensive queries - Use Redis for frequently accessed, rarely changing data (e.g., reference lookup tables)