Laravel-Training-Project/app/Http/Controllers/EmployeeController.php

<?php

namespace App\Http\Controllers;

use App\Models\employee;
use App\Models\office;
use Illuminate\Http\Request;
use Yajra\DataTables\Facades\DataTables;
use Illuminate\Support\Facades\DB;
class EmployeeController extends Controller
{
    public function data(Request $request)
    {
        $searchValue = $request->search['value']; // User-provided search term

        $sql = "
    SELECT
        employees.name AS employee_name,
        employees.email,
        employees.position,
        offices.name AS office_name,
        offices.location AS office_location
    FROM
        employees
    INNER JOIN
        offices ON employees.office_id = offices.id
";
        // Apply filtering
        if (!empty($searchValue)) {
            $sql .= " WHERE employees.name LIKE '%{$searchValue}%'
                  OR employees.email LIKE '%{$searchValue}%'
                  OR employees.position LIKE '%{$searchValue}%'
                  OR offices.name LIKE '%{$searchValue}%'
                  OR offices.location LIKE '%{$searchValue}%'";
        }

// Apply sorting
        if(is_array($request->order)) {
            $orderColumnIndex = $request->order[0]['column'];
            $orderDirection = $request->order[0]['dir'];
            $columns = ['employee_name', 'email', 'position', 'office_name', 'office_location'];
            $orderColumn = $columns[$orderColumnIndex];
            $sql .= " ORDER BY {$orderColumn} {$orderDirection}";
        }


// Apply pagination
        $start = $request->start;
        $length = $request->length;
        $sql .= " LIMIT {$start}, {$length}";

// Execute query
        $results = DB::select($sql);
        $collection = collect($results);

        // Total record count for pagination
        $totalRecords = DB::select("
        SELECT COUNT(*) as count
        FROM employees
        INNER JOIN offices ON employees.office_id = offices.id
    ")[0]->count;

        // Total filtered record count
        $filteredRecords = empty($searchValue)
            ? $totalRecords
            : DB::select("
            SELECT COUNT(*) AS count
            FROM employees
            INNER JOIN offices ON employees.office_id = offices.id
            WHERE employees.name LIKE '%{$searchValue}%'
              OR employees.email LIKE '%{$searchValue}%'
              OR employees.position LIKE '%{$searchValue}%'
              OR offices.name LIKE '%{$searchValue}%'
              OR offices.location LIKE '%{$searchValue}%'"
            )[0]->count;

        // Return the paginated response
        return response()->json([
            'draw' => $request->draw,
            'recordsTotal' => $totalRecords,      // Total records without filtering
            'recordsFiltered' => $filteredRecords,  // Update if filtered search is applied
            'data' => $results                    // Current page data
        ]);
    }
}