Duplicate records

Jensen010

Hi, I have a bizarre issue, and I can't figure out the culprit.

I finally have everything displaying as we need it in our tables, but in our main table I am seeing some duplicate records. Except they aren't true duplicates...

These records are a second instance of themselves. In other words, I have somehow told datatables "Display this record twice." I have confirmed this is the case because any change to one of the cells in a duplicated row is reflected in the other row as well. Also, I've checked on the DB side, and no actual duplicates exist data-wise. Has anyone encountered anything like this? I'm stumped.

Here's my code, I left out the php file that generates the JSON data due to length, but I suppose the problem could be there too:

Editor Instance:

//Load Editor Overlay - Assets
    var assetsEditor = new $.fn.dataTable.Editor({
        ajax: { url: srvRoot+"/wp-content/plugins/datatables/php/table-data/assets-table.php", type: "POST" },
        table: '#assets-table',
        fields: [ 
            //Left Side
            { label: "Owner",             name: "Owner",             className: "col-sm-5 d-inline-block align-top", type: "select"                       },
            { label: "Barcode Number",    name: "Barcode",           className: "col-sm-5 d-inline-block align-top", type: "text"                         },
            { label: "Serial Number",     name: "SerialNum",         className: "col-sm-5 d-inline-block align-top", type: "text"                         },
            { label: "Order Number",      name: "OrderNum",          className: "col-sm-5 d-inline-block align-top", type: "select"                       },
            { label: "Model",             name: "Model",             className: "col-sm-5 d-inline-block align-top", type: "select"                       },
            { label: "Manufacturer",      name: "Manufacturer",      className: "col-sm-5 d-inline-block align-top", type: "select"                       },
            { label: "Device Type",       name: "Device_Type",       className: "col-sm-5 d-inline-block align-top", type: "select"                       },
            { label: "Item Status",       name: "Item_Status",       className: "col-sm-5 d-inline-block align-top", type: "select", options: itemStatus  },
            //Right Side
            { label: "Equipment Type",    name: "Equipment_Type",    className: "col-sm-5 d-inline-block align-top", type: "select"                       },
            { label: "Assigned Level",    name: "AssignedLevel",     className: "col-sm-5 d-inline-block align-top", type: "select"                       },
            { label: "Acquired Date",     name: "Acquired_Date",     className: "col-sm-5 d-inline-block align-top", type: "datetime", format: 'M/D/YYYY' },
            { label: "Issued Date",       name: "Issued_Date",       className: "col-sm-5 d-inline-block align-top", type: "datetime", format: 'M/D/YYYY' },
            { label: "Last Seen Date",    name: "Last_Seen_Date",    className: "col-sm-5 d-inline-block align-top", type: "datetime", format: 'M/D/YYYY' },
            { label: "Warranty Exp Date", name: "Warranty_Exp_Date", className: "col-sm-5 d-inline-block align-top", type: "datetime", format: 'M/D/YYYY' },
            { label: "Surplus Date",      name: "Surplus_Date",      className: "col-sm-5 d-inline-block align-top", type: "datetime", format: 'M/D/YYYY' },
            { label: "Comments",          name: "Comments",          className: "col-sm-5 d-inline-block align-top", type: "textarea"                     }
        ]
    });

Datatables Initialization

//Load Client Side Table - Assets
    assetsDataTable = $('#assets-table').DataTable({
        serverSide: true,
        ajax: { url: srvRoot+"/wp-content/plugins/datatables/php/table-data/assets-table.php", type: "POST" },
        dom: "<'table-functions-top row col-sm-12 ml-0 pt-3 pl-4' <'#assetsLength.col-sm-2.lengthBox'l><'#assetsButtons.col-sm-8 buttonBar float-right'B>><'row col-sm-12 bottom-row pt-4 pl-5 m-0' <'#assetsPager.col-sm-5 float-left'p><'#assetsInfo.col-sm-4 float-right'i><'#assetsSearch.col-sm-3'f>><'#assetsProcessing'r>t",
        scrollX: "1720px",
        scrollY: "550px",
        columnDefs: [ /*Row Height Control*/{ className: "rowHeight", targets: "_all"} ],
        columns: [
            //Special Columns
            //Hidden Rows
            { data: "Alias",     "defaultContent": "<strong><i>Not set</i></strong>", "width": "125px", "targets": 0, visible: false },
            { data: "Last_Name", "defaultContent": "<strong><i>Not set</i></strong>", "width": "125px", "targets": 1, visible: false },

            //Checkbox
            { data: null, "defaultContent": '', searchable: false, orderable: false, className: 'select-checkbox CheckBoxRow', "width": "60px", "targets": 2 },

            //Owner Column
            { data: "Owner", "defaultContent": "<strong><i>Not set</i></strong>", orderable: false, editField: "Owner",
              render: function(data, type, row, meta) {
                // console.log(row);
              if(row.Alias === null && row.Last_Name === null){return '<strong><i>No owner assigned</i></strong>';}
              else if(row.Alias === null && row.Last_Name === ''){return '<strong><i>Not set</i></strong>';}
              else if (row.Alias === null) {return '<strong><i>(No first name specified) </i></strong>'+row.Last_Name;}
              else {return row.Alias+' '+row.Last_Name;}
            }, "width": "150px", "targets": 3, searchable: false                                                                                           },

            //Normal Columns
            //Barcode Column
            { data: "Barcode",           "defaultContent": "<strong><i>Not set</i></strong>", "width": "150px", "targets": 4                               },
            { data: "SerialNum",         "defaultContent": "<strong><i>Not set</i></strong>", "width": "150px", "targets": 5                               },
            { data: "Model",             "defaultContent": "<strong><i>Not set</i></strong>", "width": "150px", "targets": 6                               },
            { data: "Manufacturer",      "defaultContent": "<strong><i>Not set</i></strong>", "width": "150px", "targets": 7                               },
            { data: "Device_Type",       "defaultContent": "<strong><i>Not set</i></strong>", "width": "150px", "targets": 8                               },
            { data: "Last_Seen_Date",    "defaultContent": "<strong><i>Not set</i></strong>", "width": "150px", "targets": 9, className: "datePicker"      },
            { data: "OrderNum",          "defaultContent": "<strong><i>Not set</i></strong>", "width": "150px", "targets": 10                              },
            { data: "Item_Status",       "defaultContent": "<strong><i>Not set</i></strong>", "width": "150px", "targets": 11                              },
            { data: "Equipment_Type",    "defaultContent": "<strong><i>Not set</i></strong>", "width": "150px", "targets": 12                              },
            { data: "Comments",          "defaultContent": "<strong><i>Not set</i></strong>", "width": "150px", "targets": 13, className: "datePicker"     },
            { data: "AssignedLevel",     "defaultContent": "<strong><i>Not set</i></strong>", "width": "150px", "targets": 14                              },
            { data: "Acquired_Date",     "defaultContent": "<strong><i>Not set</i></strong>", "width": "150px", "targets": 15, className: "datePicker"     },
            { data: "Issued_Date",       "defaultContent": "<strong><i>Not set</i></strong>", "width": "150px", "targets": 16, className: "datePicker"     },
            { data: "Warranty_Exp_Date", "defaultContent": "<strong><i>Not set</i></strong>", "width": "150px", "targets": 17, className: "datePicker"     },
            { data: "Surplus_Date",      "defaultContent": "<strong><i>Not set</i></strong>", "width": "150px", "targets": 18, className: "datePicker"     }
        ],
        fixedColumns: { leftColumns: 2 },
        order:        [ 9, 'desc' ],
        keys:         { columns: ':not(:first-child)', editor:  assetsEditor },
        select:       { style: 'os', selector: 'td.select-checkbox', blurable: true },
        pageLength:   50,
        lengthMenu:   [ [50, 100, 150, -1], [50, 100, 150, "All"] ],
        orderMulti:   true,
        deferRender: true,
        processing: true,
        pagingType: "full_numbers",
        autoFill: true,
        colReorder: true
    }).on( 'dblclick', 'tbody td', function() {
        var initialVal = $(this).val();
        console.log(initialVal);
        //Enable inline Editing
        assetsEditor.inline(this, {
            onBlur:       'submit',
            onReturn:     'submit',
            onEsc:        'blur',
            onFieldError: 'none',
            submit:       'changed',
            drawType:     'full-hold'
        });
    });

    //Draw Assets Table
    assetsDataTable.draw();

colin

Hi @Jensen010 ,

Nothing leaps out as being obviously wrong. Is it definitely a case of 'being displayed twice', as opposed to 'being sent twice' in the Ajax payload?

Cheers,

Colin

Jensen010

I'm just not sure, I had thought of that but nothing jumps out at me from there either:

PHP code (generating JSON)
```php
<?php

// //Globals
global $_POST;

// //Local Vars
include("....\lib\DataTables\Editor-PHP-1.7.2\php\DataTables.php");
include("....\lib\DataTables\Editor-PHP-1.7.2\php\Editor\Editor.php");

//Load dt Editor Modules
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate;

Editor::inst( $db, 'Assets')
->fields(
// Field::inst( 'Assets.ID', 'ID' )->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Contacts.Alias', 'Alias' )->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Contacts.Last_Name', 'Last_Name' )->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Assets.Owner', 'Owner' )->setFormatter( Format::ifEmpty( null ) )
->options( Options::inst()
->table( 'Contacts' )
->value( 'Contacts.ID')
->label( array( 'Contacts.Alias', 'Contacts.Last_Name' ))
->order( 'Contacts.Alias asc')
),
Field::inst( 'Assets.Barcode', 'Barcode' )->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Assets.SerialNum', 'SerialNum' )->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Assets.Model', 'Model' )->setFormatter( Format::ifEmpty( null ) )
->options( Options::inst()
->table( 'Models' )
->value( 'Models.Model' )
->label( 'Models.Model' )
->order( 'Models.Model asc')
),
Field::inst( 'Models.Manufacturer', 'Manufacturer' )->setFormatter( Format::ifEmpty( null ) )
->options( Options::inst()
->table( 'Models' )
->value( 'Models.Manufacturer' )
->label( 'Models.Manufacturer' )
->order( 'Models.Manufacturer asc')
),
Field::inst( 'Models.Device_Type', 'Device_Type' )->setFormatter( Format::ifEmpty( null ) )
->options( Options::inst()
->table( 'Models' )
->value( 'Models.Device_Type' )
->label( 'Models.Device_Type' )
->order( 'Models.Device_Type asc')
),
Field::inst( 'Assets.Last_Seen_Date', 'Last_Seen_Date' )
->getFormatter( Format::dateSqlToFormat( 'm/d/Y' ) )
->setFormatter( Format::dateFormatToSql( 'm/d/Y' ) )
->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Assets.OrderNum', 'OrderNum' )->setFormatter( Format::ifEmpty( null ) )
->options( Options::inst()
->table( 'OrderNum_CTC' )
->value( 'OrderNum_CTC.OrderNum' )
->label( 'OrderNum_CTC.OrderNum' )
->order( 'OrderNum_CTC.OrderNum asc')
),
Field::inst( 'Assets.Item_Status', 'Item_Status' )->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Assets.Equipment_Type', 'Equipment_Type' )->setFormatter( Format::ifEmpty( null ) )
->options( Options::inst()
->table( 'Assets' )
->value( 'Assets.Equipment_Type' )
->label( 'Assets.Equipment_Type' )
->order( 'Assets.Equipment_Type asc')
),
Field::inst( 'Assets.Comments', 'Comments' )->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Assets.AssignedLevel', 'AssignedLevel' )->setFormatter( Format::ifEmpty( null ) )
->options( Options::inst()
->table( 'Assets' )
->value( 'Assets.AssignedLevel' )
->label( 'Assets.AssignedLevel' )
->order( 'Assets.AssignedLevel asc')
),
Field::inst( 'Assets.Acquired_Date', 'Acquired_Date' )
->getFormatter( Format::dateSqlToFormat( 'm/d/Y' ) )
->setFormatter( Format::dateFormatToSql( 'm/d/Y' ) )
->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Assets.Issued_Date', 'Issued_Date' )
->getFormatter( Format::dateSqlToFormat( 'm/d/Y' ) )
->setFormatter( Format::dateFormatToSql( 'm/d/Y' ) )
->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Assets.Warranty_Exp_Date', 'Warranty_Exp_Date' )
->getFormatter( Format::dateSqlToFormat( 'm/d/Y' ) )
->setFormatter( Format::dateFormatToSql( 'm/d/Y' ) )
->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Assets.Surplus_Date', 'Surplus_Date' )
->getFormatter( Format::dateSqlToFormat( 'm/d/Y' ) )
->setFormatter( Format::dateFormatToSql( 'm/d/Y' ) )
->setFormatter( Format::ifEmpty( null ) )
)
->leftJoin( 'Contacts', 'Contacts.ID', '=', 'Assets.Owner' )
->leftJoin( 'Models', 'Models.Model', '=', 'Assets.Model' )
->leftJoin( 'OrderNum_CTC', 'OrderNum_CTC.OrderNum', '=', 'Assets.OrderNum' )
->process( $_POST )
->json();

<?php > ``` ?>

colin

A good thing to do would be to check in the Browsers development tools, open the Network tab and check the packets being received. You can tell from there fairly easily if the browser is being sent double records.

C

Jensen010

Hadn't thought of that, and well what do you know...

Every table initialization file that I have is being sent twice from the server <facepalm>
That gives me a direction, thank you

Jensen010

After more investigation, I'm not entirely convinced this is the problem. I have a .draw() at the end of all my tables, which was causing the 2nd call to the PHP file above. However, I have killed that 15 different ways and I still have the problem. Additionally, I noticed that everything I type in the search box generates another call to that file as well, so this seems to be some sort of default functionality I'm attempting to mess with.

kthorngren

You have serverSide enabled which will generate an AJAX request, for each draw, to get the data required for the page. If you have a page length of 10 then server side processing expects a response with only those 10 rows to be displayed. More info about server side processing is here:
https://datatables.net/manual/server-side

Maybe you don't need it enabled.

Kevin

colin

Morning @Jensen010 ,

Another thing to try would be to remove the leftJoin on the OrderNum_CTC table - there aren't any fields being read from that table, and we've seen that cause duplicates before. Could you remove that and let us know if that worked, please?

Cheers,

Colin

Jensen010

Thanks @kthorngren for the suggestion, I do need SSP on though, since our tables are pretty large.

@colin Thank you! That appears to have been the issue. I was under the impression that I needed a join to generate table options, but I see now that's not the case. Thanks again!