How to remove Duplicate without groupBy function?

INTONE

I have been checking for a solution but I discovered that datatables/editor does not support mysql group by function. This has made a very simple task very difficult to fix. I was able to fix the issue on the client side using the excellent APIs. here:

    drawCallback: function( settings ) {

            var api = this.api();

            // Output the data for the visible rows to the browser's console
            //console.log( api.rows( {page:'current'} ).data() );

            $.each(api.rows( {page:'current'} ).data(),function(i,v){
                //console.log(v.ad_client.company_name);
                console.log(v.DT_RowId);
                var ids = $('[id="' + v.DT_RowId + '"]');
               if (ids.length > 1) $('[id="' + v.DT_RowId + '"]:gt(0)').remove();
            })
        }

allan

I'd be interested if you could explain a little bit more about your use case here (for my future planning of Editor's features). Presumably each row in the source db table has a unique id, so how are you grouping data (or perhaps more specifically, why is there duplicates when there is a unique id for each row)? Are you applying a where condition to the table's data set?

Thanks,
Allan

INTONE

The use case for this is that I have a master table and a details table. On the master table i do a left join with the details table for verification purposes but in the out put I only want to see the single master record entry not all the accompanied details entry that would have created multiple master entries. So a "group by" would have fixed this, however this is not possible with datatables on the server side if using the "->leftJoin" thus I employed a front end method of removing those records.

Because when a master table is joined to a details table using a left join the master record will be repeated depending on how many details records share the same id, then all the code does is take the table row id represented by DT_RowId and check for multiple DT_RowId with the same value then remove all except one. Thats pretty much it.

allan

Ah interesting - thanks for the feedback.

What you are looking for is actually possible in Editor 1.4 using a where statement with a sub-select and doing an IN. Not as clean as a group by, but it should work quite well.

I wrote a big old post about that a week or so ago and now I can't find it... Planning to write a blog post on this topic soon.

Allan

INTONE

If you could find the post I would appreciate it.

allan

Got it. My inner select needed work (see the discussion further down) but the basic idea of using an inner select to do the job should work nicely.

Allan