How can I do any multiplication or any mathematical operation for 2 rows in data table-CodePudding

I am new to development, and trying to build data table with JSON. So far I did

{
    "qty": "2",
    "price": 1
}, {
    "qty": "3",
    "price": "5",
}, {
    "qty": "3",
    "price": "4",
}

var table = $('#example').DataTable({
            data: dataSet,
            columns: [{
                        title: "qty",
                        data: "qty"
                    },
                    {
                        title: "price",
                        data: "price",
                        render: function(data, type, row) {
                            return '<input type="number" value="'   data   '">';
                        },
                        {
                            title: "total",
                            data: "multiplication'}
                        }
                    ]

I want to perform multiplication of two rows qty and price and store result in third row.

CodePudding user response：

Consider another example.

$(function() {
  var dataSet = [{
      "qty": 2,
      "price": 1.00,
    },
    {
      "qty": 3,
      "price": 5.99
    },
    {
      "qty": 3,
      "price": 4.00
    }
  ];

  function calcTotal(q, p) {
    var t = 0.00;
    t = parseInt(q) * parseFloat(p);
    return t.toFixed(2);
  }

  $.each(dataSet, function(i, r) {
    r.total = calcTotal(r.qty, r.price);
  });

  var table = $('#example').DataTable({
    data: dataSet,
    columns: [{
        "data": "qty"
      },
      {
        "data": "price",
        "render": function(data, type, row) {
          return '$<input type="number"  min="0.00" step="0.01" value="'   data   '" />';
        }
      },
      {
        "data": "total",
        "render": function(data, type, row) {
          return "$"   data;
        }
      }
    ]
  });

  $("#example").on("change", ".price-input", function(event) {
    var row = $(this).closest("tr");
    var qty = table.cell($("td", row).eq(0)).data();
    var price = $(this).val();
    var total = table.cell($("td", row).eq(2).get(0));
    total.data(calcTotal(qty, price)).draw();
  });
});

td {
  text-align: center !important;
}

.price-input {
  width: 5em;
}

<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css" />
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/responsive/2.2.9/css/responsive.dataTables.min.css" />

<script type="text/javascript" src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/responsive/2.2.9/js/dataTables.responsive.min.js"></script>

<table id="example" class="display" cellspacing="0" width="100%">
  <thead>
    <tr>
      <th>Quantity</th>
      <th>Price</th>
      <th>Total</th>
    </tr>
  </thead>
  <tbody>
  </tbody>
</table>

This updates the Data Set before building the Table. So the values are already there. You can then use a change callback to modify that data.

Note: Most interfaces want to change the Quantity and not the price, but since your example changed the Price, I kept it as is. You can easily modify this to work the other way.

CodePudding user response：

Here is a simple solution you can do this with columnDefs option:

In your column definition, add a third data column with the value null. (Remember to have the number of columns matching in your HTML). I created a third HTML column with the <th>Total</th>. The table will render the column empty.

When it gets to the columnDef, you want to make sure you target the correct column, remember it reads them based on a 0 index, so 2 is actually 3.

columnDefs: [{
                "targets": 2,
                "render": function(data, type, full, meta) {
                    return type === 'display' ? ((full.qty)*(full.price))   '' : data;
                }

Remember, the full parameter is the data for the entire row, so in the return of your render, you multiply the two data values (full.qty)*(full.price)

var dataSet = [
{
    "qty": "2",
    "price": "1",
}, 
{
    "qty": "3",
    "price": "5"
}, 
{
    "qty": "3",
    "price": "4"
}]

var table = $('#example').DataTable({
            data: dataSet,
            columns: [
            {"data" : "qty"},
            {"data": "price"},
            {"data" : null}
            ],
            columnDefs: [{
                "targets": 2,
                "render": function(data, type, full, meta) {
                    return "$"   ((full.qty)*(full.price));
                }
            }]
});

td {
  text-align: center !important;
}

<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css"/>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/responsive/2.2.9/css/responsive.dataTables.min.css"/>
 
<script type="text/javascript" src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/responsive/2.2.9/js/dataTables.responsive.min.js"></script>


  <table id="example" class="display" cellspacing="0" width="100%">
    <thead>
        <tr>
      <th>Quantity</th>
            <th>Price</th>
            <th>Total</th>
        </tr>
    </thead>
<tbody>
  
</tbody>
</table>