Velo Tutorial: Calculating and Displaying Collection Data
Visit the Velo by Wix website to onboard and continue learning.
There are many instances when you want to display data that does not come directly from one of your collections. Instead, you want to calculate values based on the data stored in a collection and display those calculated values. Sometimes you want to calculate data for each item you're displaying and other times you want to calculate aggregated values based on a number of items. In this article, we demonstrate how to display such data using a variety of different page elements.
Per Item Calculations
There are several ways to calculate and display data on a per item basis. Here, we will explain how to do so using a data hook.
You can add a hook to a collection by clicking on Hooks in the Content Manager's menu. After choosing which hooks you want to register, skeletal code will be added to a file named data.js in your site's Code Files, Backend section, for each hook you selected.
The idea is to create an afterQuery hook which intercepts the data from your collection before it reaches your page code. The hook runs for each item that is retrieved by a dataset or query. The hook performs the desired calculations and adds the calculated data to each item before it gets to the page. Then, in your page code, you can get the calculated data and apply it to your page's elements.
You can add a hook to a collection by clicking on Hooks in the Content Manager's menu. After choosing which hooks you want to register, skeletal code will be added to a file named data.js in your site's Code Files, Backend section, for each hook you selected.
The idea is to create an afterQuery hook which intercepts the data from your collection before it reaches your page code. The hook runs for each item that is retrieved by a dataset or query. The hook performs the desired calculations and adds the calculated data to each item before it gets to the page. Then, in your page code, you can get the calculated data and apply it to your page's elements.
For example, let's say you have a collection named Employees that contains information about employees. The collection might store an employee's first name and last name in separate fields. It also might store an employee's date of birth. But for display purposes, you want to show employee names together in one element and employee ages instead of dates of birth.
To get started, you write an afterQuery hook for the Employees collection:
1// In file backend/data.js
2
3export function Employees_afterQuery(item, context) {
4 item.fullName = item.lastName + ", " + item.firstName;
5 item.age = ageFromDob(item.dob);
6
7 return item;
8}The hook receives the item from the collection and adds two new properties to it. First, it adds a fullName property that concatenates the employee's name in a "lastName, firstName" format. Second, it adds an age property that calculates an employee's age based on the date stored in the dob field.
The hook uses the following function, which can also be included in the data.js file:
1function ageFromDob(dob) {
2 const now = new Date();
3 const days = now.getDate() - dob.getDate();
4 const months = now.getMonth() - dob.getMonth();
5 let age = now.getFullYear() - dob.getFullYear();
6
7 if (months < 0 || (months === 0 && days < 0)) {
8 age--;
9 }
10
11 return age.toString();
12}Note:
You may want to create the ageFromDob function in a separate file and import it in data.js. That will allow you to use the function elsewhere.
A page that retrieves items from the Employees collection using a dataset or query will now have the new properties that you've added to each item. The next step is to apply the values from those properties to the page's elements.
In the following examples, we will assume that the page receives its data using a dataset. Generally, this means that you can connect your page elements to any non-calculated data as you would normally do. However, since your calculated data is not part of the database schema, you will have to "connect" the data to page elements using code. If you use a query instead of a dataset to retrieve your page's information, you can use a similar approach to apply the calculated data to your page elements.
In the following examples, we will assume that the page receives its data using a dataset. Generally, this means that you can connect your page elements to any non-calculated data as you would normally do. However, since your calculated data is not part of the database schema, you will have to "connect" the data to page elements using code. If you use a query instead of a dataset to retrieve your page's information, you can use a similar approach to apply the calculated data to your page elements.
Text Elements
To apply calculated data to a text element, you set the value of the text element's text property to the value of one of the item's calculated properties in the dataset's onReady() function.
If your page's dataset contains more than one item and you allow your site visitors to click through multiple items using previous and next buttons, you also need to set the value of the text element's text property in the dataset's onCurrentIndexChanged function.
You can connect all the other elements on your page to their corresponding non-calculated fields as you would normally do.
For example, assuming the employee data mentioned above, you connect non-calculated fields, such as address and job title, to page elements using their connect panels. However, you leave the elements that will show the calculated data, the employee name and age, unconnected. Those connections are dealt with in the following code.
1$w.onReady(function () {
2 $w("#dataset1").onReady(() => {
3 populateCalculatedFields();
4 } );
5
6 $w("#dataset1").onCurrentIndexChanged( (index) => {
7 populateCalculatedFields();
8 } );
9} );
10
11function populateCalculatedFields() {
12 const currentItem = $w("#dataset1").getCurrentItem();
13 $w("#textFullName").text = currentItem.fullName;
14 $w("#textAge").text = currentItem.age;
15}Here, the populateCalculatedFields() function takes the calculated data from the dataset and applies it to the corresponding page elements. It is called when the dataset is first ready and then every time the current item is changed.
Table
To apply calculated data to a table, you reset the table's columns using its columns property to include columns for each item's calculated values. You then set the data of the table using the table's rows property.
You begin by connecting the table to a dataset as you would normally do. Then, use the table's Manage Table panel to delete any columns that you don't want to be shown in your table because you're going to calculate their data.
For example, assuming the employee data mentioned above, after you connect your table to the dataset, you will delete the First Name, Last, Name, and DOB columns. The columns that are remaining will receive their data from the dataset. The columns that will display your calculated data are created in the following code.
1$w.onReady(function () {
2 const currentColumns = $w("#employeeTable").columns;
3 const calculatedColumns = [
4 {
5 "id": "colFullName",
6 "dataPath": "fullName",
7 "label": "Name",
8 "type": "string",
9 },
10 {
11 "id": "colAge",
12 "dataPath": "age",
13 "label": "Age",
14 "type": "string",
15 }
16 ];
17 $w("#employeeTable").columns = calculatedColumns.concat(currentColumns);Here, the code begins by getting the current columns from the table using the columns property. Remember, those will correspond to the fields from the dataset, minus the columns we deleted.
Next, it creates an array of two new columns that will hold the calculated data.
Then, it puts both sets of columns together and uses that to reset the table's columns property.
Repeater
To apply calculated data to a repeater, you set the values of the corresponding elements using the repeater's onItemReady() function.
You can connect all the other elements in your repeater to their corresponding non-calculated fields as you would normally do.
For example, assuming the employee data mentioned above, you connect non-calculated fields, such as address and job title, to elements in your repeater using their connect panels. However, you leave the elements that will show the calculated data, the employee name and age, unconnected. Those connections are dealt with in the following code.
1$w.onReady(function () {
2 $w("#employeeRepeater").onItemReady( ($item, itemData, index) => {
3 $item("#repeaterFullName").text = itemData.fullName;
4 $item("#repeaterAge").text = itemData.age;
5 } );
6} );Here, the repeater onItemReady() function takes the calculated data from the dataset and applies it to the corresponding elements in the repeater items. It is called for each item in the repeater as they are created.
Aggregations
If you want to display aggregated values, using a data hook is not necessarily the best option since hooks work on a per item basis. Instead, it is usually better to perform your aggregation calculations in your page code.
Note:
This example only works if there are 1000 items or less in your collection. That is because the query() function is limited to retrieving at most 1000 items. If you have more than 1000 items, you can work around the limitation by performing multiple queries.
For example, let's say you have a collection named Expenses that contains information about monthly expenses. The collection might store the month, type, and amount of each individual expense. There can be multiple items for each month. Instead of displaying all the expense items individually, you want to just display a total for each month.
We can use the following code to populate a table with the aggregated expense data:
1import wixData from 'wix-data';
2import {monthSort} from 'public/utils';
3
4$w.onReady(function () {
5 $w("#aggTable").columns = [
6 {
7 "id": "col1",
8 "dataPath": "month",
9 "label": "Month",
10 "type": "string"
11 },
12 {
13 "id": "col2",
14 "dataPath": "total",
15 "label": "Total Expenses",
16 "visible": true,
17 "type": "number"
18 }
19 ];
20
21 wixData.query("Expenses")
22 .ascending("month")
23 .limit(1000) // include a limit if you have more than 50 items
24 .find()
25 .then( (result) => {
26 const months = result.items.map(x => x.month)
27 .filter((obj, index, self) => index === self.indexOf(obj))
28 .sort(monthSort);
29 const aggregated = months.map(x => {
30 return {
31 month: x,
32 total: result.items.filter(obj => obj.month === x)
33 .map(z => z.amount)
34 .reduce((sum, current) => sum + current)
35 };
36 } );
37
38 $w("#aggTable").rows = aggregated;
39 } );
40} );The code begins by setting the columns of a table. There will be two columns, one for the name of the month and another for the total expenses during that month.
Next, the code performs a query on the expenses collection. Once it receives the results of the query, it uses standard JavaScript array functions to calculate the total expenditures for each month.
Next, the code performs a query on the expenses collection. Once it receives the results of the query, it uses standard JavaScript array functions to calculate the total expenditures for each month.
In this particular case, the code first extracts a sorted list of the months found in the collection. It uses the following function from a public file to sort the months list:
1const months = ["January", "February", "March", "April",
2 "May", "June", "July", "August",
3 "September", "October", "November", "December"];
4
5export function monthSort(a, b) {
6 return months.indexOf(a) - months.indexOf(b);
7}Then, for each of those months, it creates an object with two key:value pairs. The first pair corresponds to the name of the month. The second pair corresponds to the total expenditures for that month.
Finally, the objects that were just created are set to be the row data for the table.
Finally, the objects that were just created are set to be the row data for the table.
Did this help?
|