Use Left Join on Datatable Server-Side

0

After several attempts, I have not yet been able to implement a 100% left join within the Datatable Server-Side

Please 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 / protocolos.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_protocolo.php (Only the part that interests you)

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 )
    );
}

Look at this block of code:

"SELECT SQL_CALC_FOUND_ROWS '".implode("', '", self::pluck($columns, 'db'))."'
 FROM '$table'
 $where
 $order
 $limit"
);

I'd like to replace this:

"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 this, it works, the grid is filled, the pagination works, the indexing when I click the column works, however the FILTRATION does not work.

@Bacco, thanks for the comment, see:

My problem is more with the Datatable than with the Left Join itself, but you're absolutely right about WHERE.

I made the modification that you proposed and the filter still did not work, please 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 do not know how this filter works right.

    
asked by anonymous 16.11.2014 / 03:23

1 answer

1

It should not work anyway, since it already has a WHERE in the middle of SELECT and you can not have the filter together.

The question is confusing, but it's probably something along those lines you're looking for:

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
LEFT JOIN tbl_setores s ON l.id_setor = s.id_setor
$where
$order
$limit
    
16.11.2014 / 04:36