Select and Calculate

A WireBootstrap DataTable allows rows of data to be selected, aggregated, and transformed. 
Select
The following example selects two columns, product and price, from the data table and creates a new data table using the table function at the end of the method chain.
let data = [ 
    { product: "apples", order: "PRN001", quantity: 10, price: 5.5 },
    { product: "oranges", order: "PRN002", quantity: 15, price: 6 },
    { product: "bananas", order: "PRN002", quantity: 20, price: 7.5 }
];
​
let table = new wire.data.DataTable(data);
 
table = table.select("product", "price").table();
Format
Columns have a  number of attributes that can be set in the select method chain.  In order to set these attributes, reference an individual column using the column method and then use any number of the column attribute methods to set it's values.
Use the as function to change the name of a field.  In the example below, the field quantity will be named number_of_skus in the new table.
...
​
table = table.select().column("quantity").as("number_of_skus").table();
Title
Use the title function to give any column a descriptive title. This will be set into the Title property for the column in the resulting table.
...
​
table = table.select().column("product").title("Product Title").table();
A data table needs a descriptive Title value for any column.  If one is not supplied, it will automatically create one.  In doing so, it will parse camelCase or PascalCase field names and intelligently capitalize and space accordingly to come up with a friendly title.
In the example below, a title for the numberOfSkus column is not supplied.  In the new table, the title will automatically be Number Of Skus.
...
​
table = table.select().column("quantity").as("numberOfSkus").table();
Number Formatting
To format numeric columns, use the format method with a format specification.  Valid values are C[N], P[N], N[N] where [N] is the number of decimal places.
The example below sets the formatting for the price column to currency one decimal place using the C1 parameter in the format function.  This value will be set into the Format property for the column in the resulting table.
...
​
table = table.select("product").column("price").format("C1").table();
Custom Formatting
The format method can also accept a custom function in order to format data in the column.  
The following example formats a date field called date_field using the third party library moment.js.
...
​
table = table.select().column("date_field").format((row) => {
    return moment(row["date_field"]).format("MM-DD-YYY");
}).table();
Calculated Columns
Data tables allow calculated columns to be created on the fly.
The following creates two columns in a new data table that are the aggregates of columns in an existing data table.  In the new table, quantity will be aggregated using sum.  It will have the same name and be formatted to numeric one decimal place using format N1.  The price column will be averaged using avg and it's name changed to avg_price.  It will be formatted to currency one decimal place using format C1.
...
​
table = table.select()
    .sum("quantity").format("N1")
    .avg("price").as("avg_price").format("C1")
    .table();
Aggregates can also be used with implicit grouping by selecting other columns to be included in the result. In the example below, the order column is included in the new data table, so the calculated column quantity is broken out by order.
...
​
table = table.select("order").sum("quantity").table();
 Calculated columns can also be created using custom formulas using the calc method.
In the example below, a new column, more_quantity, is calculated as the sum of the quantity column times 10 and returned in a new table.  This is done using a custom function and a delegate row.  The function will also have the index position and all rows in the data table available as it is being evaluated.
...
​
table = table.select()
    .column("more_quantity")
    .sum().calc((row, index, rows) => {
        return row["quantity"] * 10;
    })    .table();
​
Last modified 2yr ago