Automatic translation of database values

cmplus · July 2023

Good morning to all of you in the forum, I wanted to ask for help, before I loaded the tables by inserting the values in this way and I was able to translate the values from the database without problems, but now I had to change the method for reasons of table loading time, the loading was too long and it didn't go well, now it loads very fast but I can't translate the values from the database, how can I solve the problem? I created a translation file in resources/lang that I used to translate the values but now I don't know how to call that file and see the translation as I saw it before

before I saw the translations I loaded the data like this

@foreach($tickets as $ticket)

<td>{{ trans($ticket['code']) }}</td>

now with this mode I don't see any more translations

 ajax: '{{ route('tickets.index') }}',
 columns: [
   {data: 'code',
      searchPanes: {
       show: true
      }
},

allan · July 2023

Assuming you still want the translation to happen at the server-side, you would do it in whatever script the ajax property resolves to.

That script must be reading from a database (or some other data store)? Instead of having it output HTML as you were before (albeit via what looks like a handlebars template) you are having it output JSON, but you can still do translations on it (you might even be able to use handlebars to output the JSON if you really wanted, but there are probably more efficient ways).

The exact answer will depend upon how you are generating the JSON - but that's where you want to do the translation - before DataTables sees the data at all.

Allan

cmplus · July 2023

Thanks for the answer, I don't know why I tried to answer several times yesterday but I don't see the answer, I try again to write it. I need to translate the values in the database, before I did it with that code but the data loading was too slow, I tried to do it this way but now I'm having problems that I don't know how to solve. I can't get the translation to work and the searchPanes remain empty with no values. the code is this

@push('scripts')
            <script type="text/javascript">
                $(function () {
                    var table = $('table.display').DataTable({
                        dom: 'PBfrtip',
                        searchPanes:{
                            cascadePanes: true,
                            initCollapsed: true,
                            show: true,
                            dtOpts:{
                                dom: 'tp',
                                paging: 'true',
                                pagingType: 'simple',
                                searching: true
                            }
                        },
                        language: {
                            url: "//cdn.datatables.net/plug-ins/1.13.1/i18n/it-IT.json"
                        },
                        buttons:[
                            {
                                extend:     'copyHtml5',
                                text:       'Copia',
                                titleAttr:  'Copia',
                                className:  'btn'
                            }, 
                            {
                                extend:     'csvHtml5',
                                text:      'CSV',
                                titleAttr: 'CSV',
                                className: 'btn '
                            }, 
                            {
                                extend:    'excelHtml5',
                                text:      'Excel',
                                titleAttr: 'Excel',
                                className: 'btn '
                            }, 
                            {
                                extend:    'pdfHtml5',
                                orientation: 'landscape',
                                pageSize: 'LEGAL',
                                text:      'PDF',
                                titleAttr: 'PDF',
                                className: 'btn '
                            }, 
                            {
                                extend:    'print',
                                text:      'Stampa',
                                titleAttr: 'Stampa',
                                className: 'btn '
                            },
                            { 
                                extend: "colvis",
                                postfixButtons: ["colvisRestore"],
                            }
                        ],
                        deferRender: true,
                        responsive: true,
                        lengthChange: true,
                        orderCellsTop: true,
                        fixedHeader: true,
                        select: true,
                        processing: true,
                        serverSide: true,
                        stateSave: true,
                        ajax: '{{ route('tickets.index') }}',
                        columns: [
                            {data: 'id',
                                searchPanes: {
                                show: true
                                }
                            },
                            {data: 'time',
                                searchPanes: {
                                show: true
                                }
                            },
                            {data: 'priority',
                                searchPanes: {
                                show: true
                                }
                            },
                            {data: 'code',
                                searchPanes: {
                                show: true
                                }
                            },
                            {data: 'category',
                                searchPanes: {
                                show: true
                                }
                            },
                            {data: 'action', name: 'Azioni', orderable: false, searchable: false,
                                searchPanes: {
                                    show: false
                                }
                            },
                        ],
                        columnDefs: [
                            {
                                searchPanes: {
                                    show: true
                                },
                                targets: [0, 1, 2, 3, 4, 5]
                            }
                        ]
                    });
                });
            </script>
        @endpush

controller

    public function index(Request $request)
    {
        if ($request->ajax()) {
            $data = Ticket::select('*');
            return DataTables::of($data)
                ->addIndexColumn()
                ->addColumn('action', function($row){
                    $btn  = '<a href="'.route('tickets.show', $row->id).'"><i class="ti-eye"></i></a>
                            <a href="'.route('tickets.edit', $row->id).'"><i class="ti-pencil"></i></a>
                            <a href="'.route('tickets.create', $row->id).'"><i class="icon-plus"></i></a>';
                    return $btn;
                })
                ->rawColumns(['action'])
                ->make(true);
        }
        return view('tickets.index');
    }

allan · July 2023

I don't know why I tried to answer several times yesterday but I don't see the answer

Sorry - looks like the spam filter was a bit trigger happy. I've marked your account to bypass it now .

serverSide: true,

That is seriously going to complicate things. How many records do you have? If less than 10'000, remove that option and just use client-side processing.

If you do need server-side processing. then things get complicated since the filtering is done at the server-side, but the data at the client-side is translated. Its possible, but if client-side processing is an option, use it!

Allan

cmplus · July 2023

Thanks for the reply, when I delete or put false on serverSide: true I get this error,
DataTables warning: table id=DataTables_Table_0 - Ajax error. For more information about this error, please see http://datatables.net/tn/7
I have tables containing a lot of data and when I loaded in the way I have always used I waited a long time to see the table loaded, so I tried to do it in another way

now with the table I'm trying to make it load in a tenth of a second, whereas before it took almost a minute to load
Results 1 to 10 of 119,888 items

rf1234 · July 2023

If you do need server-side processing. then things get complicated since the filtering is done at the server-side, but the data at the client-side is translated.

Just to share my experience: I had to use server side for one page because it also has full-text search in very large attached documents which is hard to do client side. I have two languages, English and German. Even simple things like numbers rendering so that they can be found in an SQL view can get really, really complicated!

To be able to find a German formatted number in an SQL view you need to do this for example. Look at annual_cost_income_german versus annual_cost_income_english

CONCAT(REPLACE(LEFT( FORMAT(a.annual_cost_income, 2), POSITION('.' IN FORMAT(a.annual_cost_income, 2)) - 1 ), ',', '.'), ',', SUBSTR(FORMAT(a.annual_cost_income, 2), POSITION('.' IN FORMAT(a.annual_cost_income, 2)) + 1))
                                                AS annual_cost_income_german,
       FORMAT(a.annual_cost_income, 2)          AS annual_cost_income_english,

Why am I doing this? Server side search searches database values! So your database needs to return the formatted values. Otherwise they won't be found. Alternatively you can manipulate the user's search entries client side and send unformatted values to the server that are identical with database fields.

allan · July 2023

No question - server-side processing it is with that many rows.

Let's work on getting the table to display correctly - we can come back to orthogonal data with SearchPanes.

return view('tickets.index');

That generates JSON does it? Can you show me that template?

Allan

cmplus · July 2023

i'm working with laravel, that's the blade file where i put the code to generate the page with the table

cmplus · July 2023

Maybe it's not working because I'm using yajra ? should I get the editor to not have these problems? I followed a guide for creating serverside tables with yajra for laravel CRUD but I really don't understand why I can't get searchPanes and the translation of the values in the database to work.

Controller

use App\Models\Ticket;
use Illuminate\Http\Request;
use Yajra\DataTables\Facades\DataTables;
use Illuminate\Support\Facades\Session;

    public function index(Request $request)
    {
        if ($request->ajax()) {
            $data = Ticket::select('*');
            return DataTables::of($data)
                ->addColumn('action', function($row){
                    $btn  = '<a href="'.route('tickets.show', $row->id).'"><i class="ti-eye"></i></a>
                            <a href="'.route('tickets.edit', $row->id).'"><i class="ti-pencil"></i></a>
                            <a href="'.route('tickets.create', $row->id).'"><i class="icon-plus"></i></a>';
                    return $btn;
                })
                ->rawColumns(['action'])
                ->make(true);
        }
        return view('tickets.index');
    }

index.blade.php

<table id="" class="display table-striped table-bordered" cellspacing="0" width="100%">
                                    <thead>
                                        <tr>
                                            <th>Tickets</th>
                                            <th>Stato</th>
                                            <th>Orario</th>
                                            <th>Priorita</th>
                                            <th>Descrizione</th>
                                            <th>Categoria</th>
                                            <th>Azioni</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                    </tbody>
                                    <tfoot>
                                        <tr>
                                            <th>Tickets</th>
                                            <th>Stato</th>
                                            <th>Orario</th>
                                            <th>Priorita</th>
                                            <th>Descrizione</th>
                                            <th>Categoria</th>
                                            <th>Azioni</th>
                                        </tr>
                                    </tfoot>
                                </table>

        @push('scripts')
            <script type="text/javascript">
                $(document).ready(function() {
                    var table = $('table.display').DataTable({
                        dom: '<PBfrt<t>lip>',
                        searchPanes:{
                            cascadePanes: true,
                            initCollapsed: true,
                            show: true,
                            dtOpts:{
                                dom: 'tp',
                                paging: 'true',
                                pagingType: 'simple',
                                searching: true
                            }
                        },
                        language: {
                            url: "//cdn.datatables.net/plug-ins/1.13.5/i18n/it-IT.json"
                        },
                        buttons:[
                            {
                                extend:     'copyHtml5',
                                text:       'Copia',
                                titleAttr:  'Copia',
                                className:  'btn'
                            }, 
                            {
                                extend:     'csvHtml5',
                                text:      'CSV',
                                titleAttr: 'CSV',
                                className: 'btn '
                            }, 
                            {
                                extend:    'excelHtml5',
                                text:      'Excel',
                                titleAttr: 'Excel',
                                className: 'btn '
                            }, 
                            {
                                extend:    'pdfHtml5',
                                orientation: 'landscape',
                                pageSize: 'LEGAL',
                                text:      'PDF',
                                titleAttr: 'PDF',
                                className: 'btn '
                            }, 
                            {
                                extend:    'print',
                                text:      'Stampa',
                                titleAttr: 'Stampa',
                                className: 'btn '
                            },
                            { 
                                extend: "colvis",
                                postfixButtons: ["colvisRestore"],
                            }
                        ],
                        deferRender: true,
                        responsive: true,
                        lengthChange: true,
                        orderCellsTop: true,
                        fixedHeader: true,
                        select: true,
                        processing: true,
                        serverSide: true,
                        stateSave: true,
                        ajax: '{{ route('tickets.index') }}',
                        columns: [
                            {data: 'id', name: 'id',
                                searchPanes: {
                                show: true
                                }
                            },
                            {data: 'stato', name: 'stato',
                                searchPanes: {
                                show: true
                                }
                            },
                            {data: 'orario', name: 'orario',
                                searchPanes: {
                                show: true
                                }
                            },
                            {data: 'priorita', name: 'priorita',
                                searchPanes: {
                                show: true
                                }
                            },
                            {data: 'descrizione', name: 'descrizione',
                                searchPanes: {
                                show: true
                                }
                            },
                            {data: 'categoria', name: 'categoria',
                                searchPanes: {
                                show: true
                                }
                             },
                            {data: 'action', name: 'Azioni', orderable: false, searchable: false,
                                searchPanes: {
                                    show: false
                                }
                            },
                        ],
                        columnDefs: [
                            {
                                target: 2,
                                render: DataTable.render.date(),
                                target: 5,
                                render: DataTable.render.ellipsis( 17, true ),
                                searchPanes: {
                                    show: true
                                },
                                targets: '_all',
                            }
                        ]
                    });
                });
            </script>
        @endpush

allan · July 2023

I'd suggest forgetting SearchPanes for the moment, until we get the main table working with translations. Yajra will work with Editor although is isn't developed or supported by myself.

What we need to know is where, in the Yajra sequence of code we can do the translations.

If it were the Editor libraries, you would use the getFormatter() method for each field. Yajra might has something like that - you'd need to ask them.

Allan

cmplus · July 2023

If I take the editor developed by you, the translation and everything else should work fine right? I've been going crazy for days to find a solution but I can't, the tables I have are very full and I have to find a solution to load them quickly, I don't understand why I see the data loaded in the table and it is also loaded immediately, I just can't translate the entries in the database as I did before and I can't get the to work SearchPanes which interests me a lot to have filters with totals loaded and ready to use

cmplus · July 2023

I bought the editor but I don't know what I have to do to insert it in the table I have, what should I change to use the editor and see if searchPanes and everything else finally works?

allan · July 2023

So the Editor PHP libraries are actually open source under the MIT license. You don't need an Editor license to use them. I can refund the purchase if you like (or if you want to use Editor - carry on!).

If I take the editor developed by you, the translation and everything else should work fine right?

Kind of - there is still the extra step needed to do the translation via a formatter, and in the case of SearchPanes with a label renderer, but yes, it will be possible to get it working.

What I would suggest, to get the DataTable populated via our libraries is to (1) use Generator to create the basic PHP for you (click the PHP button at the end of the page).

You'll also want to make sure your DataTable is configured to request the data via POST:

ajax: {
  url: '{{ route('tickets.index') }}',
  type: 'post'
},

That way the ->process($_POST) call in the PHP that is created will be able to see the server-side processing request parameters.

Do that first - make sure you can get the untranslated data into the DataTable.

(2) Next, use the get formatter to call your translation method for each value that is being retrieved. That should get you the basic DataTable + translation working.

(3) SearchPanes will be the next step. For that you use the label method of the SearchPaneOptions object. Again you would run it through the translation method.

SearchPanes will get a value for filtering and a label for display - so while your end user will see the translated value, the filtering will happen on the untranslated value in the database.

Do those three steps individually, rather than all at one time. The libraries are different from what you've used, so it might take a little while to get used to them - Generator should help with a bit of a jump start.

Regards,
Allan

p.s. I'm travelling for the next week on holiday, so while I will be responding in the forum here, I might not always be as quick as I'd like to be I'm afraid. I'll typically do two "sessions" of support replies per day.

cmplus · July 2023

I don't understand why I can't get searchPanes to work even with the purchased editor, I've been trying for days, how can I use the code that is generated by the generator with laravel? what should i put in the controller? currently I see the table loaded with

ajax: '{{ route('tickets.index') }}',
dataType:"json",

but searchPanes is always empty, I've tried different ways of using the code generated by the generator but I don't see the results. Does anyone have this problem that can help me fix it?

allan · July 2023

I don't know if Yajra works with SearchPanes and server-side processing. I don't recall having seen that before, but I might have just missed it. It seems likely that is the issue though.

It sounds like you are still on step 1 to get the DataTable to display when using the Editor PHP libraries? The Editor PHP code needs to be installed alongside Laravel. Inside the controller would you call the Editor class basically the same has here (just move it into a controller).

But - instead of using ->json() use ->data() to get the JSON, and then return that in whatever way Laravel expects - e.g. something like:

public function index(Request $request)
{
    // todo - get $db

    $data = Editor::inst( $db, 'staff' )
        ->fields(
            Field::inst( 'first_name' ),
            Field::inst( 'last_name' ),
            Field::inst( 'position' ),
            Field::inst( 'email' ),
            Field::inst( 'office' )
        )
        ->process( $_POST )
        ->data();

    return json($data); // no idea if this is right
}

Note that $db still needs to be defined. Do you have a PDO connection available to the database? You can do:

$db = new Database([
  'type' => 'MySQL',
  'pdo' => $pdoResource
]);

Allan

cmplus · July 2023

I still can't solve the empty searchPanes problem, now I also have the new, edit, remove buttons, but they don't work and I still haven't figured out how to use the editor. I don't understand what it's for. This thing is really complicated, because searchPanes stopped working when I started using serverside, has anyone managed to use searchPanes with serverside table loading?

allan · July 2023

Yes, it is possible - this example shows how it can be done. You need to use the SearchPanesOptions class for the field(s) that you want to be included in the search pane panels. It isn't as simple as just adding searchPanes: true unfortunately.

Did you manage to get steps 1 and 2 from my post above working with the Editor PHP libraries? They are prerequisites to getting SearchPanes working as well. Can you show me the code that you are using for it?

Allan

cmplus · July 2023

right now that I view the data in the database I have this code

index.blade.php

@push('scripts')
    <script type="text/javascript">
        const editor = new DataTable.Editor({
            ajax: '{{ route('tickets.index') }}',
            fields: [
                {label: 'Ticket', name: 'id'},
                {label: 'Stato', name:'stato'},
                {label: 'Segnalazione', name: 'segnalazione'},
                {label: 'Orario', name: 'orario'},
                {label: 'Priorita', name: 'priorita'},
                {label: 'Chiusura', name: 'chiusura'},
                {label: 'Descrizione', name: 'descrizione'},
                {label: 'Categoria', name: 'categoria'},
            ],
            table: 'table.display'
        });
        new DataTable('table.display', {
            language: {
                url: "//cdn.datatables.net/plug-ins/1.13.5/i18n/it-IT.json"
            },
            ajax:'{{ route('tickets.index') }}',
            dataType:"json",
            type: "POST",
                
            buttons: [
                { extend: 'create', editor },
                { extend: 'edit', editor },
                { extend: 'remove', editor },
                { extend: 'copyHtml5', text: 'Copia', titleAttr: 'Copia', className: 'btn'}, 
                { extend: 'csvHtml5', text: 'CSV', titleAttr: 'CSV', className: 'btn'}, 
                { extend: 'excelHtml5',text: 'Excel',titleAttr: 'Excel',className: 'btn'}, 
                { extend: 'pdfHtml5',orientation: 'landscape',pageSize: 'LEGAL',text: 'PDF',titleAttr: 'PDF', className: 'btn'}, 
                { extend: 'print',text: 'Stampa',titleAttr: 'Stampa',className: 'btn'},
                { extend: "colvis", postfixButtons: ["colvisRestore"]},
            ],
            columns: [
                {data: 'id', name: 'id'},
                {data: 'stato', name: 'stato'},
                {data: 'segnalazione', name: 'segnalazione'},
                {data: 'orario', name: 'orario'},
                {data: 'priorita', name: 'priorita'},
                {data: 'chiusura', name: 'chiusura'},
                {data: 'descrizione', name: 'descrizione'},
                {data: 'categoria', name: 'categoria'},
            ],
            dom: '<PBfrt<t>lip>',
            deferRender: true,
            responsive: true,
            lengthChange: true,
            orderCellsTop: true,
            fixedHeader: true,
            select: true,
            processing: true,
            serverSide: true,
            stateSave: true,
            searchPanes:{
                cascadePanes: true,
                initCollapsed: true,
                show: true,
                dtOpts:{
                    dom: 'tp',
                    paging: 'true',
                    pagingType: 'simple',
                    searching: true
                }
            },
            columnDefs: [
                {
                    target: 2,
                    render: DataTable.render.date(),
                    target: 5,
                    render: DataTable.render.ellipsis( 17, true ),
                    searchPanes: {
                        show: true
                    },
                    targets: '_all',
                }
            ],
        });
    </script>
@endpush

controller

    public function index(Request $request)
    {

        if ($request->ajax()) {
            $data = Ticket::select('*');
            return DataTables::of($data)
                ->addIndexColumn()
                ->addColumn('action', function($row){
                    $btn  = '<a href="'.route('tickets.show', $row->id).'"><i class="ti-eye"></i></a>
                            <a href="'.route('tickets.edit', $row->id).'"><i class="ti-pencil"></i></a>
                            <a href="'.route('tickets.create', $row->id).'"><i class="icon-plus"></i></a>';
                    return $btn;
                })
                ->rawColumns(['action'])
                ->make(true);
        }

        return view('tickets.index');
    }

if I try to use the code you suggested I get this error that I don't understand what it means because I don't have any variable called "user" in use in the code

ErrorException

Undefined array key "user"

controller

    public function index(Request $request)
    {
        $db = new Database([
            'type' => 'Mysql'
        ]);

        $data = Editor::inst($db, 'tickets')
        ->fields(
            Field::inst('id'),
            Field::inst('stato'),
            Field::inst('segnalazione'),
            Field::inst('orario'),
            Field::inst('priorita'),
            Field::inst('chiusura'),
            Field::inst('descrizione'),
            Field::inst('categoria'),
            Field::inst('riportato_da'),
            Field::inst('luogo'),
            Field::inst('contratto_assegnato')
        )
        ->process($_POST)
        ->data();

        return view('tickets.index', compact('data'));
    }

cmplus · July 2023

If I change like this I get this error

Call to undefined method Illuminate\Database\Eloquent\Builder::rollback()

    public function index(Request $request)
    {
        $db = Ticket::select('*');

        $data = Editor::inst($db)
        ->fields(
            Field::inst('id')->searchPaneOptions( SearchPaneOptions::inst()),
            Field::inst('stato')->searchPaneOptions( SearchPaneOptions::inst()),
            Field::inst('segnalazione')->searchPaneOptions( SearchPaneOptions::inst()),
            Field::inst('orario')->searchPaneOptions( SearchPaneOptions::inst()),
            Field::inst('priorita')->searchPaneOptions( SearchPaneOptions::inst()),
            Field::inst('chiusura')->searchPaneOptions( SearchPaneOptions::inst()),
            Field::inst('descrizione')->searchPaneOptions( SearchPaneOptions::inst()),
            Field::inst('categoria')->searchPaneOptions( SearchPaneOptions::inst())
        )
        ->process($_POST)
        ->json();

        return view('tickets.index', compact('data'));
    }

if I remove the process

DataTables warning: table id=DataTables_Table_0 - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

allan · July 2023

public function index(Request $request)
{
    $db = new Database([
        'type' => 'Mysql'
    ]);
 
    $data = Editor::inst($db, 'tickets')
    ->fields(
        Field::inst('id'),
        Field::inst('stato'),
        Field::inst('segnalazione'),
        Field::inst('orario'),
        Field::inst('priorita'),
        Field::inst('chiusura'),
        Field::inst('descrizione'),
        Field::inst('categoria'),
        Field::inst('riportato_da'),
        Field::inst('luogo'),
        Field::inst('contratto_assegnato')
    )
    ->process($_POST)
    ->data();
 
    return view('tickets.index', compact('data'));
}

This one is closest, but you missed the bit about needing the PDO resource:

$db = new Database([
  'type' => 'MySQL',
  'pdo' => $pdoResource
]);

Otherwise there is no connection made to the database (which is why it is complaining about not having a $user variable, because it wants a user, pass and host (or an existing connection).

My understanding from the Laravel documentation is that DB::connection()->getPdo() will get the PDO resource, so you would need to use that.

Also the $data variable contains the data to be returned as JSON, so you could just return that rather than running it through a view:

public function index(Request $request)
{
    $db = new Database([
        'type' => 'Mysql',
        'pdo' => DB::connection()->getPdo()
    ]);
 
    $data = Editor::inst($db, 'tickets')
    ->fields(
        Field::inst('id'),
        Field::inst('stato'),
        Field::inst('segnalazione'),
        Field::inst('orario'),
        Field::inst('priorita'),
        Field::inst('chiusura'),
        Field::inst('descrizione'),
        Field::inst('categoria'),
        Field::inst('riportato_da'),
        Field::inst('luogo'),
        Field::inst('contratto_assegnato')
    )
    ->process($_POST)
    ->data();
 
    return $data;
}

Assuming that works, you would then add the get formatters to do the translation and after that is working add the SearchPanesOptions class to the required fields.

Allan

cmplus · July 2023

thanks for your patience and support, adding the 'pdo' => DB::connection()->getPdo() now if i leave the return $data; I display the data like this {"data":[{"DT_RowId":"row_1","id":1 in the page, if I remove the return $data; I get this message DataTables warning: table id=DataTables_Table_0 - Ajax error. For more information about this error, please see http://datatables.net/tn/7

F12 console

{message: "The id field is required.", errors: {id: ["The id field is required."]}}
errors
:
{id: ["The id field is required."]}
id
:
["The id field is required."]
0
:
"The id field is required."
message
:
"The id field is required."

I'm not understanding anything anymore, all this seems absurd to me

allan · July 2023

I display the data like this {"data":[{"DT_RowId":"row_1","id":1 in the page,

Okay - that is what you want for the JSON for the table, so I think what you need now is to split the JSON data request from the page request. I hadn't realised they were overlapping.

In other words, you need two controllers:

A view to show the HTML for the page and the basic table construct (just the table and thead with columns defined. Don't have it output the data.
A route for the data (which is what we've been working on).

So perhaps something like:

public function index(Request $request)
{ 
    return view('tickets.index');
}

public function data(Request $request)
{
    $db = new Database([
        'type' => 'Mysql',
        'pdo' => DB::connection()->getPdo()
    ]);
  
    $data = Editor::inst($db, 'tickets')
    ->fields(
        Field::inst('id'),
        Field::inst('stato'),
        Field::inst('segnalazione'),
        Field::inst('orario'),
        Field::inst('priorita'),
        Field::inst('chiusura'),
        Field::inst('descrizione'),
        Field::inst('categoria'),
        Field::inst('riportato_da'),
        Field::inst('luogo'),
        Field::inst('contratto_assegnato')
    )
    ->process($_POST)
    ->data();
  
    return $data;
}

And update the ajax option for DataTables to point at the data controller / route.

Allan

cmplus · July 2023

Thanks so much @allan,
I've now managed to load the data into the table like you suggested, so now I'm ready to try and get searchPanes to finally work

cmplus · July 2023

Almost finished but not quite, I entered the instructions for searchpanel but now it gives me this error, I don't understand why it sees duplicates as an error?
in the description it is normal that there may be duplicates

```
public function data(Request $request){

    $db = new Database([
        'type' => 'Mysql',
        'pdo' => DB::connection()->getPdo()
    ]);

    $data = Editor::inst($db, 'tickets')
    ->fields(
        Field::inst('id')->searchPaneOptions( SearchPaneOptions::inst()),
        Field::inst('stato')->searchPaneOptions( SearchPaneOptions::inst()),
        Field::inst('segnalazione')->searchPaneOptions( SearchPaneOptions::inst()),
        Field::inst('orario')->searchPaneOptions( SearchPaneOptions::inst()),
        Field::inst('priorita')->searchPaneOptions( SearchPaneOptions::inst()),
        Field::inst('chiusura')->searchPaneOptions( SearchPaneOptions::inst()),
        Field::inst('descrizione')->searchPaneOptions( SearchPaneOptions::inst()),
        Field::inst('categoria')->searchPaneOptions( SearchPaneOptions::inst()),
    )
    ->process($_POST)
    ->data();

    return $data;
}

```

DataTables warning: table id=DataTables_Table_0 - An SQL error occurred: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'cargo, torre E, bagno uomo, piano T' for key '/var/tmp/mysql/#sql26caf8_4db081_a.<group_key>'

allan · July 2023

I don't know I'm afraid! Could you add ->debug(true) just before the ->process($_POST) call please? Then show me the JSON returned by the server when the Ajax request is made. It will include the SQL that is being generated and executed, so I should be able to get better handle on the error.

Thanks,
Allan

cmplus · July 2023

The error refers to a column that does not contain unique data, how do you make it still see the column even if it does not contain unique data?

16
: 
{,…}
bindings
: 
[]
query
: 
"SELECT DISTINCT  `descrizione` as 'label', `descrizione` as 'value', COUNT(*) as total FROM  `tickets`  GROUP BY `descrizione`"
error
: 
"An SQL error occurred: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry  for key '/var/tmp/mysql/#sql26caf8_4f2dc6_a.<group_key>'"
fieldErrors
: 
[]
ipOpts
: 
[]

DataTables warning: table id=DataTables_Table_0 - An SQL error occurred: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry for key '/var/tmp/mysql/#sql26caf8_4f2afc_a.<group_key>'

allan · July 2023

descrizione doesn't need to contain unique data - indeed, that's kind of the point of SearchPanes - to let you see the grouping and then filter by that.

As a little example, consider this page and the First name column. It uses:

        Field::inst( 'users.first_name' )
            ->searchPaneOptions(SearchPaneOptions::inst()
        ),

Edit one or more of the rows so there are duplicate names - e.g. two (or more) with "Alexa". The query runs successfully with that duplicate data.

What version of MySQL are you using here? And is there enough disk space for that query to run? I've seen mention that the error there can happen if MySQL is low on temp disk space. What happens if you run it directly against the database (e.g. phpMyAdmin or similar).

Sorry - I know this has been rather torturous for you, unfortunately, I've never seen that error before (at least that I can recall), and it might require a little investigation to figure out what is going wrong.

If you comment out SearchPanes on that column, do the panes for the other columns load, or is it the same for all of them?

Allan

cmplus · July 2023

The strange thing is that now the query loads me twice and it takes a very long time to load it, if I don't use searchPaneOptions it loads in an instant instead with searchPaneOptions it takes forever to load, I've removed the description column for now to see if it loads and loading is very slow, if I try with phpmyadmin it loads in an instant like when I do it without searchPaneOptions. yes nothing like this has ever happened to me either and I still can't find the solution about it, if it has never happened to you either then I managed to create something absurd

allan · July 2023

cascadePanes: true, has a very significant impact on performance. You could try removing it and see if that helps a bit.

Using phpMyAdmin directly isn't going to do the same thing, unless you run the same queries as SearchPanes is generating (you'll see them in the debug return of the JSON if you want to try running them.

It's the grouping count aggregate that is used for the cascade that is absolutely brutal on performance. It could probably be optimised with indexes on the target columns, but I don't know how much it would help and if it would be enough.

Allan

Automatic translation of database values

Automatic translation of database values

Answers

Howdy, Stranger!

Categories

DataTables

Automatic translation of database values

Automatic translation of database values

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables