CodeIgniter Forums
Datatables and ORM Model - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: Model-View-Controller (https://forum.codeigniter.com/forumdisplay.php?fid=10)
+--- Thread: Datatables and ORM Model (/showthread.php?tid=81674)

Datatables and ORM Model - kabeza - 04-07-2022

Hi
I'm building a new ORM Model, which uses sqlsrv driver and works

PHP Code:
<?php
namespace App\Models;
use CodeIgniter\Model;
class MyModel extends Model
{
    protected $DBGroup = 'sqlserver';
    protected $table = 'project';
    protected $primaryKey = 'idproject';
    protected $useAutoIncrement = true;
    protected $returnType     = 'array';
    protected $useSoftDeletes = true;
    protected $allowedFields = ['name', 'logo'];
    protected $useTimestamps = false;
} 

I load that model from a Library and return data to Controller

Code:
$data = json_encode($this->mymodel->findAll());

Now, I must use Datatables in the view, with serverSide=true and Ajax property pointing to Controller's url

The json format for Datatables is custom and must have additional fields (draw, recordsTotal, data, etc.)
https://datatables.net/manual/server-side

Code:
{
    "draw": 1,
    "recordsTotal": 57,
    "recordsFiltered": 57,
    "data": [
        [
            "Test1",
            "bla",
        ],
        [
            "Test2",
            "bla bla",
        ],
        ...
    ]
}

PS: I've already seen https://github.com/hermawanramadhan/CodeIgniter4-DataTables but it won't fit my needs in future

Does anyone have an example about how to build that custom json using the methods from ORM ?

Thanks a lot for any tip you can give me

RE: Datatables and ORM Model - JustJohnQ - 04-07-2022

I am using sEcho from datatables post, iTotalRecords using row count before applying filters and limit in query and iTotalDisplayRecords as row count after applying filters and limit. aaData is the actual query result.

Code:
$iFilteredTotal = $result['filteredRecords'];
$iTotal        = $result['totalRecords'];

// Output
$output = array ("sEcho" => intval ($_POST['sEcho']), "whichorders" => $member, "iTotalRecords" => $iTotal, "iTotalDisplayRecords" => $iFilteredTotal, "aaData" => $result['data']);

$out = json_encode ($output);

EDIT: It looks like the parameter names have been changed in the new datatables version but you get the idea.


RE: Datatables and ORM Model - kabeza - 04-08-2022

(04-07-2022, 11:38 PM)JustJohnQ Wrote: // Output
$output = array ("sEcho" => intval ($_POST['sEcho']), "whichorders" => $member, "iTotalRecords" => $iTotal, "iTotalDisplayRecords" => $iFilteredTotal, "aaData" => $result['data']);
$out = json_encode ($output);
EDIT: It looks like the parameter names have been changed in the new datatables version but you get the idea.[/code]

Yes, the parameters and structure changed in latest versions

This is so far the code I could build to work with sqlsrv SQL Server, ysing its syntax, etc.
This is a controller which handles post request from Datatables

Code:
$request = \Config\Services::request();
        $post = $request->getPost();
        $db = \Config\Database::connect('sqlserver');

        $draw = $post['draw'];
        $row = $post['start'];
        $rowperpage = $post['length']; // Rows display per page
        $columnIndex = $post['order'][0]['column']; // Column index
        $columnName = $post['columns'][$columnIndex]['data']; // Column name
        $columnSortOrder = $post['order'][0]['dir']; // asc or desc
        $searchValue = $post['search']['value']; // Search value

        $searchQuery = " ";
        if ($searchValue != '') {
            $searchQuery = " AND (name like '%" . $searchValue . "%') ";
        }

        $totalRecords = $db->table('project')->countAllResults();

        $sqlcf = "
            SELECT idproject, name
            FROM schema.project
            WHERE (1=1) " . $searchQuery;

        $querysf = $db->query($sqlcf);
        $totalRecordwithFilter = count($querysf->getResultArray());

        if ($totalRecordwithFilter > 0) {
            $sqlfinal = "
                SELECT *
                FROM schema.project
                WHERE (1=1) " . $searchQuery . "
                ORDER BY " . $columnName . " " . $columnSortOrder;

            if ($rowperpage != -1) {
                $sqlfinal .= "
            OFFSET " . $row . " ROWS FETCH NEXT " . $rowperpage . " ROWS ONLY";
            }

            $queryfinal = $db->query($sqlfinal);
            $data = $queryfinal->getResultArray();

            foreach ($data as $keyd => $valdat) {
                $datos_finales[] = array(
                    "name" => $valdat['nombre'],
                    "logo" => $valdat['logo'],
                    "idproject" => $valdat['idproyecto'],
                );
            }

            ## Response
            $response = array(
                "draw" => intval($draw),
                "recordsTotal" => $totalRecords,
                "recordsFiltered" => $totalRecordwithFilter,
                "data" => $datos_finales
            );
        } else {
            $response = array(
                "draw" => intval($draw),
                "recordsTotal" => $totalRecords,
                "recordsFiltered" => $totalRecordwithFilter,
                "data" => []
            );
        }
        echo json_encode($response);

It is working so far