Json - ServerSide - postgresql db

goktuginal41@gmail.com · July 2023

Hello everyone,

I have a table with 5,000 rows of data and 95 columns. However, when I refresh the page, it takes 3.30 minutes for the table to appear. It's not normal for it to take that long. I edit json with serializer. Below you can see a few lines of the code and a json file example. I think the way to reduce this time is to use server-side with php.

https://datatables.net/examples/server_side/simple

I already checked above page. However, I don't know how to convert the code I have into server-side operation. Can anyone help with an idea?

var table = $('#example').DataTable({
          // json to fill the table rows and columns.
          "ajax": {
            url: "/json",
            type: "GET"
          },
          paging: true,
          pageLength: 10,
          deferRender: true,

{"data":[
      {
         "id":1,
         "p_id_s":"G000001",
         "reception_date":"2014-03-27",
         "hospital_date":"2014-01-15",
         "culture":"MGIT",
         "index_sample":"G000001",
         "is_index_sample":"Yes",
         "status":"Transferred",
         "hospital_sample_number":"2013982",
         "sample_type":"Sputum",
         "inactivation_date":null,
         "transfer_date":null,
         "comments":null,
         "projects":{
            "name":"Paris",
            "prefix":"P",
            "description":""
         }
    ]
}

allan · July 2023

I would suggest you look into using our Editor PHP libraries. They are MIT licensed and can be used without needing to use Editor (or having an Editor license). There is a blog post here with further details about how you can use them.

Allan

goktuginal41@gmail.com · July 2023

Hi Allan,

Thank you for the response. I implemented a simple code on Django to try the PHP Editor. However, I am getting the following error. Where am I going wrong, can you help?

**DataTables warning: table id=example - Ajax error. For more information about this error, please see http://datatables.net/tn/7**

model.py

class Users(models.Model):

    namee = models.CharField(max_length=128, unique=True)
    surname = models.CharField(max_length=256)
    occupation = models.CharField(max_length=256)
    city = models.CharField(max_length=256)
    country = models.CharField(max_length=256)
    description = models.CharField(max_length=256, null=True, blank=True)

    def __str__(self):
        return self.namee

base.html

<body>
    <table id="example" class="display compact" style="width:100%">
      <thead>
        <tr>
          <th>Name</th>
          <th>Surname</th>
          <th>Occupation</th>
          <th>City</th>
          <th>Country</th>
          <th>Description</th>
        </tr>
      </thead>
      <tbody>
        
      </tbody>
    </table>
  </body>
  <script>
  $(document).ready(function() {
      $('#example').DataTable({
        ajax: {
          url: "../php/exampleController.php",
          type: "POST"
        },
        columns: [
          { data: "namee" },
          { data: "surname" },
          { data: "occupation" },
          { data: "city" },
          { data: "country" },
          { data: "description" }
        ],
        processing: true,
        serverSide: true
      });
  });
  </script>

exampleController.php

<?php
include("../lib/DataTables.php");

use
    DataTables\Editor,
    DataTables\Editor\Field;

Editor::inst( $db, 'my_users' )
    ->field(
        Field::inst( 'namee' ),
        Field::inst( 'surname' ),
        Field::inst( 'occupation' ),
        Field::inst( 'city' ),
        Field::inst( 'country' ),
        Field::inst( 'description' )
    )

    ->write( false )
    ->process( $_POST )
    ->json();

config.php

$sql_details = array(
    "type" => "Postgres",  // Database type: "Mysql", "Postgres", "Sqlserver", "Sqlite" or "Oracle"
    "user" => "postgres",  // Database user name
    "pass" => "test",    // Database password
    "host" => "127.0.0.1", // Database host
    "port" => "",          // Database connection port (can be left empty for default)
    "db"   => "testdata",   // Database name
    "dsn"  => "",          // PHP DSN extra information. Set as `charset=utf8mb4` if you are using MySQL
    "pdoAttr" => array()   // PHP PDO attributes array. See the PHP documentation for all options
);

goktuginal41@gmail.com · July 2023

Hi Allan,

Thank you for the response. I wrote a simple code on Django to try the PHP Editor. Although there are entries(rows) in the db, I can't see any data on the screen. Where am I doing wrong?

models.py

class Users(models.Model):

    namee = models.CharField(max_length=128, unique=True)
    surname = models.CharField(max_length=256)
    occupation = models.CharField(max_length=256)
    city = models.CharField(max_length=256)
    country = models.CharField(max_length=256)
    description = models.CharField(max_length=256, null=True, blank=True)

    def __str__(self):
        return self.namee

base.html

  <body>
    <table id="example" class="display compact" style="width:100%">
      <thead>
        <tr>
          <th>Name</th>
          <th>Surname</th>
          <th>Occupation</th>
          <th>City</th>
          <th>Country</th>
          <th>Description</th>
        </tr>
      </thead>
      <tbody>
        
      </tbody>
    </table>
  </body>
  <script>
  $(document).ready(function() {
      $('#example').DataTable({
        ajax: {
          url: "../php/exampleController.php",
          type: "POST"
        },
        columns: [
          { data: "id" },
          { data: "namee" },
          { data: "surname" },
          { data: "occupation" },
          { data: "city" },
          { data: "country" },
          { data: "description" }
        ],
        processing: true,
        serverSide: true
      });
  });
  </script>

config.php

$sql_details = array(
    "type" => "Postgres",  // Database type: "Mysql", "Postgres", "Sqlserver", "Sqlite" or "Oracle"
    "user" => "postgres",  // Database user name
    "pass" => "pass",    // Database password
    "host" => "127.0.0.1", // Database host
    "port" => "",          // Database connection port (can be left empty for default)
    "db"   => "testdata",   // Database name
    "dsn"  => "",          // PHP DSN extra information. Set as `charset=utf8mb4` if you are using MySQL
    "pdoAttr" => array()   // PHP PDO attributes array. See the PHP documentation for all options
);

exampleController.php

<?php
include("../lib/DataTables.php");

use
    DataTables\Editor,
    DataTables\Editor\Field;

Editor::inst( $db, 'my_users' )
    ->field(
        Field::inst( 'id' ),
        Field::inst( 'namee' ),
        Field::inst( 'surname' ),
        Field::inst( 'occupation' ),
        Field::inst( 'city' ),
        Field::inst( 'country' ),
        Field::inst( 'description' )
    )

    ->write( false )
    ->process( $_POST )
    ->json();

goktuginal41@gmail.com · July 2023

I just realized that it gives Not Found: /php/exampleController.php error.

Obviously, I'm typing the extension wrong,
url: "../php/exampleController.php",
but I couldn't find how to get the correct one.

kthorngren · July 2023

I've never tried running a PHP script with Django. Maybe this SO thread will help. This forum doesn't have the Django expertise to help get this going. However if you have Datatables questions let us know.

Kevin

allan · July 2023

I hadn't realised you were using Django. The server-side processing protocol is fully documented - it would need a Python implemention to work on a Django server.

Allan

Json - ServerSide - postgresql db

Json - ServerSide - postgresql db

Answers

Howdy, Stranger!

Categories

DataTables

Json - ServerSide - postgresql db

Json - ServerSide - postgresql db

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables