Reorder Data in Table using jquery each

Question

I have this set of data pulled from the database. The prices are different but some data are redundant.

Here's the screenshot of the data.

The table that I want it to look like is this.

I arranged them in jQuery using .each() but what I got is an epic fail.

Only the header and first body row is correct. Here's my code:

var route_price_table = '';
route_price_table = '<table class="table table-bordered table-striped"><thead><tr><th>SEA</th>';
var initial_boxname = '';
var iterator = 0;
var iterator2 = 0;
var length = data.length;
var route_price_body_table = '';
var route_price_body_table2= '';
var initial_routename = '';
$.each( data, function( key, value ) {
            if(iterator == key){
                initial_routename = value.Route;
                route_price_table += '<th>' + value.BoxName + '</th>';
                initial_boxname = value.BoxName;
                if(initial_routename == value.Route){
                    route_price_body_table += '<tr><td>' + value.Route + '</td><td>' + value.Price + ' (' + value.BoxName + ')</td>';
                }
            }
            if(initial_boxname != value.BoxName){
                initial_boxname = value.BoxName;
                route_price_table += '<th>' + value.BoxName + '</th>';
                route_price_body_table += '<td>' + value.Price + ' (' + value.BoxName + ')</td>';
            }
            if(initial_routename != value.Route){
                route_price_body_table2 += '<tr><td>' + value.Route + '</td><td>' + value.Price + ' (' + value.BoxName + ')</td>';
                initial_routename = value.Route;
            }
            if (key === (length - 1)) {
                route_price_table += '</tr></thead><tbody>';
                route_price_table += route_price_body_table + route_price_body_table2;
            }
        });
        route_price_table += '</tbody></table>';
        $('#route_price_table').html(route_price_table);

Can this be achieved using jQuery or do I need to just arrange it backend? Any help is highly appreciated.

This can be achieved via jQuery but I'll suggest you to do it on backend so that you don't have to traverse all data again and again. — sauhardnc
– sauhardnc, Commented Jul 18, 2020 at 4:50
@sauhardnc I looked into crosstab query and I think that's what I need but I have no exp in that and I'm still experimenting on that to achieve what I need. It would be awesome if I could be able to set the table headers dynamically using query. — elimariaaa
– elimariaaa, Commented Jul 18, 2020 at 18:59

Triby · Accepted Answer · 2020-07-19 06:51:10Z

You can create a new array, grouping items by Route, having all info for each table row in a single object:

let data = [
    {"Price": "10.00", "BoxName": "Small Box", "Route": "NCR/SLuz"},
    {"Price": "15.00", "BoxName": "Small Box", "Route": "NLUZ/VisMin"},
    {"Price": "20.00", "BoxName": "Small Box", "Route": "ISLANDS"},
    {"Price": "25.00", "BoxName": "Small Box", "Route": "Indonesia"},
    {"Price": "30.00", "BoxName": "Medium Box", "Route": "NCR/SLuz"},
    {"Price": "35.00", "BoxName": "Medium Box", "Route": "NLUZ/VisMin"},
    {"Price": "40.00", "BoxName": "Medium Box", "Route": "ISLANDS"},
    {"Price": "45.00", "BoxName": "Medium Box", "Route": "Indonesia"},
    {"Price": "50.00", "BoxName": "Large Box", "Route": "NCR/SLuz"},
    {"Price": "55.00", "BoxName": "Large Box", "Route": "NLUZ/VisMin"},
    {"Price": "60.00", "BoxName": "Large Box", "Route": "ISLANDS"},
    {"Price": "65.00", "BoxName": "Large Box", "Route": "Indonesia"},
    {"Price": "70.00", "BoxName": "Regular Box", "Route": "NCR/SLuz"},
    {"Price": "75.00", "BoxName": "Regular Box", "Route": "NLUZ/VisMin"},
    {"Price": "80.00", "BoxName": "Regular Box", "Route": "ISLANDS"},
    {"Price": "85.00", "BoxName": "Regular Box", "Route": "Indonesia"},
    {"Price": "90.00", "BoxName": "Very Big Box", "Route": "NCR/SLuz"},
    {"Price": "95.00", "BoxName": "Jumbo Box", "Route": "NLUZ/VisMin"},
    {"Price": "100.00", "BoxName": "Jumbo Box", "Route": "ISLANDS"},
    {"Price": "105.00", "BoxName": "Another Box", "Route": "Indonesia"},
];

// .map to get just BoxName and .filter to get unique values
let boxes = data.map((obj) => obj.BoxName).filter((v, i, a) => a.indexOf(v) === i);

// Define a new object to reorder items
let nData = {};

// Loop on original array
$.each(data, (key, item) => {
    // Check if exists current Route
    if(!nData[item.Route]) {
        // Initialize as empty object
        nData[item.Route] = {};
    }
    // Assign Price to BoxName
    nData[item.Route][item.BoxName] = item.Price;
});

// Now you can use boxes for table head and loop nData to build the table
console.log(boxes, nData);

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

Thanks for the solution! The box types are not fixed so when I tried what you did, those who are not equal to names: Small Medium Large Regular Jumbo were not included. I need it to be dynamic.

Collectives™ on Stack Overflow

Reorder Data in Table using jquery each

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related