Server side processing with custom range filtering‎

valmello

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)

numberone

Look at Ignited-Datatables range filtering example :
https://github.com/n1crack/IgnitedDatatables-native-php-version

fbas

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?

valmello

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!

fbas

Yes, I will post it when I return to work tomorrow (Monday).

valmello

Ok! Ficarei no aguardo...

fbas

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]

valmello

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?

fbas

Can you post a link to your site?

fbas

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.

valmello

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!

fbas

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.

valmello

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!

fbas

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

numberone

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/

fbas

What is CodeIgniter, if you could summarize it for me?

numberone

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.

valmello

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

valmello

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?

numberone

hi valmello,
you can download the files here:
http://www.2shared.com/file/tQ67VZQ7/range_filtering_date.html

valmello

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!

numberone

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.

fbas

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.

numberone

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

fbas

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?

numberone

Sakila :
http://downloads.mysql.com/docs/sakila-db.zip

BIRT db for examples on kodingen :
http://www.eclipse.org/birt/phoenix/db/#mysql

valmello

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!

numberone

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

valmello

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

numberone

about second issue, i'm afraid it is not possible with server-side scripts at the moment.

Regards,
Yusuf

valmello

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