Employee leave dashboard 04 · Advanced
Build a company-scoped dashboard from three related tables: leave_requests, employees and leave_types. The example has a client-side snapshot and a server-side dashboard. They share the query, row mapper, Blade view and JavaScript initializer.
Try the interactive preview first. It runs in the documentation site without Laravel. Follow the steps below to run the real queries and check permissions.
1. Set up a local demo
Use a local Laravel app with EasyDataTable installed, SQLite configured, and a working login. A plain Laravel skeleton does not include a login screen; start from an authenticated app or install a Laravel starter kit for your version before continuing. Keep the auth middleware.
Import the leave fixture
Save demo.sql as leave-demo.sql at the Laravel app root. Run php artisan tinker and enter these lines once:
\Illuminate\Support\Facades\DB::unprepared(file_get_contents(base_path('leave-demo.sql')));
\Illuminate\Support\Facades\DB::table('leave_requests')->count();Expect 8 requests across two companies. Exit the Tinker prompt with exit. Import into an isolated demo database, not into a database with existing leave tables or real employee data.
Configure demo accounts and review permission
The controller expects a trusted company_id on the signed-in user and checks the review-leave-requests Gate. If your application already has company membership and a permissions policy, use those. For an isolated Laravel 13 demo using the default App\Models\User, use this setup:
Add demo user fields and accounts
If your
userstable has neither field, runphp artisan make:migration add_leave_demo_fields_to_users_table --table=users. Replace the generatedup()anddown()methods with these. The generated file already importsSchemaandBlueprint. Skip this migration if both fields exist; if just one is missing, add and drop only that field.phppublic function up(): void { Schema::table('users', function (Blueprint $table) { $table->unsignedBigInteger('company_id')->nullable(); $table->string('role')->default('employee'); }); } public function down(): void { Schema::table('users', function (Blueprint $table) { $table->dropColumn(['company_id', 'role']); }); }Run
php artisan migrateon the demo database. Openphp artisan tinker, paste the following and note the generated local password for the three demo accounts. Enterexitwhen done.php$password = bin2hex(random_bytes(12)); foreach ([ 'reviewer@example.test' => [10, 'hr'], 'employee@example.test' => [10, 'employee'], 'other-reviewer@example.test' => [20, 'hr'], ] as $email => [$companyId, $role]) { $user = \App\Models\User::firstOrNew(['email' => $email]); $user->forceFill([ 'name' => $email, 'company_id' => $companyId, 'role' => $role, 'password' => \Illuminate\Support\Facades\Hash::make($password), 'email_verified_at' => now(), ])->save(); } $password;
For those demo accounts, add use App\Models\User; and use Illuminate\Support\Facades\Gate; to app/Providers/AppServiceProvider.php if they are not present. Inside the existing boot() method, add:
Gate::define('review-leave-requests', static fn (User $user): bool => $user->role === 'hr');2. Copy the dashboard files
Copy all three files from the repository into the Laravel app, creating the destination directories if needed:
| Source in EasyDataTable | Destination in your Laravel app |
|---|---|
LeaveTableController.php | app/Http/Controllers/LeaveTableController.php |
leave.blade.php | resources/views/examples/leave.blade.php |
table.js | public/examples/leave/table.js |
Add use App\Http\Controllers\LeaveTableController; and use Illuminate\Support\Facades\Route; to the top of routes/web.php (keep each import once), then add the route group:
Route::middleware('auth')->prefix('examples/leave')->name('examples.leave.')->group(function () {
Route::view('/client', 'examples.leave', ['serverSide' => false]);
Route::view('/server', 'examples.leave', ['serverSide' => true]);
Route::get('/client-data', [LeaveTableController::class, 'client'])->name('client-data');
Route::get('/server-data', [LeaveTableController::class, 'server'])->name('server-data');
Route::get('/review/{id}', [LeaveTableController::class, 'review'])->whereNumber('id')->name('review');
});Run php artisan route:list --path=examples/leave. You should see five GET routes: two pages, two JSON endpoints and the review endpoint. Both pages use the same Blade file. The serverSide value passed by the route determines which named endpoint and which browser controls the view uses.
3. Understand scope, search and actions
Join only rows the current user may see
/** Keep tenant restrictions and the public column list common to both modes. */
private function visibleRequests(Request $request): Builder
{
$companyId = $request->user()->company_id ?? null;
abort_if($companyId === null, 403);
return DB::table('leave_requests')
->join('employees', 'employees.id', '=', 'leave_requests.employee_id')
->leftJoin('leave_types', 'leave_types.id', '=', 'leave_requests.leave_type_id')
->where('employees.company_id', $companyId)
->select(
'leave_requests.id as request_id',
'employees.code as employee_code',
'employees.name as employee',
'leave_types.name as leave_type',
'leave_requests.reason',
'leave_requests.starts_on',
'leave_requests.status'
)
->orderByDesc('leave_requests.id');
}The company comes from the signed-in user, not from a browser parameter. The explicit projection defines the public field names: JavaScript sorts employee, but the query searches employees.name. The left join keeps requests with no leave type.
Apply external filters before global search
The browser sends status and started_since on each server-side draw. The controller validates both filters before using them. It also caps each request at 100 rows; the browser's page-size menu offers 5, 10 and 25.
/** Apply dashboard filters and process a single DataTables draw. */
public function server(Request $request): array
{
$query = $this->visibleRequests($request);
$filters = $request->validate([
'length' => ['required', 'integer', 'between:1,100'],
'status' => ['nullable', 'in:pending,approved,rejected'],
'started_since' => ['nullable', 'date_format:Y-m-d'],
]);
if (isset($filters['status'])) {
$query->where('leave_requests.status', $filters['status']);
}
if (isset($filters['started_since'])) {
$query->where('leave_requests.starts_on', '>=', $filters['started_since']);
}
$canReview = $request->user()->can('review-leave-requests');
return EasyDataTable::init()
->serverSide()
->request($request)
->query($query)
->search(static function (Builder $query, string $term): Builder {
return $query->where(static function (Builder $match) use ($term): void {
$match->where('employees.name', 'like', "%{$term}%")
->orWhere('employees.code', 'like', "%{$term}%")
->orWhere('leave_types.name', 'like', "%{$term}%")
->orWhere('leave_requests.reason', 'like', "%{$term}%");
});
})
->map(fn ($row) => $this->present($row, $canReview))
->response();
}Status and start date restrict the base query and therefore recordsTotal. Global search applies on top and determines recordsFiltered.
Keep OR conditions inside one group
The intended condition is company AND filters AND (employee OR code OR type OR reason). An orWhere() added directly to the base builder can bypass the company restriction.
Format rows and authorize actions
/** Preserve ISO dates for sorting and expose a review link only for eligible rows. */
private function present(object $row, bool $canReview): array
{
return [
'request_id' => (int) $row->request_id,
'employee_code' => $row->employee_code,
'employee' => $row->employee,
'leave_type' => $row->leave_type ?? 'Uncategorized',
'reason' => $row->reason,
'starts_on' => $row->starts_on,
'status' => $row->status,
'review_url' => $canReview && $row->status === 'pending'
? route('examples.leave.review', ['id' => $row->request_id], false)
: null,
];
}Keep dates as ISO strings for sorting. The frontend displays them in the user's locale, escapes names and reasons as text, and creates a review link only when review_url is non-null. review_url is a mapped field, so its column is not orderable.
A hidden action is not authorization
The review route checks the Gate and loads the pending record within the user's company. The sample GET route returns details without updating them. A real write action needs its own authorization and CSRF protection.
4. Run and verify both modes
From the app root, start php artisan serve --host=127.0.0.1 --port=8000. Open the app in your browser and sign in with reviewer@example.test (company 10). Append these paths to http://127.0.0.1:8000:
| What to open or do | What you should see |
|---|---|
/examples/leave/client | Seven visible requests total. Searching for Vacation leaves three matches without a new Ajax request. |
/examples/leave/server | Seven visible requests total, five on the first page. Searching, sorting and paging send new Ajax requests. |
| Select Pending on the server page, leaving the other filters empty. | recordsTotal: 3, recordsFiltered: 3. |
Search for Vacation, then set the date to 2026-10-10. | The date and status leave two rows before search. The Ajax response has recordsTotal: 2, recordsFiltered: 1 and only request 104 in data. |
/examples/leave/review/104 as the reviewer, then as employee@example.test. | The reviewer receives JSON details. The employee receives 403 and does not see review links on the dashboard. |
/examples/leave/review/201 as the company 10 reviewer. | 404. Signing in as other-reviewer@example.test in company 20 instead shows only request 201 in the dashboard and allows reviewing it. |
Read recordsTotal and recordsFiltered in the /examples/leave/server-data response in the browser's Network tab. Clear filters before switching accounts.
Read the Blade and JavaScript together
The initializer waits 300 milliseconds after typing before searching. Changing status or date reloads the Ajax source and resets pagination. Responsive prioritizes the employee and status columns on small screens; the error region surfaces invalid filters or a failed request.
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Leave requests | EasyDataTable</title>
<link rel="stylesheet" href="https://cdn.datatables.net/2.3.4/css/dataTables.dataTables.min.css">
<link rel="stylesheet" href="https://cdn.datatables.net/responsive/3.0.7/css/responsive.dataTables.min.css">
<style>
body { max-width: 76rem; margin: 3rem auto; padding: 0 1.25rem; font: 1rem/1.5 system-ui, sans-serif; }
.filters { display: flex; flex-wrap: wrap; gap: 1rem; margin-block: 1.5rem; }
.filters label { display: grid; gap: .35rem; }
.filters :is(input, select) { padding: .5rem; font: inherit; }
.status { display: inline-block; padding: .15rem .65rem; border-radius: 2rem; background: #e9eef5; }
.status[data-status="pending"] { background: #fff1cd; color: #794600; }
.status[data-status="approved"] { background: #dcf4ea; color: #135c43; }
.status[data-status="rejected"], [role="alert"] { color: #972b2b; }
.status[data-status="rejected"] { background: #fbe4e4; }
</style>
</head>
<body>
<main data-leave-dashboard>
<h1>Employee leave requests</h1>
<p>{{ $serverSide ? 'Server-side dashboard' : 'Client-side snapshot' }} for your company.</p>
<div class="filters">
<label>
Search requests
<input type="search" data-leave-search placeholder="Try Vacation or E-100">
</label>
@if ($serverSide)
<label>
Status
<select data-leave-status>
<option value="">All statuses</option>
<option value="pending">Pending</option>
<option value="approved">Approved</option>
<option value="rejected">Rejected</option>
</select>
</label>
<label>
Starts on or after
<input type="date" data-leave-from>
</label>
@endif
</div>
<p role="alert" data-leave-error hidden></p>
<table class="display" style="width: 100%"
data-source="{{ route($serverSide ? 'examples.leave.server-data' : 'examples.leave.client-data') }}"
data-server-side="{{ $serverSide ? 'true' : 'false' }}">
<thead>
<tr>
<th>Request</th><th>Code</th><th>Employee</th><th>Type</th>
<th>Reason</th><th>Starts on</th><th>Status</th><th>Review</th>
</tr>
</thead>
<tbody></tbody>
</table>
</main>
<script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
<script src="https://cdn.datatables.net/2.3.4/js/dataTables.min.js"></script>
<script src="https://cdn.datatables.net/responsive/3.0.7/js/dataTables.responsive.min.js"></script>
<script type="module">
import { mountLeaveTable } from "{{ asset('examples/leave/table.js') }}";
mountLeaveTable(document.querySelector('[data-leave-dashboard]'));
</script>
</body>
</html>/**
* Mount the dashboard after jQuery, DataTables 2 and its Responsive extension load.
* The table's data attributes select the endpoint and processing mode.
*/
export function mountLeaveTable(dashboard) {
const DataTable = window.DataTable;
const $table = window.jQuery(dashboard.querySelector('table'));
const search = dashboard.querySelector('[data-leave-search]');
const status = dashboard.querySelector('[data-leave-status]');
const from = dashboard.querySelector('[data-leave-from]');
const error = dashboard.querySelector('[data-leave-error]');
const serverSide = $table.attr('data-server-side') === 'true';
const text = DataTable.render.text();
const date = new Intl.DateTimeFormat(undefined, { dateStyle: 'medium', timeZone: 'UTC' });
$table.on('xhr.dt', (_event, _settings, json, xhr) => {
error.hidden = json !== null;
if (json === null) {
error.textContent = xhr?.status === 422
? 'The filters were rejected. Check the requested date, status, and page size.'
: 'Could not load leave requests. Check your connection and sign-in.';
return true;
}
});
const table = $table.DataTable({
serverSide,
processing: true,
responsive: true,
orderMulti: false,
order: [[0, 'desc']],
pageLength: 5,
lengthMenu: [5, 10, 25],
layout: { topEnd: null },
ajax: {
url: $table.attr('data-source'),
headers: { Accept: 'application/json' },
data(params) {
if (serverSide && status.value) params.status = status.value;
if (serverSide && from.value) params.started_since = from.value;
}
},
columns: [
{ data: 'request_id', responsivePriority: 3 },
{ data: 'employee_code', render: text },
{ data: 'employee', render: text, responsivePriority: 1 },
{ data: 'leave_type', render: text },
{ data: 'reason', render: text },
{
data: 'starts_on',
render: (value, type) => type === 'display' ? date.format(new Date(`${value}T00:00:00Z`)) : value
},
{
data: 'status',
responsivePriority: 2,
render(value, type) {
if (type !== 'display') return value;
const badge = document.createElement('span');
badge.className = 'status';
badge.dataset.status = value;
badge.textContent = value[0].toUpperCase() + value.slice(1);
return badge;
}
},
{
data: 'review_url',
orderable: false,
searchable: false,
render(url, type) {
if (type !== 'display' || !url) return '';
const link = document.createElement('a');
link.href = url;
link.textContent = 'Review';
return link;
}
}
]
});
search.addEventListener('input', DataTable.util.debounce(() => table.search(search.value).draw(), 300));
if (serverSide) {
status.addEventListener('change', () => table.ajax.reload());
from.addEventListener('change', () => table.ajax.reload());
}
return table;
}Use the same initializer with Metronic and an external toolbar.
Bonus: report by leave type
To sort aggregate counts, give them a name in an outer query. Add this method to the same LeaveTableController class if you want a separate report endpoint:
public function report(Request $request): array
{
$request->validate(['length' => ['required', 'integer', 'between:1,100']]);
$byType = $this->visibleRequests($request)->reorder()
->select('leave_types.name as leave_type')
->selectRaw('count(*) as request_count')
->groupBy('leave_types.name');
$report = $byType->newQuery()
->fromSub($byType, 'leave_summary')
->select('leave_type', 'request_count')
->orderByDesc('request_count');
return EasyDataTable::init()
->serverSide()
->request($request)
->query($report)
->search(fn ($query, $term) => $query->where('leave_type', 'like', "%{$term}%"))
->response();
}Inside the existing authenticated examples/leave route group, add:
Route::get('/report-data', [LeaveTableController::class, 'report'])->name('report-data');Open /examples/leave/report-data?draw=1&start=0&length=10 while signed in to company 10. The totals are both 4, and request_count values are 3, 2, 1, and 1. One row now represents a leave type, including a null type for uncategorized requests. A report table would use leave_type and request_count as its columns[].data keys, with defaultContent: 'Uncategorized' for the nullable type.
