1
After several attempts, I still haven’t been able to implement a 100% left Join within the Datatable Server-Side
Note, the following code works 100%
HTML
<table id="datatable_fixed_column" class="table table-striped table-bordered" width="100%">
<thead>
<tr>
<th>Protocolo</th>
<th>Assunto</th>
<th>Localização</th>
</tr>
</thead>
JAVASCRIPT
<script type="text/javascript">
$(document).ready(function() {
var otable = $('#datatable_fixed_column').DataTable({
"processing": true,
"serverSide": true,
"ajax": "server_processing/protocolos.php",
"order": [[ 0, "asc" ]],
"columnDefs": [
{ "width": "20%", "targets": 0 },
{ "width": "50%", "targets": 1 },
{ "width": "30%", "targets": 2 },
],
"sDom": "<'dt-toolbar'<'col-xs-6'f><'col-xs-6'<'toolbar'>>r>"+
"t"+
"<'dt-toolbar-footer'<'col-xs-6'i><'col-xs-6'p>>"
});
$("div.toolbar").html('<div class="text-right"><img src="img/logo2.png" alt="SmartAdmin" style="width: 111px; margin-top: 3px; margin-right: 10px;"></div>');
})
</script>
server_processing/protocols.php
$table = 'tbl_protocolos';
$primaryKey = 'id_protocolo';
$columns = array(
array( 'db' => 'protocolo', 'dt' => 0 ),
array( 'db' => 'assunto', 'dt' => 1 ),
array( 'db' => 'id_local', 'dt' => 2 )
);
$sql_details = array(
'user' => 'root',
'pass' => '*****',
'db' => '***',
'host' => 'localhost'
);
require( 'ssp.class_protocolo.php' );
echo json_encode(
SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);
ssp.class_protocol.php ( Only part q interests )
static function simple ( $request, $sql_details, $table, $primaryKey, $columns )
{
$bindings = array();
$db = self::sql_connect( $sql_details );
// Build the SQL query string from the request
$limit = self::limit( $request, $columns );
$order = self::order( $request, $columns );
$where = self::filter( $request, $columns, $bindings );
// Main query to actually get the data
$data = self::sql_exec( $db, $bindings,
"SELECT SQL_CALC_FOUND_ROWS `".implode("`, `", self::pluck($columns, 'db'))."`
FROM `$table`
$where
$order
$limit"
);
);
// Data set length after filtering
$resFilterLength = self::sql_exec( $db,
"SELECT FOUND_ROWS()"
);
$recordsFiltered = $resFilterLength[0][0];
// Total data set length
$resTotalLength = self::sql_exec( $db,
"SELECT COUNT(`{$primaryKey}`)
FROM `$table`"
);
$recordsTotal = $resTotalLength[0][0];
/*
* Output
*/
return array(
"draw" => intval( $request['draw'] ),
"recordsTotal" => intval( $recordsTotal ),
"recordsFiltered" => intval( $recordsFiltered ),
"data" => self::data_output( $columns, $data )
);
}
Watch this block of code:
"SELECT SQL_CALC_FOUND_ROWS `".implode("`, `", self::pluck($columns, 'db'))."`
FROM `$table`
$where
$order
$limit"
);
I’d like to substitute for that:
"SELECT SQL_CALC_FOUND_ROWS p.protocolo, p.assunto, s.nome
FROM $table p LEFT JOIN tbl_prot_local l ON p.id_protocolo = l.id_prot, tbl_setores s where l.id_setor = s.id_setor
$where
$order
$limit"
When I do, it works, the grid is filled, paging works, indexing when I click the column works, but FILTERING does not work.
@Bacco, thanks for the comment, see:
My problem is more with Datatable than with Left Join itself, but you’re absolutely right about WHERE.
I made the modification that you propose and the filter hasn’t worked yet, note the code below:
static function filter ( $request, $columns, &$bindings )
{
$globalSearch = array();
$columnSearch = array();
$dtColumns = self::pluck( $columns, 'dt' );
if ( isset($request['search']) && $request['search']['value'] != '' ) {
$str = $request['search']['value'];
for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
$requestColumn = $request['columns'][$i];
$columnIdx = array_search( $requestColumn['data'], $dtColumns );
$column = $columns[ $columnIdx ];
if ( $requestColumn['searchable'] == 'true' ) {
$binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
$globalSearch[] = "`".$column['db']."` LIKE ".$binding;
}
}
}
// Individual column filtering
for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
$requestColumn = $request['columns'][$i];
$columnIdx = array_search( $requestColumn['data'], $dtColumns );
$column = $columns[ $columnIdx ];
$str = $requestColumn['search']['value'];
if ( $requestColumn['searchable'] == 'true' &&
$str != '' ) {
$binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
$columnSearch[] = "`".$column['db']."` LIKE ".$binding;
}
}
// Combine the filters into a single string
$where = '';
if ( count( $globalSearch ) ) {
$where = '('.implode(' OR ', $globalSearch).')';
}
if ( count( $columnSearch ) ) {
$where = $where === '' ?
implode(' AND ', $columnSearch) :
$where .' AND '. implode(' AND ', $columnSearch);
}
if ( $where !== '' ) {
$where = 'WHERE '.$where;
}
return $where;
}
that’s where the $Where comes out, I think I have to put a p. somewhere so the Where would look something like:
where p.protocolo = '12345'
I don’t know how this filter works.