Server side processing with custom range filtering‎

Server side processing with custom range filtering‎

valmellovalmello Posts: 27Questions: 4Answers: 0
edited July 2011 in DataTables 1.8
Olá, desculpem meu inglês pois não falo esta lingua, mas vou tentar com a ajuda do google me expressar.
Não seria possível criar um exemplo com a utilização do range_filtering utilizando server side?
Já tentei de várias formas sem sucesso, inclusive tendo como base os posts que existem no forum.
Fico no aguardo de uma resposta.

Abraço a todos!

valmello (Salvador, Bahia, Brasil)
«1

Replies

  • numberonenumberone Posts: 86Questions: 0Answers: 0
    Look at Ignited-Datatables range filtering example :
    https://github.com/n1crack/IgnitedDatatables-native-php-version
  • fbasfbas Posts: 1,094Questions: 4Answers: 0
    edited July 2011
    Not a problem, your Portugues is easy enough to understand.

    I don't believe ranger filtering through DataTables will work at all on server-side processing, but you can add to the parameters sent to your server-side code. I do this in my code to specify a range of text strings (in my case just comparing strings, not converting to date on the server side, but you can take it to the next level if you want).

    My HTML page has 2 textboxes, one for the lower range value, one for the higher range value. I attach event listeners to them in $(document).ready() after initializing my table.

    in the DataTables initialization, you can specify parameters to be added to the AJAX query string:
    [code]
    oTable = $('#results_table').dataTable( {
    "bProcessing": true,
    "bServerSide": true,
    "sAjaxSource": "server-side-script-page.php",
    "fnServerData": function ( sSource, aoData, fnCallback ) {
    /* Add some extra data to the sender */
    aoData.push( {"name": "daterecv_start", "value": $('#daterecv_start').val() } );
    aoData.push( {"name": "daterecv_end", "value": $('#daterecv_end').val() } );

    $.getJSON( sSource, aoData, function (json) {
    /* Do whatever additional processing you want on the callback, then tell DataTables */
    fnCallback(json)
    } );
    }, // and the rest of your initializations
    } );
    [/code]


    [code]
    // for daterecv_start and _end fields, trigger refresh of the table
    $('#daterecv_start').keyup( function() {
    oTable.fnDraw();
    } );

    $('#daterecv_end').keyup( function() {
    oTable.fnDraw();
    } );
    [/code]


    Then modify your server side code to look for the params you added and filter/search/sort accordingly.

    Does this help?
  • valmellovalmello Posts: 27Questions: 4Answers: 0
    fbas,

    Você poderia postar também a modificação que ficaria no server side code tomando como base o seu exemplo acima?
    Tentei de algumas formas e não deu certo.
    Fico muito grato se puder me ajudar.

    Grande abraço!
  • fbasfbas Posts: 1,094Questions: 4Answers: 0
    Yes, I will post it when I return to work tomorrow (Monday).
  • valmellovalmello Posts: 27Questions: 4Answers: 0
    Ok! Ficarei no aguardo...
  • fbasfbas Posts: 1,094Questions: 4Answers: 0
    edited July 2011
    I use http://www.datatables.net/release-datatables/examples/data_sources/server_side.html as a base for my server-side (PHP) code.

    With the values I sent using aoData on the client side (javascript), I check if I have these values on the server side and add code to the WHERE clause of the SQL query.

    (this is AFTER the $sWhere variable has been set with any columns sent by client-side, i.e. datatables filter or column filters)


    [code]
    $daterecv_start = $daterecv_end = $date_filter = ""; // initialize to empty string

    if ( isset($_GET['daterecv_start']) && $_GET['daterecv_start'] != "" ) $daterecv_start = " datereceived >= '".mysql_real_escape_string($_GET['daterecv_start'])."'";
    if ( isset($_GET['daterecv_end']) && $_GET['daterecv_end'] != "" ) $daterecv_end = " datereceived <= '".mysql_real_escape_string($_GET['daterecv_end'])."'";
    if ($daterecv_start && $daterecv_end) $date_filter = $daterecv_start . " AND " . $daterecv_end;
    else $date_filter = $daterecv_start . $daterecv_end; // one or none of these has any text in it

    if ($date_filter) {
    if($sWhere == "")
    $sWhere = "WHERE " . $date_filter;
    else
    $sWhere = str_replace("WHERE (", "WHERE (" . $date_filter, $sWhere) . ") AND (";

    [/code]
  • valmellovalmello Posts: 27Questions: 4Answers: 0
    fbas,

    Bom dia!

    Irmão... peço mil desculpas pela insistencia, mas não deu certo.
    Vou postar o código inteiro para você ver o que estou fazendo de errado.

    Abaixo o lado Cliente

    [code]







    /* Custom filtering function which will filter data in column four between two values */
    $.fn.dataTableExt.afnFiltering.push(
    function( oSettings, aData, iDataIndex ) {
    var iMin = document.getElementById('min').value * 1;
    var iMax = document.getElementById('max').value * 1;
    var iVersion = aData[4] == "-" ? 0 : aData[4]*1;
    if ( iMin == "" && iMax == "" )
    {
    return true;
    }
    else if ( iMin == "" && iVersion < iMax )
    {
    return true;
    }
    else if ( iMin < iVersion && "" == iMax )
    {
    return true;
    }
    else if ( iMin < iVersion && iVersion < iMax )
    {
    return true;
    }
    return false;
    }
    );



    var asInitVals = new Array();


    $(document).ready(function() {


    var oTable = $('#example').dataTable( {

    "sPaginationType": "full_numbers",
    "bProcessing": true,
    "bServerSide": true,
    "sAjaxSource": "scripts/server_processing.php",
    "fnServerData": function ( sSource, aoData, fnCallback ) {

    /* Add some data to send to the source, and send as 'POST' */
    aoData.push( { "name": "daterecv_start", "value": $('#daterecv_start').val() } );
    aoData.push( { "name": "daterecv_end", "value": $('#daterecv_end').val() } );

    $.getJSON( sSource, aoData, function (json) {
    /* Do whatever additional processing you want on the callback, then tell DataTables */
    fnCallback(json)
    } );
    }, // and the rest of your initializations

    } );


    // for daterecv_start and _end fields, trigger refresh of the table
    $('#daterecv_start').keyup( function() {
    oTable.fnDraw();
    } );

    $('#daterecv_end').keyup( function() {
    oTable.fnDraw();
    } );




    // Cria no rodapé o multifiltro

    $("tfoot input").keyup( function () {
    /* Filter on the column (the index) of this element */
    oTable.fnFilter( this.value, $("tfoot input").index(this) );
    } );


    /*
    * Support functions to provide a little bit of 'user friendlyness' to the textboxes in
    * the footer
    */
    $("tfoot input").each( function (i) {
    asInitVals[i] = this.value;
    } );

    $("tfoot input").focus( function () {
    if ( this.className == "search_init" )
    {
    this.className = "";
    this.value = "";
    }
    } );

    $("tfoot input").blur( function (i) {
    if ( this.value == "" )
    {
    this.className = "search_init";
    this.value = asInitVals[$("tfoot input").index(this)];
    }
    } );


    // Seleciona multiplas linhas

    var aSelected = [];

    /* Click event handler */
    $('#example tbody tr').live('click', function () {
    var id = this.id;
    var index = jQuery.inArray(id, aSelected);

    if ( index === -1 ) {
    aSelected.push( id );
    } else {
    aSelected.splice( index, 1 );
    }

    $(this).toggleClass('row_selected');
    } );




    } );





    [/code]

    Veja que utilizo outras funções também.
    Todas as outras funções que coloquei funcionam perfeitamente [ filtro geral, filtro por coluna, quantidade de exibição, seleção de multiplos registros. ]

    Apenas estou me mordendo neste filtro por faixa de dados.
    Abaixo a forma que você orientou no lado servidor.

    [code]

    /* Range filtering */

    $daterecv_start = $daterecv_end = $date_filter = ""; // initialize to empty string

    if ( isset($_GET['daterecv_start']) && $_GET['daterecv_start'] != "" ) $daterecv_start = " datereceived >= '".mysql_real_escape_string($_GET['daterecv_start'])."'";
    if ( isset($_GET['daterecv_end']) && $_GET['daterecv_end'] != "" ) $daterecv_end = " datereceived <= '".mysql_real_escape_string($_GET['daterecv_end'])."'";
    if ($daterecv_start && $daterecv_end) $date_filter = $daterecv_start . " AND " . $daterecv_end;
    else $date_filter = $daterecv_start . $daterecv_end; // one or none of these has any text in it

    if ($date_filter) {
    if($sWhere == "")
    $sWhere = "WHERE " . $date_filter;
    else
    $sWhere = str_replace("WHERE (", "WHERE (" . $date_filter, $sWhere) . ") AND (";
    }

    [/code]

    Onde estou realmente errando?
  • fbasfbas Posts: 1,094Questions: 4Answers: 0
    Can you post a link to your site?
  • fbasfbas Posts: 1,094Questions: 4Answers: 0
    Here's a link to my code:

    http://www.beg.utexas.edu/_work/datatables.net.php

    There is a lot of other stuff in there, which might be confusing.
  • valmellovalmello Posts: 27Questions: 4Answers: 0
    Realmente ficou um pouco complicado de entender o seu código, por isso estou encaminhando um link onde estou preparando as funções que vou precisar antes de aplicar em meu sistema.

    No exemplo a seguir preciso filtrar o campo "versão", que está no formato de data [ dd/mm/yyyy ]

    http://www.q-werty.com/datatables/examples/server_side/server_side.html

    Aproveito para perguntar como eu poderia aplicar um efeito igual ao que coloquei na tabela de filtro no topo da pagina, onde ao passar o mouse na tr exibe um backgroud. No exemplo que você coloca ele seleciona multiplas tr. Queria que alem disso mostrasse um over de uma cor diferente.

    Se possível gostaria de te mandar os arquivos para ficar mais fácil de você visualizar.
    Caso possa, me forneça seu email que te envio os arquivos e o sql para você efetuar os testes.

    Grande abraço!
  • fbasfbas Posts: 1,094Questions: 4Answers: 0
    edited July 2011
    yes, send to fbastage@yahoo.com

    but looking at your example page in the link above, you are having issues with your SQL query in the server_processing.php.

    [quote] "Unknown column 'datereceived' in 'where clause'" [/quote]

    My database has a column named 'datereceived', but you will have to change that name to whatever your dates are in your database ('Versão' or 'version'). see lines 5 and 6.

    [code]
    /* Range filtering */

    $daterecv_start = $daterecv_end = $date_filter = ""; // initialize to empty string

    if ( isset($_GET['daterecv_start']) && $_GET['daterecv_start'] != "" ) $daterecv_start = " Versão >= '".mysql_real_escape_string($_GET['daterecv_start'])."'";
    if ( isset($_GET['daterecv_end']) && $_GET['daterecv_end'] != "" ) $daterecv_end = " Versão <= '".mysql_real_escape_string($_GET['daterecv_end'])."'";
    if ($daterecv_start && $daterecv_end) $date_filter = $daterecv_start . " AND " . $daterecv_end;
    else $date_filter = $daterecv_start . $daterecv_end; // one or none of these has any text in it

    if ($date_filter) {
    if($sWhere == "")
    $sWhere = "WHERE " . $date_filter;
    else
    $sWhere = str_replace("WHERE (", "WHERE (" . $date_filter, $sWhere) . ") AND (";
    }
    [/code]

    I would like to see what your server_processing.php file looks like.
  • valmellovalmello Posts: 27Questions: 4Answers: 0
    fbas,

    Foi justamente onde eu estava errando, pois agora com esta sua explicação deu certo o filtro. Porém só funcionou depois que setei o campo "version" como DATE, pois como VARCHAR não estava considerando o valor total.

    Só que aqui no Brasil o formato DATE é dd/mm/yyyy. Como eu poderia exibir a data no lado cliente neste formato? Tem como fazer isso ou o seu fremework só recupera os valores da forma que está no banco?

    Veja minha página de exemplo como está agora depois que o filtro funcionou.

    http://www.q-werty.com/datatables/examples/server_side/server_side.html

    Fico no aguardo da resposta com relação ao formato da data.

    Abração e + 1 x obrigado!
  • fbasfbas Posts: 1,094Questions: 4Answers: 0
    edited July 2011
    You can have your database SELECT statement run a function to format the date any way you want it. In MySQL it is like this:
    [code]
    SELECT DATE_FORMAT(version, '%d-%m-%Y') as version FROM tablename;
    [/code]
    http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

    where "version" is the column name and "tablename" is the tablename. You can put this into the sName for that column in the DataTable column definition and you won't have to change any of your server side code.

    [code]
    $(document).ready(function() {
    var oTable = $('#example').dataTable({
    "sPaginationType": "full_numbers",
    "bProcessing": true,
    "bServerSide": true,
    "sAjaxSource": "scripts/server_processing.php",
    "aoColumnDefs": { "aTargets": [ 4 ], "sName": "DATE_FORMAT(version, '%d-%m-%Y') as version" }, // set aTargets to the correct column
    // and the rest of your initializations
    }
    }
    [/code]


    in the server_processing.php file, you can have your users input dates as dd-mm-yyyy and then have your php code convert to a format that your database expects

    [code]
    if (isset($_GET["daterecv_start"]) {
    $datearray = explode($_GET["daterecv_start"], "/");
    $iTime = strtotime($datestr = $datearray[2] . "-" . $datearray[1] . "-" . $datearray[0]); // have system parse this date to make sure it's a valid date

    if ($iTime) $datestr = " datereceived <= '". date('Y', $iTime) . '-' . date('m', $iTime) . '-' . date('d', $iTime) . "'";

    // now you can use this datestring with your database
    }
    [/code]
    http://php.net/manual/en/function.strtotime.php

    You might not have to go through all this with your date. Your localization settings on the database might already work with the date format you prefer.

    But the documents seem to indicate requiring YYYY-MM-DD format
    [quote]
    Although MySQL tries to interpret values in several formats, dates always must be given in year-month-day order (for example, '98-09-04'), rather than in the month-day-year or day-month-year orders commonly used elsewhere (for example, '09-04-98', '04-09-98').
    [/quote]
    http://dev.mysql.com/doc/refman/5.5/en/date-and-time-types.html
  • numberonenumberone Posts: 86Questions: 0Answers: 0
    edited July 2011
    Some examples for range filtering with Ignited Datatables:

    http://numberone.kodingen.com/datatables/examples/range_filtering/

    http://numberone.kodingen.com/datatables/examples/range_filtering_date/
  • fbasfbas Posts: 1,094Questions: 4Answers: 0
    What is CodeIgniter, if you could summarize it for me?
  • numberonenumberone Posts: 86Questions: 0Answers: 0
    Codeigniter is a php framework (http://codeigniter.com/) but you dont have to use it.

    Here is the native php library https://github.com/n1crack/IgnitedDatatables-native-php-version which has no dependency on Codeigniter.
  • valmellovalmello Posts: 27Questions: 4Answers: 0
    edited July 2011
    numberone,

    Você poderia me encaminhar os arquivos zipados referente ao exemplo que você postou abaixo:

    http://numberone.kodingen.com/datatables/examples/range_filtering_date/

    Email: rosevalmello@uol.com.br
  • valmellovalmello Posts: 27Questions: 4Answers: 0
    fbas,

    O lado do cliente funcionou bem, porém o sName não tá dando certo.
    Usei o sName com outras funções e funcionou, porém para o DATE_FORMAT não.

    Coloquei desta forma e não funcionou:

    [code]

    /*"aoColumnDefs": {
    "aTargets": [ 4 ],
    "sName": "DATE_FORMAT(version, '%d-%m-%Y') as version"
    }, // set aTargets to the correct column*/


    [/code]

    Coloquei desta outra forma e também não funcionou :

    [code]

    "aoColumnDefs": [
    {
    "aTargets": [4],
    "sName": "DATE_FORMAT(version, '%d-%m-%Y') as version",
    }
    ],

    [/code]

    Porém se eu utilizar um outro recurso, por exemplo o que segue abaixo, dá certo :

    "aoColumnDefs": [
    {
    "aTargets": [4],
    "bVisible": false,
    }
    ],

    Então creio que o probelma está nesta parte : "sName": "DATE_FORMAT(version, '%d-%m-%Y') as version"
    É desta forma mesmo?
  • numberonenumberone Posts: 86Questions: 0Answers: 0
    hi valmello,
    you can download the files here:
    http://www.2shared.com/file/tQ67VZQ7/range_filtering_date.html
  • valmellovalmello Posts: 27Questions: 4Answers: 0
    edited July 2011
    Valeu numberone!

    Do jeito que está o seu código ficou mais viável para o meu projeto.
    Agradeço de verdade a você e, ao fbas também que foi atencioso pra caralho...., porém para ficar perfeito preciso de mais 2 ajudas e meia:

    1. Como colocar o "individual column filtering" para funcionar?
    1.1. Desejo que a filtragem do individual fique entre o e o e não no , como no exemplo a seguir:
    http://jquery-datatables-column-filter.googlecode.com/svn/trunk/numberRange.html
    Eu mudo isso no css ou no script?

    2. Como exibir a coluna "Order Date" no formato Brasil dd/mm/yyyy?

    Segue link de como ficou depois das minhas alterações para você verificar de como pretendo que fique meu projeto.

    http://www.q-werty.com/numberone/range_filtering_date/

    Abraços!
  • numberonenumberone Posts: 86Questions: 0Answers: 0
    2. Como exibir a coluna "Order Date" no formato Brasil dd/mm/yyyy?
    check : http://numberone.kodingen.com/datatables/examples/range_filtering_date/
    I've reuploaded it.

    1. Como colocar o "individual column filtering" para funcionar?
    Not supported atm. I will add that feature as soon as possible.
  • fbasfbas Posts: 1,094Questions: 4Answers: 0
    I thought about it after I posted that code and using sName is not the best idea because it will probably break the WHERE and ORDER BY portions of the SQL on the client side.

    a better approach would be to edit the server_processing.php for that column just for the SELECT sql clause:
    [code]
    if (isset($_GET['sColumns'])) {
    $sColumns = $_GET['sColumns'];
    $aColumns = explode(',', $sColumns);
    }

    for ($i = 0; $i < count($aColumns); $i++) {
    if ($aColumns[$i] == "version") // change to the name of your column
    $aColumns[$i] = "DATE_FORMAT(version, '%d-%m-%Y') as version";
    }

    $sColumns = "SELECT " . impode(',', $aColumns);

    [/code]

    then use $sSelect in your $sQuery
    [code]
    $sQuery = "
    SELECT SQL_CALC_FOUND_ROWS ".$sColumns."
    FROM $sTable
    $sWhere
    $sOrder
    $sLimit
    ";
    $rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
    [/code]

    I think numberone's library looks good too, so if that's simpler, use that.
  • numberonenumberone Posts: 86Questions: 0Answers: 0
    edited July 2011
    well,
    I have updated github. download the library again.
    https://github.com/n1crack/IgnitedDatatables-native-php-version

    and i have made a new example on column filtering. now it supports "individual column filtering".
    http://numberone.kodingen.com/datatables/examples/multi_filtering/

    When you use "->filter()" instead of "->where()", it filters the data and adds "(filtered from xxx total entries)"

    see differences with http://numberone.kodingen.com/datatables/examples/range_filtering_date/

    Regards,
    Yusuf
  • fbasfbas Posts: 1,094Questions: 4Answers: 0
    n1, I dowloaded the package and went to run the examples in that directory. I need to set up the "sakila" db and "films" table, etc.

    Do you have an .sql file with the defs for this database?
  • numberonenumberone Posts: 86Questions: 0Answers: 0
    edited July 2011
    Sakila :
    http://downloads.mysql.com/docs/sakila-db.zip

    BIRT db for examples on kodingen :
    http://www.eclipse.org/birt/phoenix/db/#mysql
  • valmellovalmello Posts: 27Questions: 4Answers: 0
    numberone,

    Perfect!!!
    Só um detalhe que acho que você não entendeu. Talvez eu não tenha me expressado direito.
    Quando me referi ao formato de data do Brasil dd/mm/yyyy, eu estava me referindo a exibixão do resultado e não do campo de filtro por faixa. A consulta do filtro eu já tinha conseguido fazer aplicando uma função. Mas, de qualquer forma valeu, pois da sua forma ficou bem melhor.

    O que eu preciso é que na listagem ao inves de exibir desta forma:
    10100 2003-01-06 00:00:00 Shipped 10.00

    Seja exibido da seguinte forma :
    10100 06/01/2003 00:00:00 Shipped 10.00

    Acho que assim ficou melhor explicado.

    ::::::

    Outra coisa,

    Tentei aplicar a função abaixo para valores em R$, porém no momento de avançar a paginação informa que há comandos não definidos. Você poderia aplicar isso para mim.

    http://www.datatables.net/release-datatables/examples/advanced_init/footer_callback.html

    ::::::

    Cara, desculpas tantas perguntas, mas se fizeres estes procedimentos para mim, salvará vários projetos meus.

    Muito, muito obrigado mesmo!
  • numberonenumberone Posts: 86Questions: 0Answers: 0
    edited July 2011
    You can format your date values in both javascript or serverside (php).

    Here is the server-side solution :
    [code]
    $datatables
    ->select('orderNumber, orderDate, status, customerNumber')
    ->from('Orders')
    ->edit_column('orderDate', '$1', 'callback_my_formatdate(orderDate)'); /// add this line

    if(isset($_POST['from_date']) && $_POST['from_date'] != '')
    $datatables->filter('orderDate >=', preg_replace("/([0-9]{1,2})\/([0-9]{1,2})\/([0-9]{4})/i", "$3-$2-$1", $_POST['from_date']));

    if(isset($_POST['to_date']) && $_POST['to_date'] != '')
    $datatables->filter('orderDate <=', preg_replace("/([0-9]{1,2})\/([0-9]{1,2})\/([0-9]{4})/i", "$3-$2-$1", $_POST['to_date']));

    echo $datatables->generate();

    function my_formatdate($date) { /// and this function
    return date( 'd/m/Y H:i:s', strtotime($date) );
    }
    [/code]

    and let me think about your second problem ^^.

    Regards,
    Yusuf
  • valmellovalmello Posts: 27Questions: 4Answers: 0
    Yusuf,

    Funcionou perfeitamente, onde aproveitei e adaptei para exibição de valores em moeda do Brasil (R$). Ficou muito bom e bem facil de entender. Valeu mesmo.
    Com relação ao segundo problema ficarei no aguardo do seu pensamento!

    Grande abraço e mais uma vez muito obrigado!

    --
    valmello
  • numberonenumberone Posts: 86Questions: 0Answers: 0
    about second issue, i'm afraid it is not possible with server-side scripts at the moment.

    Regards,
    Yusuf
  • valmellovalmello Posts: 27Questions: 4Answers: 0
    Ok!!!
    Vou submeter, por enquanto, o resultado da filtragem para uma outra página onde exiba as informações dos totais. Mas, seria super interessante ter estas informações no display para server-side. Ficaria + show ainda!

    Aproveitando a oportunidade para perguntar outra duvida que surgiu.
    Tenho como determinar a largura da coluna (width="50px") por exemplo, assim como colocar um style="text-align: right;", em colunas predeterminadas usando server-side?

    Valeu!

    --
    Val
This discussion has been closed.