Issues with DataTables Not Refreshing After Update or Insert

jarmoud

Link to test case: no link
Debugger code (debug.datatables.net): no error
Error messages shown: no message
Description of problem:

Hello everyone,

I'm experiencing an issue with DataTables on my web page. I have two DataTables, and while one refreshes correctly after an update, the other does not. Here is a brief overview of my setup:

I am using DataTables with Editor for inline editing.
Each table has its own AJAX configuration, Editor instance, and event handlers.
Both tables are set up similarly, but only one of them refreshes automatically after an update.
Here is the relevant part of my PHP code:

<?php

/*
 * Editor server script for DB table vehicle
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
include( "lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

$id = isset($_POST['id']) ? intval($_POST['id']) : 0;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'mileage' )
    ->where('mileage.vehicle_id', $id)
    ->field(
        Field::inst( 'mileage.id' ), //->set( false ),
        Field::inst( 'mileage.vehicle_id' ),
        Field::inst( 'mileage.mileage' )
            ->validator( Validate::numeric() ),
        Field::inst( 'mileage.dt_mileage' )
            ->validator( Validate::dateFormat( 'm/d/y' ) )
            ->getFormatter( Format::dateSqlToFormat( 'm/d/y' ) )
            ->setFormatter( Format::dateFormatToSql( 'm/d/y' ) ),
        Field::inst( 'mileage.action_by_id' )
            ->options( Options::inst()
                ->table( 'users' )
                ->value( 'id' )
                ->label( array('name', 'last_name') )
            ),
        Field::inst( 'users.name' )
            ->set( false ),
        Field::inst( 'users.last_name' )
            ->set( false )
    )
    ->leftJoin( 'users', 'users.id', '=', 'mileage.action_by_id' )
    ->debug(true)
    ->process( $_POST )
    ->json();

Here is the relevant part of my JavaScript code:

        var editorMileage = new DataTable.Editor( {
            ajax: 'php/table.vehicle_mileage.php',
            table: '#vehicle_mileage',
            fields: [
                { label: "Mileage:", name: "mileage.mileage" },
                { label: "Date:", name: "mileage.dt_mileage", type: "datetime", "format": "MM\/DD\/YY" },
                { label: "Action By:", name: "mileage.action_by_id", type: "select" },
                { label: "Vehicle ID:", name: "mileage.vehicle_id", type: "hidden" }
                /*{ label: "First Name:", name: "users.name", type: "readonly" },
                { label: "Last Name:", name: "users.last_name", type: "readonly" },
                { label: "Vehicle ID:", name: "mileage.vehicle_id", type: "readonly", def: vehicleId }*/
            ]
        });
        /*editorMileage.on('postCreate postEdit', function(e, json, data) {
            table.ajax.reload(null, false); // Reload the table data, keeping the current paging
        });*/
        //DEBUG
        editorMileage.on('submitComplete', function(e, json, data) {
            console.log('Table1 submit complete', json, data);
            //tableMileage.ajax.reload();
        });
        // Ajout du preSubmit pour définir vehicle_id lors de la création
        editorMileage.on('preSubmit', function (e, data, action) {
            $.each(data.data, function (key, values) {
                data.data[key]['type'] = 'mileage';
            });
            if (action === 'create') {
                data.data[0].mileage.vehicle_id = vehicleId;
            }
        });
        
        var tableMileage = new DataTable('#vehicle_mileage', {
            ajax: {
                url: 'php/table.vehicle_mileage.php', // URL de votre script PHP
                type: 'POST',
                data: function(d) {
                    d.id = vehicleId; // Ajoute l'ID dans les données POST
                },
                dataSrc: 'data'
            },
            columns: [
                { data: 'mileage.mileage', title: 'Mileage' },
                { data: 'mileage.dt_mileage', title: 'Date' },
                {
                    data: null,
                    render: function (data, type, row) {
                        // Combine the first and last names into a single table field
                        return data.users.name + ' ' + data.users.last_name;
                    }
                }
                //{ data: 'users.name', title: 'First Name' },
                //{ data: 'users.last_name', title: 'Last Name' }
            ],
            colReorder: true,
            layout: {
                topStart: {
                    buttons: [
                        { extend: 'create', editor: editorMileage },
                        { extend: 'edit', editor: editorMileage },
                        { extend: 'remove', editor: editorMileage },
                        {
                            extend: 'collection',
                            text: 'Export',
                            buttons: ['copy', 'excel', 'csv', 'pdf', 'print']
                        }
                    ]
                }
            },
            responsive: true,
            select: true
        });

Could someone help me identify potential reasons why tableMileage might not be refreshing automatically? Any guidance or suggestions would be greatly appreciated.

Thank you!

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

->where('mileage.vehicle_id', $id)

You don't appear to be sending an id parameter on submit for the Editor Ajax request. That results in an id of 0 being used, which in turn results in no records being found, and hence the error. At least that is what I assume is happening from the above.

I think you need to add d.id = vehicleId; // Ajoute l'ID dans les données POST in your preSubmit event handler. But, I'm not sure where vehicleId comes from, so I'm not 100% sure that is the right thing to do.

Allan

jarmoud

Thank you Allan.
vehicleId comes from this code :

var vehicleId = getParameterByName('id');

function getParameterByName(name) {
        var match = RegExp('[?&]' + name + '=([^&]*)').exec(window.location.search);
        return match && decodeURIComponent(match[1].replace(/\+/g, ' '));
    }

In the same file, I have this table that refreshes correctly:

var editorInsurer = new DataTable.Editor( {
            ajax: 'php/table.vehicle_insurer.php',
            table: '#vehicle_insurer',
            fields: [
                { label: "Vehicle ID:", name: "assignment_insurance.vehicle_id", type: "hidden" },
                { label: "Company:", name: "assignment_insurance.company_id", type: "select" },
                { label: "Company Name:", name: "company.name", type: "readonly" },
                { label: "Insurer:", name: "assignment_insurance.insurer_id", type: "select" },
                { label: "Insurer Name:", name: "insurer.name", type: "readonly" },
                { label: "Contract Number:", name: "insurer.contract_number", type: "readonly" },
                { label: "Insurance Start Date:", name: "assignment_insurance.dt_insurance", type: "datetime", "format": "MM\/DD\/YY" },
                {
                    label: 'Files:',
                    name: 'files[].id',
                    type: 'uploadMany',
                    display: function (fileId, counter) {                       
                        var file = editorInsurer.file('files', fileId);
                        return file ? '<a href="' + file.web_path + '" target="_blank">' + file.filename + '</a>' : '';
                    },
                    noFileText: 'No file'
                }
            ]
        } );
        editorInsurer.dependent('assignment_insurance.company_id', 'php/insurance.php');


        editorInsurer.on('preSubmit', function (e, data, action) {
            $.each(data.data, function (key, values) {
                data.data[key]['type'] = 'insurer';
            });
            if (action === 'create') {
                data.data[0].assignment_insurance.vehicle_id = vehicleId;
            }
        });


        var tableInsurer = new DataTable('#vehicle_insurer', {
            ajax: {
                url: 'php/table.vehicle_insurer.php', // 
                type: 'POST',
                data: function(d) {
                    d.id = vehicleId; // 
                },
                dataSrc: 'data'
            },
            columns: [
                { data: 'company.name', title: 'Company Name' },
                { data: 'insurer.name', title: 'Insurer Name' },
                { data: 'insurer.contract_number', title: 'Contract Number' },
                { data: 'assignment_insurance.dt_insurance', title: 'Insurance Start Date' },
                { data: 'insurer.dt_end', title: 'Insurance End Date' },
                {
                    data: 'files',
                    render: function (data, type, row) {
                        if (type === 'display') {
                            if (data && data.length > 0) {
                                return data.map(function (file) {
                                    return '<a href="' + file.web_path + '" target="_blank"><i class="fas fa-file-alt"></i> ' + file.filename + '</a>';
                                }).join(', ');
                            } else {
                                return 'No files';
                            }
                        }
                        return data;
                    }
                    , title: 'Documents'
                }               
            ],
            colReorder: true,
            layout: {
                topStart: {
                    buttons: [
                        { extend: 'create', editor: editorInsurer },
                        { extend: 'edit', editor: editorInsurer },
                        { extend: 'remove', editor: editorInsurer },
                        {
                            extend: 'collection',
                            text: 'Export',
                            buttons: ['copy', 'excel', 'csv', 'pdf', 'print']
                        }
                    ]
                }
            },
            responsive: true,
            select: true
        });

allan

Yup, so the id is being set for the DataTables ajam request, but not the Editor one, hence thewhere condition is wrong as I surmised before.

Have you tried adding theid to the Editor Ajax data?

Allan

jarmoud

Hello Allan,
The data is updating, but the row disappears from the table. You need to manually refresh to see it again. The second table doesn't show any anomalies, the PreSubmit script is identical for both tables.