Button to remove unique rows and display rows with duplicate column data

2008jackson

Greetings. I require to use a button to remove all unique rows returned through AJAX JSON and display rows with duplicate column data. With code below, im unable to pick the right rows using IndexOf.

Kindly assist to fix code below.

var table = $('#table_id').DataTable({
    dom: 'PBfrtip',
    "buttons": ['copy', 'excel', {
        text: 'Show duplicates',
        action: function(row, data, index) {
            var allData = table.column(2).data().toArray();
            if (allData.indexOf(data.Reg) == allData.lastIndexOf(data.Reg)) {
                $(row).css('display', 'none');
            }
        }
    }],
    "aaData": data.d.results,
    "aoColumns": [{
            "mData": "Created",
            "render": function(data, type, row) {
                data = moment(data).format('DD-MM-YYYY HH:mm');
                return data;
            }
        },
        {
            "mData": "Family"
        },
        {
            "mData": "Reg"
        },
        {
            "mData": "Date",
            "render": function(data, type, row) {
                data = moment(data).format('DD-MM-YYYY');
                return data;
            }
        },
        {
            "mData": "Number"
        }
    ]
});

colin

The second table here removes duplicate rows - you can add that code into your button,

Colin

2008jackson

Thanks Colin for the good start! Code below works to pull duplicate entries. However, i require to pull the initial entry for matching Reg and duplicate entry instead of pulling the duplicate entry only.

Please advice.

        var table = $('#table_id').DataTable({    
            dom: 'Bfrtip',
            initComplete: function() {
                var names = [];
                var api = this.api();
                var duplicate = [];
                var avail = [];
                api.rows().every(function(rowIdx, tableLoop, rowLoop) {
                    var data = this.data();
                    console.log(data.Reg);
                    if (names[data.Reg] === undefined) {
                        // not seen before
                        names[data.Reg] = rowIdx;
                        avail.push(rowIdx);
                    } else {
                        duplicate.push(rowIdx);
                    }
                })
                api.rows(avail).remove().draw();
            },
            "aaData": data.d.results,
            "aoColumns": [  
            {    
                "mData": "Created",
                "render": function (data, type, row) {
                data = moment(data).format('DD-MM-YYYY HH:mm');
                return data;}               
            },             
            {    
                "mData": "Family"
            },             
            {    
                "mData": "Reg"
            },             
            {    
                "mData": "Date",
                "render": function (data, type, row) {
                data = moment(data).format('DD-MM-YYYY');
                return data;}               
            },
            {    
                "mData": "Number"
            }
            ]    
        });    

colin

I'm sorry, but I'm not clear what functionality you're after. Could you update my test case, please, and give an example of what you would expect to happen,

Colin

2008jackson

Hi Colin, sorry for not being clear. Test case at

http://live.datatables.net/nawudoba/1/edit

Code is working well to pull out the duplicates however, the first instance isnt available. I need to see 3 rows for 'John Smith' i.e. rows with same name column data > 1. Appreciate the support.

kthorngren

That would require a more complex loop. You can use column().data() along with toArray() to get an array of names, for example:

var names = api.column( 0 ).data().toArray();

Then you can use the rows().every() to loop through all the rows and see if the name is in the array more than once. This SO thread has some techniques for this. Choose the one that best fits your solution.

Kevin

2008jackson

Hi Kevin, Appreciate the feedback. I've now been able to identify the unique column values to remove rows with var difference. Guess i need the row IDs for these values to remove the rows. Could you pls guide me to fetch the corresponding IDs to remove rows?

            initComplete: function() {
                var names = [];
                var api = this.api();
                var duplicate = [];
                var avail = [];

                api.rows().every(function(rowIdx, tableLoop, rowLoop) {
                    var data = this.data();
                    if (names[data.Reg] === undefined) {
                        names[data.Reg] = rowIdx;
                        avail.push(data.Reg);
                    } else {
                        duplicate.push(data.Reg);
                    }
                })
                console.log(avail);
                console.log(duplicate);     
                var b1 = new Set(duplicate);
                var difference = [...new Set(avail.filter(x => !b1.has(x)))];               
                console.log(difference);
                api.rows(<<DIFFERENCE IDS>>).remove().draw();
            },

kthorngren

Sorry I meant something more like this:
http://live.datatables.net/nawudoba/4/edit

Kevin

2008jackson

Hi Kevin,

Your example works perfect for an array of arrays. However, im working with an array of objects. Refer example at http://live.datatables.net/nawudoba/5/edit

TypeError: Cannot read property 'name' of undefined.

Unable to pick the data field for name and filter values to remove uniques. Appreciate the support.

colin

Can you update Kevin's test case with your data, so we can see the problem, please.

Colin

2008jackson

Hi Colin,

Updated case available at http://live.datatables.net/nawudoba/5/edit with array of objects.

kthorngren

The problem is we are removing the rows in the loop. Instead we need to keep track of the row indexes in the loop then use rows().remove() after the loop with the indexes.
http://live.datatables.net/nawudoba/7/edit

Kevin

2008jackson

Thankyou Kevin! worked like a charm!