SmartTables.js

// Express.js server-side implementation
const express = require('express');
const app = express();
const cors = require('cors');

app.use(cors());
app.use(express.json());

// Sample data - in production, this would come from a database
const users = [
  { id: 1, name: 'John Doe', department: 'Engineering', age: 32, salary: 85000 },
  { id: 2, name: 'Jane Smith', department: 'Marketing', age: 28, salary: 65000 },
  // ... more records
];

// Column mapping for sorting
const columnMap = {
  0: 'id',
  1: 'name',
  2: 'department',
  3: 'age',
  4: 'salary'
};

// Endpoint for table data
app.get('/api/users', (req, res) => {
  try {
    // Parse parameters
    const draw = parseInt(req.query.draw) || 1;
    const page = parseInt(req.query.page) || 1;
    const perPage = parseInt(req.query.perPage) || 10;
    const search = req.query.search?.value || '';
    const order = req.query.order ? req.query.order[0] : null;
    
    // Apply search filter
    let filteredData = users;
    if (search) {
      const searchLower = search.toLowerCase();
      filteredData = users.filter(item => {
        return Object.values(item).some(val => 
          String(val).toLowerCase().includes(searchLower)
        );
      });
    }
    
    // Track total before sorting/pagination
    const recordsTotal = users.length;
    const recordsFiltered = filteredData.length;
    
    // Apply sorting
    if (order && order.column !== undefined) {
      const columnIndex = order.column;
      const columnKey = columnMap[columnIndex];
      const direction = order.dir || 'asc';
      
      if (columnKey) {
        filteredData.sort((a, b) => {
          const aVal = a[columnKey];
          const bVal = b[columnKey];
          
          // Handle numeric values
          if (typeof aVal === 'number' && typeof bVal === 'number') {
            return direction === 'asc' ? aVal - bVal : bVal - aVal;
          }
          
          // Handle strings
          const aStr = String(aVal).toLowerCase();
          const bStr = String(bVal).toLowerCase();
          return direction === 'asc' 
            ? aStr.localeCompare(bStr) 
            : bStr.localeCompare(aStr);
        });
      }
    }
    
    // Apply pagination
    const offset = (page - 1) * perPage;
    const paginatedData = filteredData.slice(offset, offset + perPage);
    
    // Return response in the format expected by SmartTables
    res.json({
      draw,
      recordsTotal,
      recordsFiltered,
      data: paginatedData
    });
  } catch (error) {
    console.error('Error processing request:', error);
    res.status(500).json({ error: 'Internal server error' });
  }
});

app.listen(3000, () => {
  console.log('Server running on port 3000');
});

// users.controller.ts
import { Controller, Get, Query } from '@nestjs/common';
import { UsersService } from './users.service';

@Controller('api/users')
export class UsersController {
  constructor(private readonly usersService: UsersService) {}

  @Get()
  findAll(@Query() query) {
    const draw = parseInt(query.draw) || 1;
    const page = parseInt(query.page) || 1;
    const perPage = parseInt(query.perPage) || 10;
    const search = query.search?.value || '';
    const order = query.order ? query.order[0] : null;
    
    return this.usersService.findWithPagination(draw, page, perPage, search, order);
  }
}

// users.service.ts
import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { User } from './user.entity';

@Injectable()
export class UsersService {
  private columnMap = {
    0: 'id',
    1: 'name',
    2: 'department',
    3: 'age',
    4: 'salary'
  };

  constructor(
    @InjectRepository(User)
    private usersRepository: Repository,
  ) {}

  async findWithPagination(draw, page, perPage, search, order) {
    // Build query with QueryBuilder
    const queryBuilder = this.usersRepository.createQueryBuilder('user');
    
    // Apply search
    if (search) {
      queryBuilder.where(
        'user.name LIKE :search OR user.department LIKE :search',
        { search: '%' + search + '%' }
      );
    }
    
    // Get total count before pagination
    const recordsTotal = await this.usersRepository.count();
    
    // Get filtered count
    const recordsFiltered = await queryBuilder.getCount();
    
    // Apply sorting
    if (order && order.column !== undefined) {
      const columnKey = this.columnMap[order.column];
      if (columnKey) {
        // Use string concatenation instead of template literals
        queryBuilder.orderBy('user.' + columnKey, order.dir.toUpperCase());
      }
    }
    
    // Apply pagination
    queryBuilder
      .skip((page - 1) * perPage)
      .take(perPage);
    
    // Execute query
    const data = await queryBuilder.getMany();
    
    // Return formatted response
    return {
      draw,
      recordsTotal,
      recordsFiltered,
      data
    };
  }
}

# views.py
from django.http import JsonResponse
from django.db.models import Q
from .models import Employee

def employee_data(request):
    # Parse request parameters
    draw = int(request.GET.get('draw', 1))
    page = int(request.GET.get('page', 1))
    per_page = int(request.GET.get('perPage', 10))
    search_value = request.GET.get('search[value]', '')
    order_column = request.GET.get('order[0][column]', '')
    order_dir = request.GET.get('order[0][dir]', 'asc')
    
    # Column mapping
    column_map = {
        '0': 'id',
        '1': 'name',
        '2': 'department',
        '3': 'age',
        '4': 'salary',
    }
    
    # Get base queryset
    queryset = Employee.objects.all()
    
    # Get total count
    records_total = queryset.count()
    
    # Apply search filter
    if search_value:
        queryset = queryset.filter(
            Q(name__icontains=search_value) |
            Q(department__icontains=search_value) |
            Q(age__icontains=search_value) |
            Q(salary__icontains=search_value)
        )
    
    # Get filtered count
    records_filtered = queryset.count()
    
    # Apply sorting
    if order_column and order_column in column_map:
        sort_column = column_map[order_column]
        if order_dir == 'desc':
            sort_column = f'-{sort_column}'
        queryset = queryset.order_by(sort_column)
    
    # Apply pagination
    offset = (page - 1) * per_page
    queryset = queryset[offset:offset + per_page]
    
    # Prepare the data
    data = list(queryset.values('id', 'name', 'department', 'age', 'salary'))
    
    # Return JSON response
    return JsonResponse({
        'draw': draw,
        'recordsTotal': records_total,
        'recordsFiltered': records_filtered,
        'data': data
    })

# main.py
from fastapi import FastAPI, Query, Depends
from typing import Optional, List, Dict, Any
from sqlalchemy.orm import Session
from sqlalchemy import or_, desc
from . import models, schemas, database

app = FastAPI()

# Column mapping
column_map = {
    0: "id",
    1: "name",
    2: "department",
    3: "age",
    4: "salary"
}

def get_db():
    db = database.SessionLocal()
    try:
        yield db
    finally:
        db.close()

@app.get("/api/employees", response_model=schemas.SmartTablesResponse)
async def get_employees(
    draw: int = Query(1),
    page: int = Query(1),
    perPage: int = Query(10),
    search: Optional<str> = Query(None, alias="search[value]"),
    order_column: Optional<int> = Query(None, alias="order[0][column]"),
    order_dir: Optional<str> = Query("asc", alias="order[0][dir]"),
    db: Session = Depends(get_db)
):
    # Base query
    query = db.query(models.Employee)
    
    # Get total count
    records_total = query.count()
    
    # Apply search filter
    if search:
        query = query.filter(
            or_(
                models.Employee.name.ilike(f"%{search}%"),
                models.Employee.department.ilike(f"%{search}%"),
                models.Employee.age.cast(String).ilike(f"%{search}%"),
                models.Employee.salary.cast(String).ilike(f"%{search}%")
            )
        )
    
    # Get filtered count
    records_filtered = query.count()
    
    # Apply sorting
    if order_column is not None and order_column in column_map:
        column_name = column_map[order_column]
        column = getattr(models.Employee, column_name)
        
        if order_dir == "desc":
            query = query.order_by(desc(column))
        else:
            query = query.order_by(column)
    
    # Apply pagination
    offset = (page - 1) * perPage
    query = query.offset(offset).limit(perPage)
    
    # Execute query
    data = query.all()
    
    # Return formatted response
    return {
        "draw": draw,
        "recordsTotal": records_total,
        "recordsFiltered": records_filtered,
        "data": data
    }

// SmartTablesRequest.cs
public class SmartTablesRequest
{
    public int Draw { get; set; }
    public int Page { get; set; } = 1;
    public int PerPage { get; set; } = 10;
    public Search Search { get; set; } = new Search();
    public List<Order> Order { get; set; } = new List<Order>();
}

public class Search
{
    public string Value { get; set; } = "";
    public bool Regex { get; set; } = false;
}

public class Order
{
    public int Column { get; set; }
    public string Dir { get; set; } = "asc";
}

// SmartTablesResponse.cs
public class SmartTablesResponse<T>
{
    public int Draw { get; set; }
    public int RecordsTotal { get; set; }
    public int RecordsFiltered { get; set; }
    public List<T> Data { get; set; } = new List<T>();
}

// EmployeesController.cs
[ApiController]
[Route("api/[controller]")]
public class EmployeesController : ControllerBase
{
    private readonly ApplicationDbContext _context;
    private readonly ILogger<EmployeesController> _logger;
    
    // Column mapping
    private readonly Dictionary<int, string> _columnMap = new Dictionary<int, string>
    {
        { 0, "Id" },
        { 1, "Name" },
        { 2, "Department" },
        { 3, "Age" },
        { 4, "Salary" }
    };
    
    public EmployeesController(ApplicationDbContext context, ILogger<EmployeesController> logger)
    {
        _context = context;
        _logger = logger;
    }
    
    [HttpGet]
    public async Task<ActionResult<SmartTablesResponse<Employee>>> GetEmployees([FromQuery] SmartTablesRequest request)
    {
        try
        {
            // Start with base query
            IQueryable<Employee> query = _context.Employees;
            
            // Get total count
            int recordsTotal = await query.CountAsync();
            
            // Apply search filter
            if (!string.IsNullOrEmpty(request.Search.Value))
            {
                string searchValue = request.Search.Value.ToLower();
                query = query.Where(e => 
                    e.Name.ToLower().Contains(searchValue) ||
                    e.Department.ToLower().Contains(searchValue) ||
                    e.Age.ToString().Contains(searchValue) ||
                    e.Salary.ToString().Contains(searchValue)
                );
            }
            
            // Get filtered count
            int recordsFiltered = await query.CountAsync();
            
            // Apply sorting
            if (request.Order.Any())
            {
                var order = request.Order.First();
                if (_columnMap.TryGetValue(order.Column, out string column))
                {
                    if (order.Dir.ToLower() == "asc")
                    {
                        query = query.OrderBy(e => EF.Property<object>(e, column));
                    }
                    else
                    {
                        query = query.OrderByDescending(e => EF.Property<object>(e, column));
                    }
                }
            }
            
            // Apply pagination
            int skip = (request.Page - 1) * request.PerPage;
            query = query.Skip(skip).Take(request.PerPage);
            
            // Execute query
            var data = await query.ToListAsync();
            
            // Return formatted response
            return new SmartTablesResponse<Employee>
            {
                Draw = request.Draw,
                RecordsTotal = recordsTotal,
                RecordsFiltered = recordsFiltered,
                Data = data
            };
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error processing SmartTables request");
            return StatusCode(500, "Internal server error");
        }
    }
}

// AWS Lambda handler (Node.js)
const AWS = require('aws-sdk');
const dynamoDB = new AWS.DynamoDB.DocumentClient();

// Column mapping
const columnMap = {
  0: 'id',
  1: 'name',
  2: 'department',
  3: 'age',
  4: 'salary'
};

exports.handler = async (event) => {
  try {
    // Parse query parameters
    const queryParams = event.queryStringParameters || {};
    
    const draw = parseInt(queryParams.draw) || 1;
    const page = parseInt(queryParams.page) || 1;
    const perPage = parseInt(queryParams.perPage) || 10;
    const search = queryParams['search[value]'] || '';
    
    let orderColumn = null;
    let orderDir = 'asc';
    
    if (queryParams['order[0][column]']) {
      orderColumn = parseInt(queryParams['order[0][column]']);
      orderDir = queryParams['order[0][dir]'] || 'asc';
    }
    
    // Fetch all data from DynamoDB
    // Note: In a production environment, you might want to use query/scan with filters
    const scanParams = {
      TableName: 'employees'
    };
    
    const scanResult = await dynamoDB.scan(scanParams).promise();
    let data = scanResult.Items || [];
    
    // Get total count
    const recordsTotal = data.length;
    
    // Apply search filter
    if (search) {
      const searchLower = search.toLowerCase();
      data = data.filter(item => {
        return Object.values(item).some(val => 
          String(val).toLowerCase().includes(searchLower)
        );
      });
    }
    
    // Get filtered count
    const recordsFiltered = data.length;
    
    // Apply sorting
    if (orderColumn !== null && columnMap[orderColumn]) {
      const sortKey = columnMap[orderColumn];
      
      data.sort((a, b) => {
        const aVal = a[sortKey];
        const bVal = b[sortKey];
        
        // Handle numeric sorting
        if (typeof aVal === 'number' && typeof bVal === 'number') {
          return orderDir === 'asc' ? aVal - bVal : bVal - aVal;
        }
        
        // Handle string sorting
        const aStr = String(aVal).toLowerCase();
        const bStr = String(bVal).toLowerCase();
        return orderDir === 'asc' ? aStr.localeCompare(bStr) : bStr.localeCompare(aStr);
      });
    }
    
    // Apply pagination
    const offset = (page - 1) * perPage;
    const paginatedData = data.slice(offset, offset + perPage);
    
    // Return response
    return {
      statusCode: 200,
      headers: {
        'Content-Type': 'application/json',
        'Access-Control-Allow-Origin': '*'
      },
      body: JSON.stringify({
        draw,
        recordsTotal,
        recordsFiltered,
        data: paginatedData
      })
    };
  } catch (error) {
    console.error('Error processing request:', error);
    
    return {
      statusCode: 500,
      headers: {
        'Content-Type': 'application/json',
        'Access-Control-Allow-Origin': '*'
      },
      body: JSON.stringify({ error: 'Internal server error' })
    };
  }
};

// Cloudflare Worker
addEventListener('fetch', event => {
  event.respondWith(handleRequest(event.request))
})

async function handleRequest(request) {
  // Parse URL and query parameters
  const url = new URL(request.url)
  
  // Only handle GET requests to /api/employees
  if (request.method !== 'GET' || !url.pathname.endsWith('/api/employees')) {
    return new Response('Not found', { status: 404 })
  }
  
  // Parse parameters
  const draw = parseInt(url.searchParams.get('draw')) || 1
  const page = parseInt(url.searchParams.get('page')) || 1
  const perPage = parseInt(url.searchParams.get('perPage')) || 10
  const search = url.searchParams.get('search[value]') || ''
  const orderColumn = parseInt(url.searchParams.get('order[0][column]'))
  const orderDir = url.searchParams.get('order[0][dir]') || 'asc'
  
  // Column mapping
  const columnMap = {
    0: 'id',
    1: 'name',
    2: 'department',
    3: 'age',
    4: 'salary'
  }
  
  try {
    // In a real implementation, fetch data from KV, D1 or external API
    // For demonstration, we use mock data
    let data = mockEmployees
    
    // Get total count
    const recordsTotal = data.length
    
    // Apply search filter
    if (search) {
      const searchLower = search.toLowerCase()
      data = data.filter(item => {
        return Object.values(item).some(val => 
          String(val).toLowerCase().includes(searchLower)
        )
      })
    }
    
    // Get filtered count
    const recordsFiltered = data.length
    
    // Apply sorting
    if (!isNaN(orderColumn) && columnMap[orderColumn]) {
      const sortKey = columnMap[orderColumn]
      
      data.sort((a, b) => {
        const aVal = a[sortKey]
        const bVal = b[sortKey]
        
        // Handle numeric sorting
        if (typeof aVal === 'number' && typeof bVal === 'number') {
          return orderDir === 'asc' ? aVal - bVal : bVal - aVal
        }
        
        // Handle string sorting
        const aStr = String(aVal).toLowerCase()
        const bStr = String(bVal).toLowerCase()
        return orderDir === 'asc' ? aStr.localeCompare(bStr) : bStr.localeCompare(aStr)
      })
    }
    
    // Apply pagination
    const offset = (page - 1) * perPage
    const paginatedData = data.slice(offset, offset + perPage)
    
    // Return response
    const response = {
      draw,
      recordsTotal,
      recordsFiltered,
      data: paginatedData
    }
    
    return new Response(JSON.stringify(response), {
      headers: {
        'Content-Type': 'application/json',
        'Access-Control-Allow-Origin': '*'
      }
    })
  } catch (error) {
    return new Response(JSON.stringify({ error: 'Internal server error' }), {
      status: 500,
      headers: {
        'Content-Type': 'application/json',
        'Access-Control-Allow-Origin': '*'
      }
    })
  }
}

// Mock data (in a real implementation, this would come from a database or KV)
const mockEmployees = [
  { id: 1, name: 'John Doe', department: 'Engineering', age: 32, salary: 85000 },
  { id: 2, name: 'Jane Smith', department: 'Marketing', age: 28, salary: 65000 },
  // Additional mock data...
]

// Server-side fix (PHP example)
// Apply search filter once to get consistent count
$filteredData = [];
$totalRecords = count($data);

if (!empty($search)) {
    $searchLower = strtolower($search);
    foreach ($data as $item) {
        $found = false;
        foreach ($item as $key => $value) {
            $strValue = strtolower((string)$value);
            if (strpos($strValue, $searchLower) !== false) {
                $found = true;
                break;
            }
        }
        if ($found) {
            $filteredData[] = $item;
        }
    }
} else {
    $filteredData = $data;
}

// Get TOTAL filtered count BEFORE pagination
$totalFiltered = count($filteredData);

// Now apply pagination
$offset = ($page - 1) * $perPage;
$paginatedData = array_slice($filteredData, $offset, $perPage);

// Return consistent counts
return [
    'recordsTotal' => $totalRecords,
    'recordsFiltered' => $totalFiltered,
    'data' => $paginatedData
];

// Client-side cache key generation
generateCacheKey() {
    // Include ALL parameters that affect the results in the key
    const params = {
        url: this.options.data.url,
        page: this.currentPage,
        perPage: this.options.perPage,
        search: this.searchQuery || '',
        sort: this.currentSort
    };
    
    this.log('Generating cache key with params:', JSON.stringify(params));
    return JSON.stringify(params);
}

// Server-side sorting solution (PHP)
// Define column map for sorting
$columnMap = [
    0 => 'id',
    1 => 'name',
    2 => 'position',
    3 => 'office',
    4 => 'age',
    5 => 'startDate',
    6 => 'salary'
];

// Identify data types for special handling
$numericFields = ['id', 'age', 'salary', 'bonus', 'progress'];
$dateFields = ['startDate', 'hireDate'];

// Apply sorting with data type awareness
if ($order) {
    $columnIndex = $order['column'];
    $columnKey = isset($columnMap[$columnIndex]) ? $columnMap[$columnIndex] : null;
    $direction = $order['dir'];
    
    if ($columnKey) {
        usort($filteredData, function($a, $b) use ($columnKey, $direction, $numericFields, $dateFields) {
            // Handle numeric fields
            if (in_array($columnKey, $numericFields)) {
                $aVal = (float)$a[$columnKey];
                $bVal = (float)$b[$columnKey];
                return $direction === 'asc' ? $aVal - $bVal : $bVal - $aVal;
            }
            // Handle date fields
            else if (in_array($columnKey, $dateFields)) {
                $aDate = strtotime($a[$columnKey]);
                $bDate = strtotime($b[$columnKey]);
                return $direction === 'asc' ? $aDate - $bDate : $bDate - $aDate;
            }
            // Default string comparison
            else {
                $aVal = strtolower($a[$columnKey]);
                $bVal = strtolower($b[$columnKey]);
                $result = strcmp($aVal, $bVal);
                return $direction === 'asc' ? $result : -$result;
            }
        });
    }
}

// Server-side page validation (PHP)
// Calculate maximum page based on filtered count
$maxPage = ceil($totalFiltered / $perPage);

// Ensure page is valid
if ($page < 1) {
    $page = 1;
} else if ($page > $maxPage && $maxPage > 0) {
    $page = $maxPage;
}

// Calculate offset based on validated page
$offset = ($page - 1) * $perPage;

// Client-side search reset
handleSearch(value) {
    this.searchQuery = value;

    if (this.options.data.serverSide) {
        // Reset the cache when search changes
        this.clearAjaxCache();
        
        this.currentPage = 1; // Reset to first page on new search
        this.loadAjax(); // Fetch new data with search query
    } else {
        // Client-side search logic
    }
}