Use LIMIT in a Datatable

Question

Good morning! I’m having a little trouble, because I’m a layman on the subject...

I use a DataTable to display some records, whenever I enter a given page, give F5, the table "delay" to "compress", because the page is loading all records of my select, I believe, that the ideal way would be to load the page a registration amount per selected page (table paging), however, I do not know how to do this...

If I just put $sql = "SELECT * FROM contas LIMIT 1, 10";, will only display 10 records from my bank obviously...

I used "pageLength" in the script, but it also, does not prevent the page from loading all records...

So how can I be using the LIMIT or another tool to "stop" the page from loading all records at once?

Script I am using:

<script>
// LISTAGEM SCROLL
$(document).ready(function () {
$('#dtHorizontalExample').DataTable({
"scrollX": true,
"pageLength": 10 
});
$('.dataTables_length').addClass('bs-select');
});
</script>

My SQL:

$sql = "SELECT contas.* , estabelecimento_detalhes.* FROM contas, estabelecimento_detalhes WHERE contas.estabelecimento = estabelecimento_detalhes.estabelecimento AND contas.categoria != 'EMPRESA-X'";

Answer 1

Hello, for you to do this you need to use jQuery Datatable Server-Side I’ll make an example for you to have a better idea.

Based on a table, it would look like this:

<script type="text/javascript">

        $(document).ready(function () {
            $('#myTable').DataTable({
                "ajax": {
                    "url": "/home/loaddataserver",
                    "type": "POST",
                    "datatype":"json"
                },
                "processing": true,
                "serverSide": true,
                "filter": false,
                "orderMulti": false,
                "paging": true,
                "info":false,
                "columns": [
                    { "data": "id", "name": "id", "autoWidth": true },                       //index 0
                    { "data": "nome", "name": "nome", "autoWidth": true },                   //index 1
                    { "data": "fornecedor.nome", "name":"fornecedor.nome", "autoWidth": true },//index 2
                    { "data": "categoria.nome", "name":"categoria.nome", "autoWidth": true },//index 3
                    { "data": "precoDeCusto", "name":"precoDeCusto", "autoWidth": true },  //index 4
                    { "data": "precoDeVenda", "name": "precoDeVenda", "autoWidth": true }, //index 5
                    { "data": "medicao", "name":"medicao", "autoWidth": true }, //index 6
                    { "data": "status", "name":"status", "autoWidth": true } //index 7
                ],
                "language": {
                    "lengthMenu": "Visualizando _MENU_ Registros por página",
                    "zeroRecords": "Não existe registros para visualização",
                    "info": "Mostrando página _PAGE_ de _PAGES_",
                    "infoEmpty": "No records available",
                    "infoFiltered": "(filtered from _MAX_ total records)",
                    "paginate": {
                        "previous": "Página Anterior",
                        "next": "Próxima página"
                    }
                }
            });


    </script>

In your controller you need to do the method that will send and receive the data using json, follow an example done in Asp.net core:

[HttpPost]
        public IActionResult loaddataserver()
        {

            var draw = Request.Form["draw"].FirstOrDefault();
            var start = Request.Form["start"].FirstOrDefault();
            var length = Request.Form["length"].FirstOrDefault();

            var sortColumn = Request.Form["columns"] + Request.Form["order[0][column]"].FirstOrDefault() + "][name]".FirstOrDefault();
            var sortColumnDir = Request.Form["order[0][dir]"].FirstOrDefault();

            int pageSize = length != null ? Convert.ToInt32(length) : 0;
            int skip = start != null ? Convert.ToInt32(start) : 0;
            int totalRecords = 0;

          //  var v = ( database.Produtos.Include(p => p.Categoria).Include(p => p.Fornecedor).Where(p => p.Id <= 35));
            var v = (database.Produtos.Include(p => p.Categoria).Include(p => p.Fornecedor));

            //SORT
            if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir)))
            {
             //   v = v.OrderBy(sortColumn + " " + sortColumnDir);
            }

            totalRecords = v.Count();
            var data = v.Skip(skip).Take(pageSize).ToList();
            return Json(new { draw = draw, recordsFiltered = totalRecords, recordsTotal = totalRecords, data = data });

        }

Answer 2

Good afternoon, Ctjr_tecnico, I’m also new here, but if I understand correctly, in my pages, when I want to limit the amount of record I want to show in Datatable, I put this line: iDisplayLength: 10,

I’ll put my code, I hope it helps you.

    <script>
            $(document).ready(function() {
                var table = $('#tabela').DataTable( {
                  initComplete: function () {
                    this.api().columns().every( function () {
                      var column = this;
                      var select = $('<select><option value=""></option></select>')
                        .appendTo( $(column.footer()).empty() )
                        .on( 'change', function () {
                          var val = $.fn.dataTable.util.escapeRegex(
                            $(this).val()
                          );

                          column
                            .search( val ? '^'+val+'$' : '', true, false )
                            .draw();
                        } );

                      column.data().unique().sort().each( function ( d, j ) {
                                select.append( '<option value="'+d+'">'+d+'</option>' )
                            } );
                        } );
                    },

                    dom: 'lBfrtip',

                    buttons: [
                        'excelHtml5',
                    {
                            extend: 'colvis',
                            collectionLayout: 'fixed four-column',
                            text: 'Visualização'
                        },
                        {
                            extend: 'colvisGroup',
                            text: 'Mostrar tudo',
                            show: ':hidden'
                        }
                    ],

                    iDisplayLength: 10, //está linha fará com que traga até 10 registros na minha página.
                    bPaginate: true, //coloco verdadeiro para ter paginação.
                    bInfo: true, //coloco verdadeiro também para trazer as informações das páginas.
                                    bFilter: true,
                                    "bAutoWidth": false,
                                    "bRetrieve": true,
                                    "bDestroy": true,
                                    "oLanguage": {
                                        "sEmptyTable":     "Nenhum registro encontrado na tabela",
                                        "sInfo": "Mostrar _START_ até _END_ do _TOTAL_ registros",
                                        "sInfoEmpty": "Mostrar 0 até 0 de 0 Registros",
                                        "sInfoFiltered": "(Filtrar de _MAX_ total registros)",
                                        "sInfoPostFix":    "",
                                        "sInfoThousands":  ".",
                                        "sLengthMenu": "Mostrar _MENU_ registros por pagina",
                                        "sLoadingRecords": "Carregando...",
                                        "sProcessing":     "Processando...",
                                        "sZeroRecords": "Nenhum registro encontrado",
                                        "sSearch": "Pesquisar: ",
                                        "oPaginate": {
                                            "sNext": "Proximo",
                                            "sPrevious": "Anterior",
                                            "sFirst": "Primeiro",
                                            "sLast":"Ultimo"
                                        },
                                        "oAria": {
                                            "sSortAscending":  ": Ordenar colunas de forma ascendente",
                                            "sSortDescending": ": Ordenar colunas de forma descendente"
                                        }
                                    },

                });
            } );
        </script>