Datatables

Datatables server-side processing et postgresql

Bonjour à tous,

vous connaissez sûrement l’excellent plugin jquery datatables qui permet de trier/rechercher dans un tableau.
Ce plugin a de très bonne fonctionnalités, mais devient ingérable quand on atteint une trop grosse quantité de données (> 5000).

Il existe une fonctionnalité qui permet de remplir ce tableau dynamiquement via un appel ajax (fonction appelée server-side processing), et le site officiel fournit un code adapté pour MySQL. On trouvé également un exemple pour PostGreSQL, mais il est pour la version 1.9 de datatable, et ne correspond donc plus à la dernière.

J’ai modifié le code existant pour l’adapter aux requêtes de la 1.10, et pour utiliser pdo et non plus pg_connect et pg_query.

Tout d’abord, j’utilise cette classe php pour pdo :

<?php

/**
 * includes/class.bddpdo.php
 *
 * Comprend des fonctions pour nous aider à gérer la base de données
 *
 * @author Stephane DEWITTE <[email protected]>
 * @version 1.0
 * @filesource
 * @package default
 */

/**
 * Classe bdd
 * @param statement : le statement à éxécuter
 * @return Statement
 * @package bdd
 */
class bddpdo
{
    /**
     * Hote
     * @var text 
     */
    var $host        = DATABASE_HOST;
    /**
     * User
     * @var text 
     */
    var $user        = DATABASE_USER;
    /**
     * Password
     * @var text 
     */
    var $password    = DATABASE_PASSWORD;
    /**
     * Database
     * @var text 
     */
    var $database    = DATABASE_NAME;
    /**
     * Persistance
     * @var text 
     */
    var $persistency = false;
    /**
     * Port
     * @var int 
     */
    var $port        = NULL;
    /**
     * Dsn
     * @var text 
     */
    var $dsn         = '';
    /**
     * Obejt PDO
     * @var mixed 
     */
    var $pdo         = NULL;
    /**
     * Error mode
     * @var text 
     */
    var $errorMode   = 'WARNING';

    /**
     * Lance la connection et genère l'objet $pdo
     * @global type $debugbar
     */
    function __construct()
    {
        global $debugbar;
        try
        {

            $this->pdo = new PDO('pgsql:host=' . $this->host . ';dbname=' . $this->database,
                                 $this->user, $this->password,
                                 array(PDO::ATTR_PERSISTENT => true));
        }
        catch (PDOException $e)
        {
            echo 'Échec lors de la connexion : ' . $e->getMessage();
        }
    }

    /**
     * Donne le type de database (pgsql, mysql, etc...)
     * @return text
     */
    function returnType()
    {

        return $this->pdo->getAttribute(PDO::ATTR_DRIVER_NAME);
    }

    /**
     * Prepare une requête
     * @desc   : Prépare la requête
     * @param : String $sql
     * @return : Query statement
     */
    function prepare($sql)
    {
        $test = $this->pdo->prepare($sql);
        if(!$test)
        {
            $this->erreur('');
        }
        return $test;
    }

    /**
     * Execute une requête
     * @param array $varArray
     * @param statement $stmt
     * @return boolean
     * @throws Exception
     */
    function execute($varArray, $stmt)
    {
        // Le paramètre doit être un Array
        if(is_array($varArray))
        {
            // Si le tableau des parametre est vide on execute la requête preparé sans paramètre
            if(empty($varArray))
            {
                $stmt->execute();
                // Sinon on execute la requête préparée avec les paramètres
            }
            else
            {
                $ret = $stmt->execute($varArray);
                if($ret === false)
                {
                    // gestion d'erreurs
                    if(DEBUG)
                    {
                        echo "<br />Erreur sur requête";
                        print_r($varArray);
                        echo "<br />";
                    }
                    $this->erreur($stmt);
                }
            }
            return $stmt;
        }
        else
        {
            // Mode exception
            if($this->exception)
            {
                throw new Exception('<pre><b>FATAL ERROR : ' . __METHOD__ . '</b> except first given parameter to be an array</pre>',
                                    0);
            }
            else
            {
                return false;
            }
        }
    }

    /**
     * lance une query
     * @desc   : Execute une requête SQL
     * @param : String $sql
     * @return : Query result
     */
    public function query($sql)
    {
        if(strpos(strtoupper($sql), "SELECT") == 0)
        {
            return $this->pdo->Query($sql);
        }
        else
        {
            return $this->pdo->exec($sql);
        }
    }

    /**
     * Autocommit
     * @desc   : Gestion des transactions
     * @param : Bool $autocomit
     * @return : Bool 
     */
    public function AutoCommit($autocommit = true)
    {
        return $this->pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, $autocommit);
    }

    /**
     * Begin transaction
     * @desc   : Démarre la transaction
     * @param : None
     * @return : Bool
     */
    public function Begin()
    {
        return $this->pdo->beginTransaction();
    }

    /**
     * Commit transaction
     * @desc   : Valide la requête
     * @param : None
     * @return : Bool
     */
    public function Commit()
    {
        return $this->pdo->commit();
    }

    /**
     * Rollback transaction
     * @desc   : Annule la requête
     * @param : None
     * @return : Bool
     */
    public function RollBack()
    {
        return $this->pdo->rollBack();
    }

    /**
     * FetchAll => retourne tous records de la requête
     * @desc   : Fecth Mode Array
     * @param : Resource query result
     * @return : query result rows
     */
    public function fetchAll($results)
    {
        return $results->fetchAll();
    }

    /**
     * fetchObject
     * @desc   : Fecth Mode Object
     * @param : Resource query result
     * @return : query result rows
     */
    public function fetchObject($results)
    {
        return $results->fetch(PDO::FETCH_OBJ);
    }

    /**
     * fetchAssoc
     * @desc   : Fecth Mode Assoc
     * @param : Resource query result
     * @return : query result rows
     */
    public function fetchAssoc($results)
    {
        return $results->fetch(PDO::FETCH_ASSOC);
    }

    /**
     * fetchRow
     * @desc   : Fecth Mode Num ROw
     * @param : Resource query result
     * @return : query result rows
     */
    public function fetchRow($results)
    {
        return $results->fetch(PDO::FETCH_NUM);
    }

    /**
     * fetchArray
     * @desc   : Fecth Mode Array
     * @param : Resource query result
     * @return : query result rows
     */
    public function fetchArray($results)
    {
        return $results->fetch(PDO::FETCH_BOTH);
    }

    /**
     * rowCount
     * @desc   : Retourne le nombre de resultat d'une requête
     * @param : None
     * @return : Int numrows
     */
    public function rowCount()
    {
        return $this->pdo->rowCount();
    }

    /**
     * LastInsertId (mysql uniquement ?)
     * @desc   : Retourne l'id du dernier enregistrement
     * @param : Void
     * @return : Int id
     */
    public function lastInsertId($options = array())
    {
        return $this->pdo->lastInsertId();
    }

    /**
     * close cursor
     * @desc   : Librer les resultats de la requête 
     * @param : results
     * @return : Bool
     */
    public function closeCursor($results = NULL)
    {
        return $this->pdo->closeCursor();
    }

    /**
     * quote 
     * @param text $value
     * @param mixed $type
     * @return text
     */
    public function quote($value, $type = DATABASE::PARAM_STR)
    {
        switch ($type)
        {
            case self::PARAM_INT :
                $value = $this->pdo->quote($value, PDO::PARAM_INT);
                break;
            case self::PARAM_FLOAT :
                $value = $this->pdo->quote($value, PDO::PARAM_FLOAT);
                break;
            case self::PARAM_BOOL :
                $value = $this->pdo->quote($value, PDO::PARAM_BOOL);
                break;
            default:
                $value = $this->pdo->quote($value, PDO::PARAM_STR);
                break;
        }
        return $value;
    }

    /**
     * Erreur
     * @param mixed $stmt
     * @return mixed
     */
    public function erreur($stmt = '')
    {
        if(empty($stmt))
        {
            return $this->pdo->errorInfo();
        }
        else
        {
            if(DEBUG)
            {
                echo "<br >" . print_r($stmt->errorInfo(), true);
                print_r($stmt, true);
            }
            return $stmt->errorInfo();
        }
    }

}

Ensuite, voici la page php à appeler à proprement parler :

<?php
/**
 * ajax/datatable_pgsql.php
 * 
 * Script:    DataTables server-side script for PHP and PostgreSQL
 * 
 * For use with databtable 1.10+ with PostGresqlDatabase
 * Must be used with pdo
 * 
 * @author Stéphane DEWITTE <[email protected]> based on Allan Jardine's work
 */

require 'class.bddpdo.php';
$pdo = new bddpdo;

error_reporting(0);
/** * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * Easy set variables
 */

/* Array of database columns which should be read and sent back to DataTables. Use a space where
 * you want to insert a non-database field (for example a counter or static image)
 */
$aColumns = array('data_rub_cod', 'data_tableau_cod', 'data_page', 'data_annee', 'data_valeur', 'data_indicateur',
    'data_lib_vertical', 'data_lib_horizontal', 'data_num_ligne', 'data_num_colonne');

/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "data_cod";

/* DB table to use */
$sTable = "data";

/*
 * Paging
 */
$sLimit = "";
if(isset($_GET['start']) && $_GET['length'] != '-1')
{
    $sLimit = "LIMIT " . intval($_GET['length']) . " OFFSET " .
            intval($_GET['start']);
}


/*
 * Ordering
 */
$sOrder = '';
if(isset($_GET['order']))
{

    $idx = $_GET['order'][0]['column'];
    $order = ' desc ';
    if($_GET['order'][0]['dir'] == 'asc')
    {
        $order = ' asc ';
    }
    $sOrder = "ORDER BY  " . $aColumns[$idx] . $order;
}



/*
 * Filtering
 * NOTE This assumes that the field that is being searched on is a string typed field (ie. one
 * on which ILIKE can be used). Boolean fields etc will need a modification here.
 */
$sWhere = "";

if($_REQUEST['search']['value'] != "")
{
    $sWhere = "WHERE (";
    for($i = 0; $i < count($aColumns); $i++)
    {
        if($_GET['columns'][$i]['searchable'] == "true")
        {
            $sWhere .= 'cast(' . $aColumns[$i] . " as text) ILIKE '%" . pg_escape_string($_REQUEST['search']['value']) . "%' OR ";
        }
    }
    $sWhere = substr_replace($sWhere, "", -3);
    $sWhere .= ")";
}

$sQuery = "
        SELECT " . str_replace(" , ", " ", implode(", ", $aColumns)) . "
        FROM   $sTable
        $sWhere
        $sOrder
        $sLimit
    ";

$rResult = $pdo->prepare($sQuery);
$rResult = $pdo->execute(array(), $rResult);

$sQuery = "
        SELECT $sIndexColumn
        FROM   $sTable
    ";
$stmt   = $pdo->prepare($sQuery);
$stmt   = $pdo->execute(array(), $stmt);
$iTotal = $stmt->rowCount();


if($sWhere != "")
{
    $sQuery         = "
            SELECT $sIndexColumn
            FROM   $sTable
            $sWhere
        ";
    $stmt           = $pdo->prepare($sQuery);
    $stmt           = $pdo->execute(array(), $stmt);
    $iFilteredTotal = $stmt->rowCount();
}
else
{
    $iFilteredTotal = $iTotal;
}


/*
 * Output
 */
$output = array(
    "draw"                 => intval($_GET['draw']),
    "iTotalRecords"        => $iTotal,
    "iTotalDisplayRecords" => $iFilteredTotal,
    "aaData"               => array(),
);

while($aRow = $rResult->fetch())
{
    $row = array();
    for($i = 0; $i < count($aColumns); $i++)
    {
        if($aColumns[$i] == "version")
        {

            $row[] = ($aRow[$aColumns[$i]] == "0") ? '-' : $aRow[$aColumns[$i]];
        }
        else if($aColumns[$i] != ' ')
        {
            $row[] = $aRow[$aColumns[$i]];
        }
    }
    $output['aaData'][] = $row;
 


echo json_encode($output);

Utilisation :

Modifiez les lignes :

/* Array of database columns which should be read and sent back to DataTables. Use a space where
 * you want to insert a non-database field (for example a counter or static image)
 */
$aColumns = array('data_rub_cod', 'data_tableau_cod', 'data_page', 'data_annee', 'data_valeur', 'data_indicateur',
    'data_lib_vertical', 'data_lib_horizontal', 'data_num_ligne', 'data_num_colonne');
 
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "data_cod";
 
/* DB table to use */
$sTable = "data";

Pour que cela corresponde à votre environnement. Vous devez avoir le même nombre de champs que le nombre de colonnes dans le tableau html.

Puis, dans votre page html :

 <table id="dataTest" class="table table-striped table-bordered table-hover">
        <thead>
            <tr>
                <th>Rubrique</th>
                <th>Tableau</th>
                <th>Page</th>
                <th>Année</th>
                <th>Valeur</th>
                <th>Indicateur</th>
                <th>Vertical</th>
                <th>Horizontal</th>
                <th>Ligne</th>
                <th>Colonne</th>
            </tr>
        </thead>

        
        
    </table>
    <script>

        $('#dataTest').DataTable({
            serverSide: true,
            ajax: 'ajax/datatable_pgsql.php',
        });
    </Script>

Testé avec une table de 10000 enregistrements, ça marche très bien, à condition que tous les champs soient bien indexés pour les recherches.

Pour l’instant, on ne gère pas le multi sorting.

Laisser un commentaire

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.