define the number of columns, their names and tha labels for the table, taken for the query

jcdelpuerto · November 2019

In Laravel 6.0

I have a model for my table named "Sis_Tables":

app/Sis_Table

<?php

namespace App;
use Illuminate\Database\Eloquent\Model;
class Sis_Table extends Model
{
    //
    protected $table='Sis_Tables';
    protected $primarykey='Id_Tabla';
    protected $fillable = [
        'Id_Tabla',
        'Tabla',
        'Desc_Tabla',
        'Id_Activo'
    ];
}

And I have a Controller where I define my queyry:

Sis_TableController.php

<?php
namespace App\Http\Controllers;
use App\Sis_Table;
use Illuminate\Http\Request;
use DataTables;
use DB;
class Sis_TableController extends Controller
{
public function Sis_Table_Admon_List()
    {
        $sis_table = DB::table('Sis_Tables')->select(
        'Id_Tabla',
        'Tabla',
        'Desc_Tabla',
        'Id_Activo')->where('Tipo_Tabla','=','Admon_Sistema');
        return datatables()->of($sis_table)
            ->make(true);
    }
}

In my html I defined my datable and all works fine:

<div class="table-responsive">
    <table id="table_table" class="table table-bordered table-striped">
        <thead>
            <tr>
                <th width="10%">Id</th>
                <th width="30%">Tabla</th>
                <th width="50%">Descripción</th>
                <th width="10%">Activo</th>                     
            </tr>
        </thead>
    </table>
</div>

ajax: "{{ url('Sis_Table_Admon_List') }}",
columns: [
    {data: 'Id_Tabla', name: 'Id_Tabla'},
    {data: 'Tabla', name: 'Tabla'},
    {data: 'Desc_Tabla', name: 'Desc_Tabla'},
    {data: 'Id_Activo', name: 'Id_Activo'}
]

In this case I know the number of columns and their names, but what I want to do, is to define the number of columns, their names and tha labels for the table, taken for the query.

Is that posible?

colin · November 2019

This thread should help, it's asking the same thing.

Cheers,

Colin

jcdelpuerto · November 2019

I think that's just what I want to do, but I don't make it. I put my code as is, the example and gives me this error in the console:

VM5442:1 Uncaught SyntaxError: Unexpected token o in JSON at position 1
at JSON.parse (<anonymous>)
at Object.success (admon:140)
at c (jquery-3.4.1.min.js:2)
at Object.fireWith [as resolveWith] (jquery-3.4.1.min.js:2)
at l (jquery-3.4.1.min.js:2)
at XMLHttpRequest.<anonymous> (jquery-3.4.1.min.js:2)

Can you help me, pleaseeeeeee!

My route for get data is:


Route::get('/send-file-temp', 'Sis_TableController@Sis_Table_Admon_List')->name('Sis_Table_Admon_List');

My function for get data is:


public function Sis_Table_Admon_List()
    {
        $sis_table = DB::table('Sis_Tables')->select(
        'Id_Tabla',
        'Tabla',
        'Desc_Tabla',
        )->where('Tipo_Tabla','=','Admon_Sistema');
        return datatables()->of($sis_table)
            ->make(true);
    }

My new JS is that:


                       var columns = [];

            function getDT() {
                $.ajax({
                    url: '/send-file-temp',
                    success: function (data) {
                        data = JSON.parse(data);
                        columnNames = Object.keys(data.data[0]);
                        for (var i in columnNames) {
                            columns.push({data: columnNames[i], 
                            title: capitalizeFirstLetter(columnNames[i])});
                        }
                        $('#table_table').DataTable( {
                            @include("partials.definicion_tablas")
                            data: data.data,
                            columns: columns,
                            @include("partials.traduccion_tablas")
                        } );
                    }   
                });
            }

            function capitalizeFirstLetter(string) {
                return string.charAt(0).toUpperCase() + string.slice(1);
            }

            $(document).ready(function() {
                @include("partials.token_for_script")
                getDT();
            } );

kthorngren · November 2019

VM5442:1 Uncaught SyntaxError: Unexpected token o in JSON at position 1

Take a look at the JSON response using the browser's developer tools > network option. In chrome the Response tab will have the raw data which you can copy into https://jsonlint.com/ to validate the JSON string.

The issue is not Datatables specific. The problem is occurring with this statement data = JSON.parse(data);. It sounds like the server script is not returning a valid JSON string.

Kevin

jcdelpuerto · November 2019

The problem was actually in the statement data = JSON.parse(data); and I was able to fix the first error by changing the statement to: data = JSON.parse(JSON.stringify(data);
With this change I managed to define the number of columns and the titles of each column, from querying the data.

The JSON that the server responds to is as follows:

{
    "draw": 0,
    "recordsTotal": 6,
    "recordsFiltered": 6,
    "data": [{
        "Id": "1",
        "Nombre de Tabla": "Adm_Cargos",
        "Descripci\u00f3n": "Cargos y Puestos"
    }, {
        "Id": "2",
        "Nombre de Tabla": "Adm_Estatus_Acuerdos",
        "Descripci\u00f3n": "Estatus de los acuerdos"
    }, {
        "Id": "3",
        "Nombre de Tabla": "Adm_Estatus_Invitaciones",
        "Descripci\u00f3n": "Estatus de invitaciones"
    }, {
        "Id": "4",
        "Nombre de Tabla": "Adm_Estatus_Pre_Registros",
        "Descripci\u00f3n": "Estatus de pre-registros"
    }, {
        "Id": "5",
        "Nombre de Tabla": "Adm_Estatus_Sepomexs",
        "Descripci\u00f3n": "Estatus env\u00edos Sepomex"
    }, {
        "Id": "6",
        "Nombre de Tabla": "Adm_Estatus_Servicios",
        "Descripci\u00f3n": "Estastus de los servicios"
    }],
    "queries": [{
        "query": "select count(*) as aggregate from (select '1' as `row_count` from `Sis_Tables` where `Tipo_Tabla` = ?) count_row_table",
        "bindings": ["Admon_Sistema"],
        "time": "29.48"
    }, {
        "query": "select `Id_Tabla` as `Id`, `Tabla` as `Nombre de Tabla`, `Desc_Tabla` as `Descripci\u00c3\u00b3n` from `Sis_Tables` where `Tipo_Tabla` = ?",
        "bindings": ["Admon_Sistema"],
        "time": "0.92"
    }],
    "input": []
}

My script for the table is as follows:

        <script  type="text/javascript">
            
            var columns = [];

            function getDT() {
                $.ajax({
                    url: '/send-file-temp',
                    success: function (data) {
                        var myData = JSON.parse(JSON.stringify(data));
                        columnNames = Object.keys(myData.data[0]);
                        for (var i in columnNames) {
                            columns.push({
                                data: columnNames[i],
                                title: columnNames[i]
                            });
                        }
                        
                        $('#table_table').DataTable( {
                            dom: 'B<"clear">frtip',
                            lengthMenu: [
                                [ 10, 25, 50, -1 ],
                                [ '10 registros', '25 registros', '50 registros', 'Mostrar todos' ]
                            ],
                            buttons: [
                                { extend: 'pageLength', text: 'Mostrar %d registros', className: 'btn btn-success' },
                                { extend: 'copy', text: 'Copiar' },
                                { extend: 'excel', text: 'Excel' },
                                { extend: 'pdf', text: 'PDF' },
                                { extend: 'colvis', text: 'Visibilidad' }
                            ],
                            processing: true,
                            serverSide: true,
                            
                            data: myData.data,
                            columns: columns,
                            
                        } );
                    }   
                });
            }

            
            $(document).ready(function() {
                @include("partials.token_for_script")
                getDT();
            } );

        </script>

And the new problem is with the data:

jquery.dataTables.min.js:50 Uncaught TypeError: Cannot set property 'data' of null
    at va (jquery.dataTables.min.js:50)
    at qb (jquery.dataTables.min.js:50)
    at S (jquery.dataTables.min.js:42)
    at V (jquery.dataTables.min.js:44)
    at ja (jquery.dataTables.min.js:62)
    at e (jquery.dataTables.min.js:106)
    at HTMLTableElement.<anonymous> (jquery.dataTables.min.js:106)
    at Function.each (jquery-3.4.1.min.js:2)
    at k.fn.init.each (jquery-3.4.1.min.js:2)
    at k.fn.init.q [as dataTable] (jquery.dataTables.min.js:96)

When running the table is created, the column titles are displayed, it shows a sign that says "processing" and then displays a sign that says: “DataTables warning: table id=table_table - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1”

I think I'm very close to achieving what I want, but I'm still missing something, some idea that can help me??

colin · November 2019

That's probably because you have serverSide enabled which means the client will talk to the server for data, but you haven't defined any ajax properties to say who to talk to!

Colin

kthorngren · November 2019

Also with serverSide processing enabled you don't use the data option to add data to the table. The server side processing component will control the data shown. Use one or the other but not both. Do you need to use server side processing?

The example I provided in the other thread is run in an environment where I don't have control over the server script. The Ajax request is fetching all the data in the table. If you are using server side processing then URL in the Ajax function should be one that only returns the column information not all the data.

This is essentially the same example using server side processing:
http://live.datatables.net/qimukefe/1/edit

Using data = JSON.parse(JSON.stringify(data); is redundant. You are taking a Javascript object converting it to a JSON string then turning it back into a Javascript object. If the data received in the success function, ie success: function (data) {, is a Javascript object then you don't need to do anything with it.

Kevin

jcdelpuerto · November 2019

Muchas gracias se resolvió mi tema!

define the number of columns, their names and tha labels for the table, taken for the query

define the number of columns, their names and tha labels for the table, taken for the query

Answers

Howdy, Stranger!

Categories

DataTables

define the number of columns, their names and tha labels for the table, taken for the query

define the number of columns, their names and tha labels for the table, taken for the query

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables